Monorepo for wisp.place. A static site hosting service built on top of the AT Protocol.
wisp.place
nekomimi.pet
1import { SQL } from "bun";
2import { BASE_HOST } from "@wisp/constants";
3
4export const db = new SQL(
5 process.env.NODE_ENV === 'production'
6 ? process.env.DATABASE_URL || (() => {
7 throw new Error('DATABASE_URL environment variable is required in production');
8 })()
9 : process.env.DATABASE_URL || "postgres://postgres:postgres@localhost:5432/wisp"
10);
11
12await db`
13 CREATE TABLE IF NOT EXISTS oauth_states (
14 key TEXT PRIMARY KEY,
15 data TEXT NOT NULL,
16 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
17 )
18`;
19
20await db`
21 CREATE TABLE IF NOT EXISTS oauth_sessions (
22 sub TEXT PRIMARY KEY,
23 data TEXT NOT NULL,
24 updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
25 expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000
26 )
27`;
28
29await db`
30 CREATE TABLE IF NOT EXISTS oauth_keys (
31 kid TEXT PRIMARY KEY,
32 jwk TEXT NOT NULL,
33 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
34 )
35`;
36
37// Cookie secrets table for signed cookies
38await db`
39 CREATE TABLE IF NOT EXISTS cookie_secrets (
40 id TEXT PRIMARY KEY DEFAULT 'default',
41 secret TEXT NOT NULL,
42 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
43 )
44`;
45
46// Domains table maps subdomain -> DID (now supports up to 3 domains per user)
47await db`
48 CREATE TABLE IF NOT EXISTS domains (
49 domain TEXT PRIMARY KEY,
50 did TEXT NOT NULL,
51 rkey TEXT,
52 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
53 )
54`;
55
56// Add columns if they don't exist (for existing databases)
57try {
58 await db`ALTER TABLE domains ADD COLUMN IF NOT EXISTS rkey TEXT`;
59} catch (err) {
60 // Column might already exist, ignore
61}
62
63try {
64 await db`ALTER TABLE oauth_sessions ADD COLUMN IF NOT EXISTS expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000`;
65} catch (err) {
66 // Column might already exist, ignore
67}
68
69try {
70 await db`ALTER TABLE oauth_keys ADD COLUMN IF NOT EXISTS created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())`;
71} catch (err) {
72 // Column might already exist, ignore
73}
74
75try {
76 await db`ALTER TABLE oauth_states ADD COLUMN IF NOT EXISTS expires_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()) + 3600`;
77} catch (err) {
78 // Column might already exist, ignore
79}
80
81// Remove the unique constraint on domains.did to allow multiple domains per user
82try {
83 await db`ALTER TABLE domains DROP CONSTRAINT IF EXISTS domains_did_key`;
84} catch (err) {
85 // Constraint might already be removed, ignore
86}
87
88// Custom domains table for BYOD (bring your own domain)
89await db`
90 CREATE TABLE IF NOT EXISTS custom_domains (
91 id TEXT PRIMARY KEY,
92 domain TEXT UNIQUE NOT NULL,
93 did TEXT NOT NULL,
94 rkey TEXT,
95 verified BOOLEAN DEFAULT false,
96 last_verified_at BIGINT,
97 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
98 )
99`;
100
101// Migrate existing tables to make rkey nullable and remove default
102try {
103 await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP NOT NULL`;
104} catch (err) {
105 // Column might already be nullable, ignore
106}
107try {
108 await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP DEFAULT`;
109} catch (err) {
110 // Default might already be removed, ignore
111}
112
113// Sites table - cache of place.wisp.fs records from PDS
114await db`
115 CREATE TABLE IF NOT EXISTS sites (
116 did TEXT NOT NULL,
117 rkey TEXT NOT NULL,
118 display_name TEXT,
119 created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
120 updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
121 PRIMARY KEY (did, rkey)
122 )
123`;
124
125// Create indexes for common query patterns
126await Promise.all([
127 // oauth_states cleanup queries
128 db`CREATE INDEX IF NOT EXISTS idx_oauth_states_expires_at ON oauth_states(expires_at)`.catch(err => {
129 if (!err.message?.includes('already exists')) {
130 console.error('Failed to create idx_oauth_states_expires_at:', err);
131 }
132 }),
133
134 // oauth_sessions cleanup queries
135 db`CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires_at ON oauth_sessions(expires_at)`.catch(err => {
136 if (!err.message?.includes('already exists')) {
137 console.error('Failed to create idx_oauth_sessions_expires_at:', err);
138 }
139 }),
140
141 // oauth_keys key rotation queries
142 db`CREATE INDEX IF NOT EXISTS idx_oauth_keys_created_at ON oauth_keys(created_at)`.catch(err => {
143 if (!err.message?.includes('already exists')) {
144 console.error('Failed to create idx_oauth_keys_created_at:', err);
145 }
146 }),
147
148 // domains queries by (did, rkey)
149 db`CREATE INDEX IF NOT EXISTS idx_domains_did_rkey ON domains(did, rkey)`.catch(err => {
150 if (!err.message?.includes('already exists')) {
151 console.error('Failed to create idx_domains_did_rkey:', err);
152 }
153 }),
154
155 // custom_domains queries by did
156 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did ON custom_domains(did)`.catch(err => {
157 if (!err.message?.includes('already exists')) {
158 console.error('Failed to create idx_custom_domains_did:', err);
159 }
160 }),
161
162 // custom_domains queries by (did, rkey)
163 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did_rkey ON custom_domains(did, rkey)`.catch(err => {
164 if (!err.message?.includes('already exists')) {
165 console.error('Failed to create idx_custom_domains_did_rkey:', err);
166 }
167 }),
168
169 // custom_domains DNS verification worker queries
170 db`CREATE INDEX IF NOT EXISTS idx_custom_domains_verified ON custom_domains(verified)`.catch(err => {
171 if (!err.message?.includes('already exists')) {
172 console.error('Failed to create idx_custom_domains_verified:', err);
173 }
174 }),
175
176 // sites queries by did
177 db`CREATE INDEX IF NOT EXISTS idx_sites_did ON sites(did)`.catch(err => {
178 if (!err.message?.includes('already exists')) {
179 console.error('Failed to create idx_sites_did:', err);
180 }
181 })
182]);
183
184const RESERVED_HANDLES = new Set([
185 "www",
186 "api",
187 "admin",
188 "static",
189 "public",
190 "preview",
191 "slingshot",
192 "plc",
193 "constellation",
194 "cdn",
195 "pds",
196 "staging",
197 "auth"
198]);
199
200export const isValidHandle = (handle: string): boolean => {
201 const h = handle.trim().toLowerCase();
202 if (h.length < 3 || h.length > 63) return false;
203 if (!/^[a-z0-9-]+$/.test(h)) return false;
204 if (h.startsWith('-') || h.endsWith('-')) return false;
205 if (h.includes('--')) return false;
206 if (RESERVED_HANDLES.has(h)) return false;
207 return true;
208};
209
210export const toDomain = (handle: string): string => `${handle.toLowerCase()}.${BASE_HOST}`;
211
212export const getDomainByDid = async (did: string): Promise<string | null> => {
213 const rows = await db`SELECT domain FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
214 return rows[0]?.domain ?? null;
215};
216
217export const getWispDomainInfo = async (did: string) => {
218 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
219 return rows[0] ?? null;
220};
221
222export const getAllWispDomains = async (did: string): Promise<Array<{ domain: string; rkey: string | null }>> => {
223 const rows = await db`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC`;
224 return rows;
225};
226
227export const countWispDomains = async (did: string): Promise<number> => {
228 const rows = await db`SELECT COUNT(*) as count FROM domains WHERE did = ${did}`;
229 return Number(rows[0]?.count ?? 0);
230};
231
232export const getDidByDomain = async (domain: string): Promise<string | null> => {
233 const rows = await db`SELECT did FROM domains WHERE domain = ${domain.toLowerCase()}`;
234 return rows[0]?.did ?? null;
235};
236
237export const isDomainAvailable = async (handle: string): Promise<boolean> => {
238 const h = handle.trim().toLowerCase();
239 if (!isValidHandle(h)) return false;
240 const domain = toDomain(h);
241 const rows = await db`SELECT 1 FROM domains WHERE domain = ${domain} LIMIT 1`;
242 return rows.length === 0;
243};
244
245export const isDomainRegistered = async (domain: string) => {
246 const domainLower = domain.toLowerCase().trim();
247
248 // Check wisp.place subdomains
249 const wispDomain = await db`
250 SELECT did, domain, rkey FROM domains WHERE domain = ${domainLower}
251 `;
252
253 if (wispDomain.length > 0) {
254 return {
255 registered: true,
256 type: 'wisp' as const,
257 domain: wispDomain[0].domain,
258 did: wispDomain[0].did,
259 rkey: wispDomain[0].rkey
260 };
261 }
262
263 // Check custom domains
264 const customDomain = await db`
265 SELECT id, domain, did, rkey, verified FROM custom_domains WHERE domain = ${domainLower}
266 `;
267
268 if (customDomain.length > 0) {
269 return {
270 registered: true,
271 type: 'custom' as const,
272 domain: customDomain[0].domain,
273 did: customDomain[0].did,
274 rkey: customDomain[0].rkey,
275 verified: customDomain[0].verified
276 };
277 }
278
279 return { registered: false };
280};
281
282export const claimDomain = async (did: string, handle: string): Promise<string> => {
283 const h = handle.trim().toLowerCase();
284 if (!isValidHandle(h)) throw new Error('invalid_handle');
285
286 // Check if user already has 3 domains
287 const existingCount = await countWispDomains(did);
288 if (existingCount >= 3) {
289 throw new Error('domain_limit_reached');
290 }
291
292 const domain = toDomain(h);
293 try {
294 await db`
295 INSERT INTO domains (domain, did)
296 VALUES (${domain}, ${did})
297 `;
298 } catch (err) {
299 // Unique constraint violations -> already taken
300 throw new Error('conflict');
301 }
302 return domain;
303};
304
305export const updateDomain = async (did: string, handle: string): Promise<string> => {
306 const h = handle.trim().toLowerCase();
307 if (!isValidHandle(h)) throw new Error('invalid_handle');
308 const domain = toDomain(h);
309 try {
310 const rows = await db`
311 UPDATE domains SET domain = ${domain}
312 WHERE did = ${did}
313 RETURNING domain
314 `;
315 if (rows.length > 0) return rows[0].domain as string;
316 // No existing row, behave like claim
317 return await claimDomain(did, handle);
318 } catch (err) {
319 // Unique constraint violations -> already taken by someone else
320 throw new Error('conflict');
321 }
322};
323
324export const updateWispDomainSite = async (domain: string, siteRkey: string | null): Promise<void> => {
325 await db`
326 UPDATE domains
327 SET rkey = ${siteRkey}
328 WHERE domain = ${domain}
329 `;
330};
331
332export const getWispDomainSite = async (did: string): Promise<string | null> => {
333 const rows = await db`SELECT rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`;
334 return rows[0]?.rkey ?? null;
335};
336
337export const deleteWispDomain = async (domain: string): Promise<void> => {
338 await db`DELETE FROM domains WHERE domain = ${domain}`;
339};
340
341export const getCustomDomainsByDid = async (did: string) => {
342 const rows = await db`SELECT * FROM custom_domains WHERE did = ${did} ORDER BY created_at DESC`;
343 return rows;
344};
345
346export const getCustomDomainInfo = async (domain: string) => {
347 const rows = await db`SELECT * FROM custom_domains WHERE domain = ${domain.toLowerCase()}`;
348 return rows[0] ?? null;
349};
350
351export const getCustomDomainByHash = async (hash: string) => {
352 const rows = await db`SELECT * FROM custom_domains WHERE id = ${hash}`;
353 return rows[0] ?? null;
354};
355
356export const getCustomDomainById = async (id: string) => {
357 const rows = await db`SELECT * FROM custom_domains WHERE id = ${id}`;
358 return rows[0] ?? null;
359};
360
361export const claimCustomDomain = async (did: string, domain: string, hash: string, rkey: string | null = null) => {
362 const domainLower = domain.toLowerCase();
363 try {
364 // Use UPSERT with ON CONFLICT to handle existing pending domains
365 const result = await db`
366 INSERT INTO custom_domains (id, domain, did, rkey, verified, created_at)
367 VALUES (${hash}, ${domainLower}, ${did}, ${rkey}, false, EXTRACT(EPOCH FROM NOW()))
368 ON CONFLICT (domain) DO UPDATE SET
369 id = EXCLUDED.id,
370 did = EXCLUDED.did,
371 rkey = EXCLUDED.rkey,
372 verified = EXCLUDED.verified,
373 created_at = EXCLUDED.created_at
374 WHERE custom_domains.verified = false
375 RETURNING *
376 `;
377
378 if (result.length === 0) {
379 // No rows were updated, meaning the domain exists and is verified
380 throw new Error('conflict');
381 }
382
383 return { success: true, hash };
384 } catch (err) {
385 console.error('Failed to claim custom domain', err);
386 throw new Error('conflict');
387 }
388};
389
390export const updateCustomDomainRkey = async (id: string, rkey: string | null) => {
391 const rows = await db`
392 UPDATE custom_domains
393 SET rkey = ${rkey}
394 WHERE id = ${id}
395 RETURNING *
396 `;
397 return rows[0] ?? null;
398};
399
400export const updateCustomDomainVerification = async (id: string, verified: boolean) => {
401 const rows = await db`
402 UPDATE custom_domains
403 SET verified = ${verified}, last_verified_at = EXTRACT(EPOCH FROM NOW())
404 WHERE id = ${id}
405 RETURNING *
406 `;
407 return rows[0] ?? null;
408};
409
410export const deleteCustomDomain = async (id: string) => {
411 await db`DELETE FROM custom_domains WHERE id = ${id}`;
412};
413
414export const getSitesByDid = async (did: string) => {
415 const rows = await db`SELECT * FROM sites WHERE did = ${did} ORDER BY created_at DESC`;
416 return rows;
417};
418
419export const upsertSite = async (did: string, rkey: string, displayName?: string) => {
420 try {
421 // Only set display_name if provided (not undefined/null/empty)
422 const cleanDisplayName = displayName && displayName.trim() ? displayName.trim() : null;
423
424 await db`
425 INSERT INTO sites (did, rkey, display_name, created_at, updated_at)
426 VALUES (${did}, ${rkey}, ${cleanDisplayName}, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW()))
427 ON CONFLICT (did, rkey)
428 DO UPDATE SET
429 display_name = CASE
430 WHEN EXCLUDED.display_name IS NOT NULL THEN EXCLUDED.display_name
431 ELSE sites.display_name
432 END,
433 updated_at = EXTRACT(EPOCH FROM NOW())
434 `;
435 return { success: true };
436 } catch (err) {
437 console.error('Failed to upsert site', err);
438 return { success: false, error: err };
439 }
440};
441
442export const deleteSite = async (did: string, rkey: string) => {
443 try {
444 await db`DELETE FROM sites WHERE did = ${did} AND rkey = ${rkey}`;
445 return { success: true };
446 } catch (err) {
447 console.error('Failed to delete site', err);
448 return { success: false, error: err };
449 }
450};
451
452// Get all domains (wisp + custom) mapped to a specific site
453export const getDomainsBySite = async (did: string, rkey: string) => {
454 const domains: Array<{
455 type: 'wisp' | 'custom';
456 domain: string;
457 verified?: boolean;
458 id?: string;
459 }> = [];
460
461 // Check wisp domain
462 const wispDomain = await db`
463 SELECT domain, rkey FROM domains
464 WHERE did = ${did} AND rkey = ${rkey}
465 `;
466 if (wispDomain.length > 0) {
467 domains.push({
468 type: 'wisp',
469 domain: wispDomain[0].domain,
470 });
471 }
472
473 // Check custom domains
474 const customDomains = await db`
475 SELECT id, domain, verified FROM custom_domains
476 WHERE did = ${did} AND rkey = ${rkey}
477 ORDER BY created_at DESC
478 `;
479 for (const cd of customDomains) {
480 domains.push({
481 type: 'custom',
482 domain: cd.domain,
483 verified: cd.verified,
484 id: cd.id,
485 });
486 }
487
488 return domains;
489};
490
491// Get count of domains mapped to a specific site
492export const getDomainCountBySite = async (did: string, rkey: string) => {
493 const wispCount = await db`
494 SELECT COUNT(*) as count FROM domains
495 WHERE did = ${did} AND rkey = ${rkey}
496 `;
497
498 const customCount = await db`
499 SELECT COUNT(*) as count FROM custom_domains
500 WHERE did = ${did} AND rkey = ${rkey}
501 `;
502
503 return {
504 wisp: Number(wispCount[0]?.count || 0),
505 custom: Number(customCount[0]?.count || 0),
506 total: Number(wispCount[0]?.count || 0) + Number(customCount[0]?.count || 0),
507 };
508};
509
510// Cookie secret management - ensure we have a secret for signing cookies
511export const getCookieSecret = async (): Promise<string> => {
512 // Check if secret already exists
513 const rows = await db`SELECT secret FROM cookie_secrets WHERE id = 'default' LIMIT 1`;
514
515 if (rows.length > 0) {
516 return rows[0].secret as string;
517 }
518
519 // Generate new secret if none exists
520 const secret = crypto.randomUUID() + crypto.randomUUID(); // 72 character random string
521 await db`
522 INSERT INTO cookie_secrets (id, secret, created_at)
523 VALUES ('default', ${secret}, EXTRACT(EPOCH FROM NOW()))
524 `;
525
526 console.log('[CookieSecret] Generated new cookie signing secret');
527 return secret;
528};