Monorepo for wisp.place. A static site hosting service built on top of the AT Protocol.
wisp.place
nekomimi.pet
1import { NodeOAuthClient, type ClientMetadata } from "@atproto/oauth-client-node";
2import { SQL } from "bun";
3import { JoseKey } from "@atproto/jwk-jose";
4import { BASE_HOST } from "./constants";
5
6export const db = new SQL(
7 process.env.NODE_ENV === 'production'
8 ? process.env.DATABASE_URL || (() => {
9 throw new Error('DATABASE_URL environment variable is required in production');
10 })()
11 : process.env.DATABASE_URL || "postgres://postgres:postgres@localhost:5432/wisp"
12);
13
14await db`
15 CREATE TABLE IF NOT EXISTS oauth_states (
16 key TEXT PRIMARY KEY,
17 data TEXT NOT NULL,
18 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
19 )
20`;
21
22await db`
23 CREATE TABLE IF NOT EXISTS oauth_sessions (
24 sub TEXT PRIMARY KEY,
25 data TEXT NOT NULL,
26 updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
27 expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000
28 )
29`;
30
31await db`
32 CREATE TABLE IF NOT EXISTS oauth_keys (
33 kid TEXT PRIMARY KEY,
34 jwk TEXT NOT NULL,
35 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
36 )
37`;
38
39// Cookie secrets table for signed cookies
40await db`
41 CREATE TABLE IF NOT EXISTS cookie_secrets (
42 id TEXT PRIMARY KEY DEFAULT 'default',
43 secret TEXT NOT NULL,
44 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
45 )
46`;
47
48// Domains table maps subdomain -> DID (now supports up to 3 domains per user)
49await db`
50 CREATE TABLE IF NOT EXISTS domains (
51 domain TEXT PRIMARY KEY,
52 did TEXT NOT NULL,
53 rkey TEXT,
54 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
55 )
56`;
57
58// Add columns if they don't exist (for existing databases)
59try {
60 await db`ALTER TABLE domains ADD COLUMN IF NOT EXISTS rkey TEXT`;
61} catch (err) {
62 // Column might already exist, ignore
63}
64
65try {
66 await db`ALTER TABLE oauth_sessions ADD COLUMN IF NOT EXISTS expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000`;
67} catch (err) {
68 // Column might already exist, ignore
69}
70
71try {
72 await db`ALTER TABLE oauth_keys ADD COLUMN IF NOT EXISTS created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())`;
73} catch (err) {
74 // Column might already exist, ignore
75}
76
77try {
78 await db`ALTER TABLE oauth_states ADD COLUMN IF NOT EXISTS expires_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()) + 3600`;
79} catch (err) {
80 // Column might already exist, ignore
81}
82
83// Remove the unique constraint on domains.did to allow multiple domains per user
84try {
85 await db`ALTER TABLE domains DROP CONSTRAINT IF EXISTS domains_did_key`;
86} catch (err) {
87 // Constraint might already be removed, ignore
88}
89
90// Custom domains table for BYOD (bring your own domain)
91await db`
92 CREATE TABLE IF NOT EXISTS custom_domains (
93 id TEXT PRIMARY KEY,
94 domain TEXT UNIQUE NOT NULL,
95 did TEXT NOT NULL,
96 rkey TEXT,
97 verified BOOLEAN DEFAULT false,
98 last_verified_at BIGINT,
99 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
100 )
101`;
102
103// Migrate existing tables to make rkey nullable and remove default
104try {
105 await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP NOT NULL`;
106} catch (err) {
107 // Column might already be nullable, ignore
108}
109try {
110 await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP DEFAULT`;
111} catch (err) {
112 // Default might already be removed, ignore
113}
114
115// Sites table - cache of place.wisp.fs records from PDS
116await db`
117 CREATE TABLE IF NOT EXISTS sites (
118 did TEXT NOT NULL,
119 rkey TEXT NOT NULL,
120 display_name TEXT,
121 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
122 updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
123 PRIMARY KEY (did, rkey)
124 )
125`;
126
127// Create indexes for common query patterns
128await Promise.all([
129 // oauth_states cleanup queries
130 db`CREATE INDEX IF NOT EXISTS idx_oauth_states_expires_at ON oauth_states(expires_at)`.catch(err => {
131 if (!err.message?.includes('already exists')) {
132 console.error('Failed to create idx_oauth_states_expires_at:', err);
133 }
134 }),
135
136 // oauth_sessions cleanup queries
137 db`CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires_at ON oauth_sessions(expires_at)`.catch(err => {
138 if (!err.message?.includes('already exists')) {
139 console.error('Failed to create idx_oauth_sessions_expires_at:', err);
140 }
141 }),
142
143 // oauth_keys key rotation queries
144 db`CREATE INDEX IF NOT EXISTS idx_oauth_keys_created_at ON oauth_keys(created_at)`.catch(err => {
145 if (!err.message?.includes('already exists')) {
146 console.error('Failed to create idx_oauth_keys_created_at:', err);
147 }
148 }),
149
150 // domains queries by (did, rkey)
151 db`CREATE INDEX IF NOT EXISTS idx_domains_did_rkey ON domains(did, rkey)`.catch(err => {
152 if (!err.message?.includes('already exists')) {
153 console.error('Failed to create idx_domains_did_rkey:', err);
154 }
155 }),
156
157 // custom_domains queries by did
158 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did ON custom_domains(did)`.catch(err => {
159 if (!err.message?.includes('already exists')) {
160 console.error('Failed to create idx_custom_domains_did:', err);
161 }
162 }),
163
164 // custom_domains queries by (did, rkey)
165 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did_rkey ON custom_domains(did, rkey)`.catch(err => {
166 if (!err.message?.includes('already exists')) {
167 console.error('Failed to create idx_custom_domains_did_rkey:', err);
168 }
169 }),
170
171 // custom_domains DNS verification worker queries
172 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_verified ON custom_domains(verified)`.catch(err => {
173 if (!err.message?.includes('already exists')) {
174 console.error('Failed to create idx_custom_domains_verified:', err);
175 }
176 }),
177
178 // sites queries by did
179 db`CREATE INDEX IF NOT EXISTS idx_sites_did ON sites(did)`.catch(err => {
180 if (!err.message?.includes('already exists')) {
181 console.error('Failed to create idx_sites_did:', err);
182 }
183 })
184]);
185
186const RESERVED_HANDLES = new Set([
187 "www",
188 "api",
189 "admin",
190 "static",
191 "public",
192 "preview"
193]);
194
195export const isValidHandle = (handle: string): boolean => {
196 const h = handle.trim().toLowerCase();
197 if (h.length < 3 || h.length > 63) return false;
198 if (!/^[a-z0-9-]+$/.test(h)) return false;
199 if (h.startsWith('-') || h.endsWith('-')) return false;
200 if (h.includes('--')) return false;
201 if (RESERVED_HANDLES.has(h)) return false;
202 return true;
203};
204
205export const toDomain = (handle: string): string => `${handle.toLowerCase()}.${BASE_HOST}`;
206
207export const getDomainByDid = async (did: string): Promise<string | null> => {
208 const rows = await db`SELECT domain FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
209 return rows[0]?.domain ?? null;
210};
211
212export const getWispDomainInfo = async (did: string) => {
213 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
214 return rows[0] ?? null;
215};
216
217export const getAllWispDomains = async (did: string) => {
218 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC`;
219 return rows;
220};
221
222export const countWispDomains = async (did: string): Promise<number> => {
223 const rows = await db`SELECT COUNT(*) as count FROM domains WHERE did = ${did}`;
224 return Number(rows[0]?.count ?? 0);
225};
226
227export const getDidByDomain = async (domain: string): Promise<string | null> => {
228 const rows = await db`SELECT did FROM domains WHERE domain = ${domain.toLowerCase()}`;
229 return rows[0]?.did ?? null;
230};
231
232export const isDomainAvailable = async (handle: string): Promise<boolean> => {
233 const h = handle.trim().toLowerCase();
234 if (!isValidHandle(h)) return false;
235 const domain = toDomain(h);
236 const rows = await db`SELECT 1 FROM domains WHERE domain = ${domain} LIMIT 1`;
237 return rows.length === 0;
238};
239
240export const isDomainRegistered = async (domain: string) => {
241 const domainLower = domain.toLowerCase().trim();
242
243 // Check wisp.place subdomains
244 const wispDomain = await db`
245 SELECT did, domain, rkey FROM domains WHERE domain = ${domainLower}
246 `;
247
248 if (wispDomain.length > 0) {
249 return {
250 registered: true,
251 type: 'wisp' as const,
252 domain: wispDomain[0].domain,
253 did: wispDomain[0].did,
254 rkey: wispDomain[0].rkey
255 };
256 }
257
258 // Check custom domains
259 const customDomain = await db`
260 SELECT id, domain, did, rkey, verified FROM custom_domains WHERE domain = ${domainLower}
261 `;
262
263 if (customDomain.length > 0) {
264 return {
265 registered: true,
266 type: 'custom' as const,
267 domain: customDomain[0].domain,
268 did: customDomain[0].did,
269 rkey: customDomain[0].rkey,
270 verified: customDomain[0].verified
271 };
272 }
273
274 return { registered: false };
275};
276
277export const claimDomain = async (did: string, handle: string): Promise<string> => {
278 const h = handle.trim().toLowerCase();
279 if (!isValidHandle(h)) throw new Error('invalid_handle');
280
281 // Check if user already has 3 domains
282 const existingCount = await countWispDomains(did);
283 if (existingCount >= 3) {
284 throw new Error('domain_limit_reached');
285 }
286
287 const domain = toDomain(h);
288 try {
289 await db`
290 INSERT INTO domains (domain, did)
291 VALUES (${domain}, ${did})
292 `;
293 } catch (err) {
294 // Unique constraint violations -> already taken
295 throw new Error('conflict');
296 }
297 return domain;
298};
299
300export const updateDomain = async (did: string, handle: string): Promise<string> => {
301 const h = handle.trim().toLowerCase();
302 if (!isValidHandle(h)) throw new Error('invalid_handle');
303 const domain = toDomain(h);
304 try {
305 const rows = await db`
306 UPDATE domains SET domain = ${domain}
307 WHERE did = ${did}
308 RETURNING domain
309 `;
310 if (rows.length > 0) return rows[0].domain as string;
311 // No existing row, behave like claim
312 return await claimDomain(did, handle);
313 } catch (err) {
314 // Unique constraint violations -> already taken by someone else
315 throw new Error('conflict');
316 }
317};
318
319export const updateWispDomainSite = async (domain: string, siteRkey: string | null): Promise<void> => {
320 await db`
321 UPDATE domains
322 SET rkey = ${siteRkey}
323 WHERE domain = ${domain}
324 `;
325};
326
327export const getWispDomainSite = async (did: string): Promise<string | null> => {
328 const rows = await db`SELECT rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
329 return rows[0]?.rkey ?? null;
330};
331
332export const deleteWispDomain = async (domain: string): Promise<void> => {
333 await db`DELETE FROM domains WHERE domain = ${domain}`;
334};
335
336// Session timeout configuration (30 days in seconds)
337const SESSION_TIMEOUT = 30 * 24 * 60 * 60; // 2592000 seconds
338// OAuth state timeout (1 hour in seconds)
339const STATE_TIMEOUT = 60 * 60; // 3600 seconds
340
341const stateStore = {
342 async set(key: string, data: any) {
343 const expiresAt = Math.floor(Date.now() / 1000) + STATE_TIMEOUT;
344 await db`
345 INSERT INTO oauth_states (key, data, created_at, expires_at)
346 VALUES (${key}, ${JSON.stringify(data)}, EXTRACT(EPOCH FROM NOW()), ${expiresAt})
347 ON CONFLICT (key) DO UPDATE SET data = EXCLUDED.data, expires_at = ${expiresAt}
348 `;
349 },
350 async get(key: string) {
351 const now = Math.floor(Date.now() / 1000);
352 const result = await db`
353 SELECT data, expires_at
354 FROM oauth_states
355 WHERE key = ${key}
356 `;
357 if (!result[0]) return undefined;
358
359 // Check if expired
360 const expiresAt = Number(result[0].expires_at);
361 if (expiresAt && now > expiresAt) {
362 await db`DELETE FROM oauth_states WHERE key = ${key}`;
363 return undefined;
364 }
365
366 return JSON.parse(result[0].data);
367 },
368 async del(key: string) {
369 await db`DELETE FROM oauth_states WHERE key = ${key}`;
370 }
371};
372
373const sessionStore = {
374 async set(sub: string, data: any) {
375 const expiresAt = Math.floor(Date.now() / 1000) + SESSION_TIMEOUT;
376 await db`
377 INSERT INTO oauth_sessions (sub, data, updated_at, expires_at)
378 VALUES (${sub}, ${JSON.stringify(data)}, EXTRACT(EPOCH FROM NOW()), ${expiresAt})
379 ON CONFLICT (sub) DO UPDATE SET
380 data = EXCLUDED.data,
381 updated_at = EXTRACT(EPOCH FROM NOW()),
382 expires_at = ${expiresAt}
383 `;
384 },
385 async get(sub: string) {
386 const now = Math.floor(Date.now() / 1000);
387 const result = await db`
388 SELECT data, expires_at
389 FROM oauth_sessions
390 WHERE sub = ${sub}
391 `;
392 if (!result[0]) return undefined;
393
394 // Check if expired
395 const expiresAt = Number(result[0].expires_at);
396 if (expiresAt && now > expiresAt) {
397 console.log('[sessionStore] Session expired, deleting', sub);
398 await db`DELETE FROM oauth_sessions WHERE sub = ${sub}`;
399 return undefined;
400 }
401
402 return JSON.parse(result[0].data);
403 },
404 async del(sub: string) {
405 await db`DELETE FROM oauth_sessions WHERE sub = ${sub}`;
406 }
407};
408
409export { sessionStore };
410
411// Cleanup expired sessions and states
412export const cleanupExpiredSessions = async () => {
413 const now = Math.floor(Date.now() / 1000);
414 try {
415 const sessionsDeleted = await db`
416 DELETE FROM oauth_sessions WHERE expires_at < ${now}
417 `;
418 const statesDeleted = await db`
419 DELETE FROM oauth_states WHERE expires_at IS NOT NULL AND expires_at < ${now}
420 `;
421 console.log(`[Cleanup] Deleted ${sessionsDeleted.length} expired sessions and ${statesDeleted.length} expired states`);
422 return { sessions: sessionsDeleted.length, states: statesDeleted.length };
423 } catch (err) {
424 console.error('[Cleanup] Failed to cleanup expired data:', err);
425 return { sessions: 0, states: 0 };
426 }
427};
428
429export const createClientMetadata = (config: { domain: `http://${string}` | `https://${string}`, clientName: string }): ClientMetadata => {
430 const isLocalDev = process.env.LOCAL_DEV === 'true';
431
432 if (isLocalDev) {
433 // Loopback client for local development
434 // For loopback, scopes and redirect_uri must be in client_id query string
435 const redirectUri = 'http://127.0.0.1:8000/api/auth/callback';
436 const scope = 'atproto transition:generic';
437 const params = new URLSearchParams();
438 params.append('redirect_uri', redirectUri);
439 params.append('scope', scope);
440
441 return {
442 client_id: `http://localhost?${params.toString()}`,
443 client_name: config.clientName,
444 client_uri: config.domain,
445 redirect_uris: [redirectUri],
446 grant_types: ['authorization_code', 'refresh_token'],
447 response_types: ['code'],
448 application_type: 'web',
449 token_endpoint_auth_method: 'none',
450 scope: scope,
451 dpop_bound_access_tokens: false,
452 subject_type: 'public'
453 };
454 }
455
456 // Production client with private_key_jwt
457 return {
458 client_id: `${config.domain}/client-metadata.json`,
459 client_name: config.clientName,
460 client_uri: config.domain,
461 logo_uri: `${config.domain}/logo.png`,
462 tos_uri: `${config.domain}/tos`,
463 policy_uri: `${config.domain}/policy`,
464 redirect_uris: [`${config.domain}/api/auth/callback`],
465 grant_types: ['authorization_code', 'refresh_token'],
466 response_types: ['code'],
467 application_type: 'web',
468 token_endpoint_auth_method: 'private_key_jwt',
469 token_endpoint_auth_signing_alg: "ES256",
470 scope: "atproto transition:generic",
471 dpop_bound_access_tokens: true,
472 jwks_uri: `${config.domain}/jwks.json`,
473 subject_type: 'public',
474 authorization_signed_response_alg: 'ES256'
475 };
476};
477
478const persistKey = async (key: JoseKey) => {
479 const priv = key.privateJwk;
480 if (!priv) return;
481 const kid = key.kid ?? crypto.randomUUID();
482 await db`
483 INSERT INTO oauth_keys (kid, jwk, created_at)
484 VALUES (${kid}, ${JSON.stringify(priv)}, EXTRACT(EPOCH FROM NOW()))
485 ON CONFLICT (kid) DO UPDATE SET jwk = EXCLUDED.jwk
486 `;
487};
488
489const loadPersistedKeys = async (): Promise<JoseKey[]> => {
490 const rows = await db`SELECT kid, jwk, created_at FROM oauth_keys ORDER BY kid`;
491 const keys: JoseKey[] = [];
492 for (const row of rows) {
493 try {
494 const obj = JSON.parse(row.jwk);
495 const key = await JoseKey.fromImportable(obj as any, (obj as any).kid);
496 keys.push(key);
497 } catch (err) {
498 console.error('Could not parse stored JWK', err);
499 }
500 }
501 return keys;
502};
503
504const ensureKeys = async (): Promise<JoseKey[]> => {
505 let keys = await loadPersistedKeys();
506 const needed: string[] = [];
507 for (let i = 1; i <= 3; i++) {
508 const kid = `key${i}`;
509 if (!keys.some(k => k.kid === kid)) needed.push(kid);
510 }
511 for (const kid of needed) {
512 const newKey = await JoseKey.generate(['ES256'], kid);
513 await persistKey(newKey);
514 keys.push(newKey);
515 }
516 keys.sort((a, b) => (a.kid ?? '').localeCompare(b.kid ?? ''));
517 return keys;
518};
519
520// Load keys from database every time (stateless - safe for horizontal scaling)
521export const getCurrentKeys = async (): Promise<JoseKey[]> => {
522 return await loadPersistedKeys();
523};
524
525// Key rotation - rotate keys older than 30 days (monthly rotation)
526const KEY_MAX_AGE = 30 * 24 * 60 * 60; // 30 days in seconds
527
528export const rotateKeysIfNeeded = async (): Promise<boolean> => {
529 const now = Math.floor(Date.now() / 1000);
530 const cutoffTime = now - KEY_MAX_AGE;
531
532 try {
533 // Find keys older than 30 days
534 const oldKeys = await db`
535 SELECT kid, created_at FROM oauth_keys
536 WHERE created_at IS NOT NULL AND created_at < ${cutoffTime}
537 ORDER BY created_at ASC
538 `;
539
540 if (oldKeys.length === 0) {
541 console.log('[KeyRotation] No keys need rotation');
542 return false;
543 }
544
545 console.log(`[KeyRotation] Found ${oldKeys.length} key(s) older than 30 days, rotating oldest key`);
546
547 // Rotate the oldest key
548 const oldestKey = oldKeys[0];
549 const oldKid = oldestKey.kid;
550
551 // Generate new key with same kid
552 const newKey = await JoseKey.generate(['ES256'], oldKid);
553 await persistKey(newKey);
554
555 console.log(`[KeyRotation] Rotated key ${oldKid}`);
556
557 return true;
558 } catch (err) {
559 console.error('[KeyRotation] Failed to rotate keys:', err);
560 return false;
561 }
562};
563
564export const getOAuthClient = async (config: { domain: `http://${string}` | `https://${string}`, clientName: string }) => {
565 const keys = await ensureKeys();
566
567 return new NodeOAuthClient({
568 clientMetadata: createClientMetadata(config),
569 keyset: keys,
570 stateStore,
571 sessionStore
572 });
573};
574
575export const getCustomDomainsByDid = async (did: string) => {
576 const rows = await db`SELECT * FROM custom_domains WHERE did = ${did} ORDER BY created_at DESC`;
577 return rows;
578};
579
580export const getCustomDomainInfo = async (domain: string) => {
581 const rows = await db`SELECT * FROM custom_domains WHERE domain = ${domain.toLowerCase()}`;
582 return rows[0] ?? null;
583};
584
585export const getCustomDomainByHash = async (hash: string) => {
586 const rows = await db`SELECT * FROM custom_domains WHERE id = ${hash}`;
587 return rows[0] ?? null;
588};
589
590export const getCustomDomainById = async (id: string) => {
591 const rows = await db`SELECT * FROM custom_domains WHERE id = ${id}`;
592 return rows[0] ?? null;
593};
594
595export const claimCustomDomain = async (did: string, domain: string, hash: string, rkey: string | null = null) => {
596 const domainLower = domain.toLowerCase();
597 try {
598 // Use UPSERT with ON CONFLICT to handle existing pending domains
599 const result = await db`
600 INSERT INTO custom_domains (id, domain, did, rkey, verified, created_at)
601 VALUES (${hash}, ${domainLower}, ${did}, ${rkey}, false, EXTRACT(EPOCH FROM NOW()))
602 ON CONFLICT (domain) DO UPDATE SET
603 id = EXCLUDED.id,
604 did = EXCLUDED.did,
605 rkey = EXCLUDED.rkey,
606 verified = EXCLUDED.verified,
607 created_at = EXCLUDED.created_at
608 WHERE custom_domains.verified = false
609 RETURNING *
610 `;
611
612 if (result.length === 0) {
613 // No rows were updated, meaning the domain exists and is verified
614 throw new Error('conflict');
615 }
616
617 return { success: true, hash };
618 } catch (err) {
619 console.error('Failed to claim custom domain', err);
620 throw new Error('conflict');
621 }
622};
623
624export const updateCustomDomainRkey = async (id: string, rkey: string | null) => {
625 const rows = await db`
626 UPDATE custom_domains
627 SET rkey = ${rkey}
628 WHERE id = ${id}
629 RETURNING *
630 `;
631 return rows[0] ?? null;
632};
633
634export const updateCustomDomainVerification = async (id: string, verified: boolean) => {
635 const rows = await db`
636 UPDATE custom_domains
637 SET verified = ${verified}, last_verified_at = EXTRACT(EPOCH FROM NOW())
638 WHERE id = ${id}
639 RETURNING *
640 `;
641 return rows[0] ?? null;
642};
643
644export const deleteCustomDomain = async (id: string) => {
645 await db`DELETE FROM custom_domains WHERE id = ${id}`;
646};
647
648export const getSitesByDid = async (did: string) => {
649 const rows = await db`SELECT * FROM sites WHERE did = ${did} ORDER BY created_at DESC`;
650 return rows;
651};
652
653export const upsertSite = async (did: string, rkey: string, displayName?: string) => {
654 try {
655 // Only set display_name if provided (not undefined/null/empty)
656 const cleanDisplayName = displayName && displayName.trim() ? displayName.trim() : null;
657
658 await db`
659 INSERT INTO sites (did, rkey, display_name, created_at, updated_at)
660 VALUES (${did}, ${rkey}, ${cleanDisplayName}, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW()))
661 ON CONFLICT (did, rkey)
662 DO UPDATE SET
663 display_name = CASE
664 WHEN EXCLUDED.display_name IS NOT NULL THEN EXCLUDED.display_name
665 ELSE sites.display_name
666 END,
667 updated_at = EXTRACT(EPOCH FROM NOW())
668 `;
669 return { success: true };
670 } catch (err) {
671 console.error('Failed to upsert site', err);
672 return { success: false, error: err };
673 }
674};
675
676export const deleteSite = async (did: string, rkey: string) => {
677 try {
678 await db`DELETE FROM sites WHERE did = ${did} AND rkey = ${rkey}`;
679 return { success: true };
680 } catch (err) {
681 console.error('Failed to delete site', err);
682 return { success: false, error: err };
683 }
684};
685
686// Get all domains (wisp + custom) mapped to a specific site
687export const getDomainsBySite = async (did: string, rkey: string) => {
688 const domains: Array<{
689 type: 'wisp' | 'custom';
690 domain: string;
691 verified?: boolean;
692 id?: string;
693 }> = [];
694
695 // Check wisp domain
696 const wispDomain = await db`
697 SELECT domain, rkey FROM domains
698 WHERE did = ${did} AND rkey = ${rkey}
699 `;
700 if (wispDomain.length > 0) {
701 domains.push({
702 type: 'wisp',
703 domain: wispDomain[0].domain,
704 });
705 }
706
707 // Check custom domains
708 const customDomains = await db`
709 SELECT id, domain, verified FROM custom_domains
710 WHERE did = ${did} AND rkey = ${rkey}
711 ORDER BY created_at DESC
712 `;
713 for (const cd of customDomains) {
714 domains.push({
715 type: 'custom',
716 domain: cd.domain,
717 verified: cd.verified,
718 id: cd.id,
719 });
720 }
721
722 return domains;
723};
724
725// Get count of domains mapped to a specific site
726export const getDomainCountBySite = async (did: string, rkey: string) => {
727 const wispCount = await db`
728 SELECT COUNT(*) as count FROM domains
729 WHERE did = ${did} AND rkey = ${rkey}
730 `;
731
732 const customCount = await db`
733 SELECT COUNT(*) as count FROM custom_domains
734 WHERE did = ${did} AND rkey = ${rkey}
735 `;
736
737 return {
738 wisp: Number(wispCount[0]?.count || 0),
739 custom: Number(customCount[0]?.count || 0),
740 total: Number(wispCount[0]?.count || 0) + Number(customCount[0]?.count || 0),
741 };
742};
743
744// Cookie secret management - ensure we have a secret for signing cookies
745export const getCookieSecret = async (): Promise<string> => {
746 // Check if secret already exists
747 const rows = await db`SELECT secret FROM cookie_secrets WHERE id = 'default' LIMIT 1`;
748
749 if (rows.length > 0) {
750 return rows[0].secret as string;
751 }
752
753 // Generate new secret if none exists
754 const secret = crypto.randomUUID() + crypto.randomUUID(); // 72 character random string
755 await db`
756 INSERT INTO cookie_secrets (id, secret, created_at)
757 VALUES ('default', ${secret}, EXTRACT(EPOCH FROM NOW()))
758 `;
759
760 console.log('[CookieSecret] Generated new cookie signing secret');
761 return secret;
762};