Monorepo for wisp.place. A static site hosting service built on top of the AT Protocol.
wisp.place
nekomimi.pet
1import postgres from 'postgres';
2import { createHash } from 'crypto';
3
4const sql = postgres(
5 process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/wisp',
6 {
7 max: 10,
8 idle_timeout: 20,
9 }
10);
11
12export interface DomainLookup {
13 did: string;
14 rkey: string | null;
15}
16
17export interface CustomDomainLookup {
18 id: string;
19 domain: string;
20 did: string;
21 rkey: string | null;
22 verified: boolean;
23}
24
25
26
27export async function getWispDomain(domain: string): Promise<DomainLookup | null> {
28 const key = domain.toLowerCase();
29
30 // Query database
31 const result = await sql<DomainLookup[]>`
32 SELECT did, rkey FROM domains WHERE domain = ${key} LIMIT 1
33 `;
34 const data = result[0] || null;
35
36 return data;
37}
38
39export async function getCustomDomain(domain: string): Promise<CustomDomainLookup | null> {
40 const key = domain.toLowerCase();
41
42 // Query database
43 const result = await sql<CustomDomainLookup[]>`
44 SELECT id, domain, did, rkey, verified FROM custom_domains
45 WHERE domain = ${key} AND verified = true LIMIT 1
46 `;
47 const data = result[0] || null;
48
49 return data;
50}
51
52export async function getCustomDomainByHash(hash: string): Promise<CustomDomainLookup | null> {
53 // Query database
54 const result = await sql<CustomDomainLookup[]>`
55 SELECT id, domain, did, rkey, verified FROM custom_domains
56 WHERE id = ${hash} AND verified = true LIMIT 1
57 `;
58 const data = result[0] || null;
59
60 return data;
61}
62
63export async function upsertSite(did: string, rkey: string, displayName?: string) {
64 try {
65 // Only set display_name if provided (not undefined/null/empty)
66 const cleanDisplayName = displayName && displayName.trim() ? displayName.trim() : null;
67
68 await sql`
69 INSERT INTO sites (did, rkey, display_name, created_at, updated_at)
70 VALUES (${did}, ${rkey}, ${cleanDisplayName}, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW()))
71 ON CONFLICT (did, rkey)
72 DO UPDATE SET
73 display_name = CASE
74 WHEN EXCLUDED.display_name IS NOT NULL THEN EXCLUDED.display_name
75 ELSE sites.display_name
76 END,
77 updated_at = EXTRACT(EPOCH FROM NOW())
78 `;
79 } catch (err) {
80 console.error('Failed to upsert site', err);
81 }
82}
83
84/**
85 * Generate a numeric lock ID from a string key
86 * PostgreSQL advisory locks use bigint (64-bit signed integer)
87 */
88function stringToLockId(key: string): bigint {
89 const hash = createHash('sha256').update(key).digest('hex');
90 // Take first 16 hex characters (64 bits) and convert to bigint
91 const hashNum = BigInt('0x' + hash.substring(0, 16));
92 // Keep within signed int64 range
93 return hashNum & 0x7FFFFFFFFFFFFFFFn;
94}
95
96/**
97 * Acquire a distributed lock using PostgreSQL advisory locks
98 * Returns true if lock was acquired, false if already held by another instance
99 * Lock is automatically released when the transaction ends or connection closes
100 */
101export async function tryAcquireLock(key: string): Promise<boolean> {
102 const lockId = stringToLockId(key);
103
104 try {
105 const result = await sql`SELECT pg_try_advisory_lock(${Number(lockId)}) as acquired`;
106 return result[0]?.acquired === true;
107 } catch (err) {
108 console.error('Failed to acquire lock', { key, error: err });
109 return false;
110 }
111}
112
113/**
114 * Release a distributed lock
115 */
116export async function releaseLock(key: string): Promise<void> {
117 const lockId = stringToLockId(key);
118
119 try {
120 await sql`SELECT pg_advisory_unlock(${Number(lockId)})`;
121 } catch (err) {
122 console.error('Failed to release lock', { key, error: err });
123 }
124}
125
126export { sql };