ptr.pet
forked from
nekomimi.pet/wisp.place-monorepo
Monorepo for Wisp.place. A static site hosting service built on top of the AT Protocol.
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
84export interface SiteRecord {
85 did: string;
86 rkey: string;
87 display_name?: string;
88}
89
90export async function getAllSites(): Promise<SiteRecord[]> {
91 try {
92 const result = await sql<SiteRecord[]>`
93 SELECT did, rkey, display_name FROM sites
94 ORDER BY created_at DESC
95 `;
96 return result;
97 } catch (err) {
98 console.error('Failed to get all sites', err);
99 return [];
100 }
101}
102
103/**
104 * Generate a numeric lock ID from a string key
105 * PostgreSQL advisory locks use bigint (64-bit signed integer)
106 */
107function stringToLockId(key: string): bigint {
108 const hash = createHash('sha256').update(key).digest('hex');
109 // Take first 16 hex characters (64 bits) and convert to bigint
110 const hashNum = BigInt('0x' + hash.substring(0, 16));
111 // Keep within signed int64 range
112 return hashNum & 0x7FFFFFFFFFFFFFFFn;
113}
114
115/**
116 * Acquire a distributed lock using PostgreSQL advisory locks
117 * Returns true if lock was acquired, false if already held by another instance
118 * Lock is automatically released when the transaction ends or connection closes
119 */
120export async function tryAcquireLock(key: string): Promise<boolean> {
121 const lockId = stringToLockId(key);
122
123 try {
124 const result = await sql`SELECT pg_try_advisory_lock(${Number(lockId)}) as acquired`;
125 return result[0]?.acquired === true;
126 } catch (err) {
127 console.error('Failed to acquire lock', { key, error: err });
128 return false;
129 }
130}
131
132/**
133 * Release a distributed lock
134 */
135export async function releaseLock(key: string): Promise<void> {
136 const lockId = stringToLockId(key);
137
138 try {
139 await sql`SELECT pg_advisory_unlock(${Number(lockId)})`;
140 } catch (err) {
141 console.error('Failed to release lock', { key, error: err });
142 }
143}
144
145export { sql };