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 "slingshot",
194 "plc",
195 "constellation",
196 "cdn",
197 "pds",
198 "staging",
199 "auth"
200]);
201
202export const isValidHandle = (handle: string): boolean => {
203 const h = handle.trim().toLowerCase();
204 if (h.length < 3 || h.length > 63) return false;
205 if (!/^[a-z0-9-]+$/.test(h)) return false;
206 if (h.startsWith('-') || h.endsWith('-')) return false;
207 if (h.includes('--')) return false;
208 if (RESERVED_HANDLES.has(h)) return false;
209 return true;
210};
211
212export const toDomain = (handle: string): string => `${handle.toLowerCase()}.${BASE_HOST}`;
213
214export const getDomainByDid = async (did: string): Promise<string | null> => {
215 const rows = await db`SELECT domain FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
216 return rows[0]?.domain ?? null;
217};
218
219export const getWispDomainInfo = async (did: string) => {
220 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
221 return rows[0] ?? null;
222};
223
224export const getAllWispDomains = async (did: string) => {
225 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC`;
226 return rows;
227};
228
229export const countWispDomains = async (did: string): Promise<number> => {
230 const rows = await db`SELECT COUNT(*) as count FROM domains WHERE did = ${did}`;
231 return Number(rows[0]?.count ?? 0);
232};
233
234export const getDidByDomain = async (domain: string): Promise<string | null> => {
235 const rows = await db`SELECT did FROM domains WHERE domain = ${domain.toLowerCase()}`;
236 return rows[0]?.did ?? null;
237};
238
239export const isDomainAvailable = async (handle: string): Promise<boolean> => {
240 const h = handle.trim().toLowerCase();
241 if (!isValidHandle(h)) return false;
242 const domain = toDomain(h);
243 const rows = await db`SELECT 1 FROM domains WHERE domain = ${domain} LIMIT 1`;
244 return rows.length === 0;
245};
246
247export const isDomainRegistered = async (domain: string) => {
248 const domainLower = domain.toLowerCase().trim();
249
250 // Check wisp.place subdomains
251 const wispDomain = await db`
252 SELECT did, domain, rkey FROM domains WHERE domain = ${domainLower}
253 `;
254
255 if (wispDomain.length > 0) {
256 return {
257 registered: true,
258 type: 'wisp' as const,
259 domain: wispDomain[0].domain,
260 did: wispDomain[0].did,
261 rkey: wispDomain[0].rkey
262 };
263 }
264
265 // Check custom domains
266 const customDomain = await db`
267 SELECT id, domain, did, rkey, verified FROM custom_domains WHERE domain = ${domainLower}
268 `;
269
270 if (customDomain.length > 0) {
271 return {
272 registered: true,
273 type: 'custom' as const,
274 domain: customDomain[0].domain,
275 did: customDomain[0].did,
276 rkey: customDomain[0].rkey,
277 verified: customDomain[0].verified
278 };
279 }
280
281 return { registered: false };
282};
283
284export const claimDomain = async (did: string, handle: string): Promise<string> => {
285 const h = handle.trim().toLowerCase();
286 if (!isValidHandle(h)) throw new Error('invalid_handle');
287
288 // Check if user already has 3 domains
289 const existingCount = await countWispDomains(did);
290 if (existingCount >= 3) {
291 throw new Error('domain_limit_reached');
292 }
293
294 const domain = toDomain(h);
295 try {
296 await db`
297 INSERT INTO domains (domain, did)
298 VALUES (${domain}, ${did})
299 `;
300 } catch (err) {
301 // Unique constraint violations -> already taken
302 throw new Error('conflict');
303 }
304 return domain;
305};
306
307export const updateDomain = async (did: string, handle: string): Promise<string> => {
308 const h = handle.trim().toLowerCase();
309 if (!isValidHandle(h)) throw new Error('invalid_handle');
310 const domain = toDomain(h);
311 try {
312 const rows = await db`
313 UPDATE domains SET domain = ${domain}
314 WHERE did = ${did}
315 RETURNING domain
316 `;
317 if (rows.length > 0) return rows[0].domain as string;
318 // No existing row, behave like claim
319 return await claimDomain(did, handle);
320 } catch (err) {
321 // Unique constraint violations -> already taken by someone else
322 throw new Error('conflict');
323 }
324};
325
326export const updateWispDomainSite = async (domain: string, siteRkey: string | null): Promise<void> => {
327 await db`
328 UPDATE domains
329 SET rkey = ${siteRkey}
330 WHERE domain = ${domain}
331 `;
332};
333
334export const getWispDomainSite = async (did: string): Promise<string | null> => {
335 const rows = await db`SELECT rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
336 return rows[0]?.rkey ?? null;
337};
338
339export const deleteWispDomain = async (domain: string): Promise<void> => {
340 await db`DELETE FROM domains WHERE domain = ${domain}`;
341};
342
343// Session timeout configuration (30 days in seconds)
344const SESSION_TIMEOUT = 30 * 24 * 60 * 60; // 2592000 seconds
345// OAuth state timeout (1 hour in seconds)
346const STATE_TIMEOUT = 60 * 60; // 3600 seconds
347
348const stateStore = {
349 async set(key: string, data: any) {
350 const expiresAt = Math.floor(Date.now() / 1000) + STATE_TIMEOUT;
351 await db`
352 INSERT INTO oauth_states (key, data, created_at, expires_at)
353 VALUES (${key}, ${JSON.stringify(data)}, EXTRACT(EPOCH FROM NOW()), ${expiresAt})
354 ON CONFLICT (key) DO UPDATE SET data = EXCLUDED.data, expires_at = ${expiresAt}
355 `;
356 },
357 async get(key: string) {
358 const now = Math.floor(Date.now() / 1000);
359 const result = await db`
360 SELECT data, expires_at
361 FROM oauth_states
362 WHERE key = ${key}
363 `;
364 if (!result[0]) return undefined;
365
366 // Check if expired
367 const expiresAt = Number(result[0].expires_at);
368 if (expiresAt && now > expiresAt) {
369 await db`DELETE FROM oauth_states WHERE key = ${key}`;
370 return undefined;
371 }
372
373 return JSON.parse(result[0].data);
374 },
375 async del(key: string) {
376 await db`DELETE FROM oauth_states WHERE key = ${key}`;
377 }
378};
379
380const sessionStore = {
381 async set(sub: string, data: any) {
382 const expiresAt = Math.floor(Date.now() / 1000) + SESSION_TIMEOUT;
383 await db`
384 INSERT INTO oauth_sessions (sub, data, updated_at, expires_at)
385 VALUES (${sub}, ${JSON.stringify(data)}, EXTRACT(EPOCH FROM NOW()), ${expiresAt})
386 ON CONFLICT (sub) DO UPDATE SET
387 data = EXCLUDED.data,
388 updated_at = EXTRACT(EPOCH FROM NOW()),
389 expires_at = ${expiresAt}
390 `;
391 },
392 async get(sub: string) {
393 const now = Math.floor(Date.now() / 1000);
394 const result = await db`
395 SELECT data, expires_at
396 FROM oauth_sessions
397 WHERE sub = ${sub}
398 `;
399 if (!result[0]) return undefined;
400
401 // Check if expired
402 const expiresAt = Number(result[0].expires_at);
403 if (expiresAt && now > expiresAt) {
404 console.log('[sessionStore] Session expired, deleting', sub);
405 await db`DELETE FROM oauth_sessions WHERE sub = ${sub}`;
406 return undefined;
407 }
408
409 return JSON.parse(result[0].data);
410 },
411 async del(sub: string) {
412 await db`DELETE FROM oauth_sessions WHERE sub = ${sub}`;
413 }
414};
415
416export { sessionStore };
417
418// Cleanup expired sessions and states
419export const cleanupExpiredSessions = async () => {
420 const now = Math.floor(Date.now() / 1000);
421 try {
422 const sessionsDeleted = await db`
423 DELETE FROM oauth_sessions WHERE expires_at < ${now}
424 `;
425 const statesDeleted = await db`
426 DELETE FROM oauth_states WHERE expires_at IS NOT NULL AND expires_at < ${now}
427 `;
428 console.log(`[Cleanup] Deleted ${sessionsDeleted.length} expired sessions and ${statesDeleted.length} expired states`);
429 return { sessions: sessionsDeleted.length, states: statesDeleted.length };
430 } catch (err) {
431 console.error('[Cleanup] Failed to cleanup expired data:', err);
432 return { sessions: 0, states: 0 };
433 }
434};
435
436export const createClientMetadata = (config: { domain: `http://${string}` | `https://${string}`, clientName: string }): ClientMetadata => {
437 const isLocalDev = process.env.LOCAL_DEV === 'true';
438
439 if (isLocalDev) {
440 // Loopback client for local development
441 // For loopback, scopes and redirect_uri must be in client_id query string
442 const redirectUri = 'http://127.0.0.1:8000/api/auth/callback';
443 const scope = 'atproto transition:generic';
444 const params = new URLSearchParams();
445 params.append('redirect_uri', redirectUri);
446 params.append('scope', scope);
447
448 return {
449 client_id: `http://localhost?${params.toString()}`,
450 client_name: config.clientName,
451 client_uri: config.domain,
452 redirect_uris: [redirectUri],
453 grant_types: ['authorization_code', 'refresh_token'],
454 response_types: ['code'],
455 application_type: 'web',
456 token_endpoint_auth_method: 'none',
457 scope: scope,
458 dpop_bound_access_tokens: false,
459 subject_type: 'public'
460 };
461 }
462
463 // Production client with private_key_jwt
464 return {
465 client_id: `${config.domain}/client-metadata.json`,
466 client_name: config.clientName,
467 client_uri: config.domain,
468 logo_uri: `${config.domain}/logo.png`,
469 tos_uri: `${config.domain}/tos`,
470 policy_uri: `${config.domain}/policy`,
471 redirect_uris: [`${config.domain}/api/auth/callback`],
472 grant_types: ['authorization_code', 'refresh_token'],
473 response_types: ['code'],
474 application_type: 'web',
475 token_endpoint_auth_method: 'private_key_jwt',
476 token_endpoint_auth_signing_alg: "ES256",
477 scope: "atproto transition:generic",
478 dpop_bound_access_tokens: true,
479 jwks_uri: `${config.domain}/jwks.json`,
480 subject_type: 'public',
481 authorization_signed_response_alg: 'ES256'
482 };
483};
484
485const persistKey = async (key: JoseKey) => {
486 const priv = key.privateJwk;
487 if (!priv) return;
488 const kid = key.kid ?? crypto.randomUUID();
489 await db`
490 INSERT INTO oauth_keys (kid, jwk, created_at)
491 VALUES (${kid}, ${JSON.stringify(priv)}, EXTRACT(EPOCH FROM NOW()))
492 ON CONFLICT (kid) DO UPDATE SET jwk = EXCLUDED.jwk
493 `;
494};
495
496const loadPersistedKeys = async (): Promise<JoseKey[]> => {
497 const rows = await db`SELECT kid, jwk, created_at FROM oauth_keys ORDER BY kid`;
498 const keys: JoseKey[] = [];
499 for (const row of rows) {
500 try {
501 const obj = JSON.parse(row.jwk);
502 const key = await JoseKey.fromImportable(obj as any, (obj as any).kid);
503 keys.push(key);
504 } catch (err) {
505 console.error('Could not parse stored JWK', err);
506 }
507 }
508 return keys;
509};
510
511const ensureKeys = async (): Promise<JoseKey[]> => {
512 let keys = await loadPersistedKeys();
513 const needed: string[] = [];
514 for (let i = 1; i <= 3; i++) {
515 const kid = `key${i}`;
516 if (!keys.some(k => k.kid === kid)) needed.push(kid);
517 }
518 for (const kid of needed) {
519 const newKey = await JoseKey.generate(['ES256'], kid);
520 await persistKey(newKey);
521 keys.push(newKey);
522 }
523 keys.sort((a, b) => (a.kid ?? '').localeCompare(b.kid ?? ''));
524 return keys;
525};
526
527// Load keys from database every time (stateless - safe for horizontal scaling)
528export const getCurrentKeys = async (): Promise<JoseKey[]> => {
529 return await loadPersistedKeys();
530};
531
532// Key rotation - rotate keys older than 30 days (monthly rotation)
533const KEY_MAX_AGE = 30 * 24 * 60 * 60; // 30 days in seconds
534
535export const rotateKeysIfNeeded = async (): Promise<boolean> => {
536 const now = Math.floor(Date.now() / 1000);
537 const cutoffTime = now - KEY_MAX_AGE;
538
539 try {
540 // Find keys older than 30 days
541 const oldKeys = await db`
542 SELECT kid, created_at FROM oauth_keys
543 WHERE created_at IS NOT NULL AND created_at < ${cutoffTime}
544 ORDER BY created_at ASC
545 `;
546
547 if (oldKeys.length === 0) {
548 console.log('[KeyRotation] No keys need rotation');
549 return false;
550 }
551
552 console.log(`[KeyRotation] Found ${oldKeys.length} key(s) older than 30 days, rotating oldest key`);
553
554 // Rotate the oldest key
555 const oldestKey = oldKeys[0];
556 const oldKid = oldestKey.kid;
557
558 // Generate new key with same kid
559 const newKey = await JoseKey.generate(['ES256'], oldKid);
560 await persistKey(newKey);
561
562 console.log(`[KeyRotation] Rotated key ${oldKid}`);
563
564 return true;
565 } catch (err) {
566 console.error('[KeyRotation] Failed to rotate keys:', err);
567 return false;
568 }
569};
570
571export const getOAuthClient = async (config: { domain: `http://${string}` | `https://${string}`, clientName: string }) => {
572 const keys = await ensureKeys();
573
574 return new NodeOAuthClient({
575 clientMetadata: createClientMetadata(config),
576 keyset: keys,
577 stateStore,
578 sessionStore
579 });
580};
581
582export const getCustomDomainsByDid = async (did: string) => {
583 const rows = await db`SELECT * FROM custom_domains WHERE did = ${did} ORDER BY created_at DESC`;
584 return rows;
585};
586
587export const getCustomDomainInfo = async (domain: string) => {
588 const rows = await db`SELECT * FROM custom_domains WHERE domain = ${domain.toLowerCase()}`;
589 return rows[0] ?? null;
590};
591
592export const getCustomDomainByHash = async (hash: string) => {
593 const rows = await db`SELECT * FROM custom_domains WHERE id = ${hash}`;
594 return rows[0] ?? null;
595};
596
597export const getCustomDomainById = async (id: string) => {
598 const rows = await db`SELECT * FROM custom_domains WHERE id = ${id}`;
599 return rows[0] ?? null;
600};
601
602export const claimCustomDomain = async (did: string, domain: string, hash: string, rkey: string | null = null) => {
603 const domainLower = domain.toLowerCase();
604 try {
605 // Use UPSERT with ON CONFLICT to handle existing pending domains
606 const result = await db`
607 INSERT INTO custom_domains (id, domain, did, rkey, verified, created_at)
608 VALUES (${hash}, ${domainLower}, ${did}, ${rkey}, false, EXTRACT(EPOCH FROM NOW()))
609 ON CONFLICT (domain) DO UPDATE SET
610 id = EXCLUDED.id,
611 did = EXCLUDED.did,
612 rkey = EXCLUDED.rkey,
613 verified = EXCLUDED.verified,
614 created_at = EXCLUDED.created_at
615 WHERE custom_domains.verified = false
616 RETURNING *
617 `;
618
619 if (result.length === 0) {
620 // No rows were updated, meaning the domain exists and is verified
621 throw new Error('conflict');
622 }
623
624 return { success: true, hash };
625 } catch (err) {
626 console.error('Failed to claim custom domain', err);
627 throw new Error('conflict');
628 }
629};
630
631export const updateCustomDomainRkey = async (id: string, rkey: string | null) => {
632 const rows = await db`
633 UPDATE custom_domains
634 SET rkey = ${rkey}
635 WHERE id = ${id}
636 RETURNING *
637 `;
638 return rows[0] ?? null;
639};
640
641export const updateCustomDomainVerification = async (id: string, verified: boolean) => {
642 const rows = await db`
643 UPDATE custom_domains
644 SET verified = ${verified}, last_verified_at = EXTRACT(EPOCH FROM NOW())
645 WHERE id = ${id}
646 RETURNING *
647 `;
648 return rows[0] ?? null;
649};
650
651export const deleteCustomDomain = async (id: string) => {
652 await db`DELETE FROM custom_domains WHERE id = ${id}`;
653};
654
655export const getSitesByDid = async (did: string) => {
656 const rows = await db`SELECT * FROM sites WHERE did = ${did} ORDER BY created_at DESC`;
657 return rows;
658};
659
660export const upsertSite = async (did: string, rkey: string, displayName?: string) => {
661 try {
662 // Only set display_name if provided (not undefined/null/empty)
663 const cleanDisplayName = displayName && displayName.trim() ? displayName.trim() : null;
664
665 await db`
666 INSERT INTO sites (did, rkey, display_name, created_at, updated_at)
667 VALUES (${did}, ${rkey}, ${cleanDisplayName}, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW()))
668 ON CONFLICT (did, rkey)
669 DO UPDATE SET
670 display_name = CASE
671 WHEN EXCLUDED.display_name IS NOT NULL THEN EXCLUDED.display_name
672 ELSE sites.display_name
673 END,
674 updated_at = EXTRACT(EPOCH FROM NOW())
675 `;
676 return { success: true };
677 } catch (err) {
678 console.error('Failed to upsert site', err);
679 return { success: false, error: err };
680 }
681};
682
683export const deleteSite = async (did: string, rkey: string) => {
684 try {
685 await db`DELETE FROM sites WHERE did = ${did} AND rkey = ${rkey}`;
686 return { success: true };
687 } catch (err) {
688 console.error('Failed to delete site', err);
689 return { success: false, error: err };
690 }
691};
692
693// Get all domains (wisp + custom) mapped to a specific site
694export const getDomainsBySite = async (did: string, rkey: string) => {
695 const domains: Array<{
696 type: 'wisp' | 'custom';
697 domain: string;
698 verified?: boolean;
699 id?: string;
700 }> = [];
701
702 // Check wisp domain
703 const wispDomain = await db`
704 SELECT domain, rkey FROM domains
705 WHERE did = ${did} AND rkey = ${rkey}
706 `;
707 if (wispDomain.length > 0) {
708 domains.push({
709 type: 'wisp',
710 domain: wispDomain[0].domain,
711 });
712 }
713
714 // Check custom domains
715 const customDomains = await db`
716 SELECT id, domain, verified FROM custom_domains
717 WHERE did = ${did} AND rkey = ${rkey}
718 ORDER BY created_at DESC
719 `;
720 for (const cd of customDomains) {
721 domains.push({
722 type: 'custom',
723 domain: cd.domain,
724 verified: cd.verified,
725 id: cd.id,
726 });
727 }
728
729 return domains;
730};
731
732// Get count of domains mapped to a specific site
733export const getDomainCountBySite = async (did: string, rkey: string) => {
734 const wispCount = await db`
735 SELECT COUNT(*) as count FROM domains
736 WHERE did = ${did} AND rkey = ${rkey}
737 `;
738
739 const customCount = await db`
740 SELECT COUNT(*) as count FROM custom_domains
741 WHERE did = ${did} AND rkey = ${rkey}
742 `;
743
744 return {
745 wisp: Number(wispCount[0]?.count || 0),
746 custom: Number(customCount[0]?.count || 0),
747 total: Number(wispCount[0]?.count || 0) + Number(customCount[0]?.count || 0),
748 };
749};
750
751// Cookie secret management - ensure we have a secret for signing cookies
752export const getCookieSecret = async (): Promise<string> => {
753 // Check if secret already exists
754 const rows = await db`SELECT secret FROM cookie_secrets WHERE id = 'default' LIMIT 1`;
755
756 if (rows.length > 0) {
757 return rows[0].secret as string;
758 }
759
760 // Generate new secret if none exists
761 const secret = crypto.randomUUID() + crypto.randomUUID(); // 72 character random string
762 await db`
763 INSERT INTO cookie_secrets (id, secret, created_at)
764 VALUES ('default', ${secret}, EXTRACT(EPOCH FROM NOW()))
765 `;
766
767 console.log('[CookieSecret] Generated new cookie signing secret');
768 return secret;
769};