import type {
GrantKind,
PrivateSessionRecord,
PrivateSite,
PrivateSiteShare,
PrivateSiteState,
} from '@wispplace/private-sites'
import postgres from 'postgres'
const privateSitesDatabaseUrl = process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/wisp'
// This is a deliberately bounded primary-only pool. Every query below affects
// authorization, expiry, or private metadata, so it must never use a replica.
const sql = postgres(privateSitesDatabaseUrl, {
max: 5,
idle_timeout: 20,
})
let privateSitesDatabaseClosePromise: Promise<void> | undefined
/**
* Close the separate private-sites pool during graceful shutdown.
* This function is idempotent so repeated shutdown signals cannot end it twice.
*/
export function closePrivateSitesDatabase(): Promise<void> {
if (privateSitesDatabaseClosePromise) return privateSitesDatabaseClosePromise
privateSitesDatabaseClosePromise = (async () => {
try {
await sql.end({ timeout: 5 })
console.log('[Private sites DB] Database connections closed')
} catch {
console.error('[Private sites DB] Database pool failed to close cleanly')
}
})()
return privateSitesDatabaseClosePromise
}
interface PrivateSiteRow {
site_id: string
owner_did: string
name: string
file_count: number
total_bytes: string | number
state: PrivateSiteState
expires_at: Date | string | null
created_at: Date | string
updated_at: Date | string
}
interface PrivateShareRow {
share_id: string
site_id: string
token_hash: string
token_prefix: string
label: string | null
audience_did: string | null
expires_at: Date | string | null
revoked_at: Date | string | null
created_at: Date | string
last_used_at: Date | string | null
}
/** Minimal primary-query surface, also used by deterministic query-plan tests. */
export type PrivateSitesQueryExecutor = typeof sql
const asDate = (value: Date | string): Date => (value instanceof Date ? value : new Date(value))
const asDateOrNull = (value: Date | string | null): Date | null => (value === null ? null : asDate(value))
// Private MIME metadata is later used as an HTTP header. Drop malformed legacy
// values rather than allowing controls or oversized data into a response.
const safeMimeType = (value: string | null): string | null =>
value && value.length <= 255 && /^[\x20-\x7E]+$/.test(value) ? value : null
const mapSite = (row: PrivateSiteRow): PrivateSite => ({
state: row.state,
siteId: row.site_id,
ownerDid: row.owner_did,
name: row.name,
fileCount: Number(row.file_count),
totalBytes: Number(row.total_bytes),
expiresAt: asDateOrNull(row.expires_at),
createdAt: asDate(row.created_at),
updatedAt: asDate(row.updated_at),
})
const mapShare = (row: PrivateShareRow): PrivateSiteShare => ({
shareId: row.share_id,
siteId: row.site_id,
tokenHash: row.token_hash,
tokenPrefix: row.token_prefix,
label: row.label,
audienceDid: row.audience_did,
expiresAt: asDateOrNull(row.expires_at),
revokedAt: asDateOrNull(row.revoked_at),
createdAt: asDate(row.created_at),
lastUsedAt: asDateOrNull(row.last_used_at),
})
export interface PrivateSiteFile {
path: string
size: number
mimeType: string | null
}
export interface AuthorizedPrivateSite {
site: PrivateSite
files: PrivateSiteFile[]
}
interface AuthorizedPrivateSiteRow extends PrivateSiteRow {
file_path: string | null
file_size: string | number | null
file_mime_type: string | null
}
/**
* Compatibility lookup. It is still primary-only and only exposes a currently
* live ready site; serving should use loadAuthorizedPrivateSite instead.
*/
export async function getPrivateSite(siteId: string): Promise<PrivateSite | null> {
const rows = await sql<PrivateSiteRow[]>`
SELECT site_id, owner_did, name, file_count, total_bytes, state, expires_at, created_at, updated_at
FROM private_sites
WHERE site_id = ${siteId}
AND state = 'ready'
AND (expires_at IS NULL OR expires_at > NOW())
LIMIT 1
`
return rows[0] ? mapSite(rows[0]) : null
}
export async function findSharesByTokenHash(siteId: string, tokenHash: string): Promise<PrivateSiteShare[]> {
const rows = await sql<PrivateShareRow[]>`
SELECT share.share_id, share.site_id, share.token_hash, share.token_prefix, share.label,
share.audience_did, share.expires_at, share.revoked_at, share.created_at, share.last_used_at
FROM private_site_shares AS share
JOIN private_sites AS site ON site.site_id = share.site_id
WHERE share.site_id = ${siteId}
AND share.token_hash = ${tokenHash}
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
`
return rows.map(mapShare)
}
export async function listPrivateSiteFiles(siteId: string): Promise<PrivateSiteFile[]> {
const rows = await sql<Array<{ path: string; size: string | number; mime_type: string | null }>>`
SELECT file.path, file.size, file.mime_type
FROM private_site_files AS file
JOIN private_sites AS site ON site.site_id = file.site_id
WHERE file.site_id = ${siteId}
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
ORDER BY file.path ASC
`
return rows.map((row) => ({ path: row.path, size: Number(row.size), mimeType: safeMimeType(row.mime_type) }))
}
interface SessionRow {
session_id: string
site_id: string
kind: string
owner_did: string | null
share_id: string | null
expires_at: Date | string
created_at: Date | string
}
/** Compatibility lookup; primary serving uses the one-query aggregate below. */
export async function findLiveSession(secretHash: string): Promise<PrivateSessionRecord | null> {
const rows = await sql<SessionRow[]>`
SELECT private_session.session_id, private_session.site_id, private_session.kind, private_session.owner_did, private_session.share_id,
private_session.expires_at, private_session.created_at
FROM private_site_sessions AS private_session
JOIN private_sites AS site ON site.site_id = private_session.site_id
LEFT JOIN private_site_shares AS share ON share.share_id = private_session.share_id
WHERE private_session.secret_hash = ${secretHash}
AND private_session.expires_at > NOW()
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND (
(private_session.kind = 'owner' AND private_session.owner_did = site.owner_did AND private_session.share_id IS NULL)
OR (
private_session.kind = 'share'
AND private_session.share_id IS NOT NULL
AND share.site_id = site.site_id
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
)
)
LIMIT 1
`
const row = rows[0]
if (!row) return null
return {
sessionId: row.session_id,
siteId: row.site_id,
kind: row.kind as GrantKind,
ownerDid: row.owner_did,
shareId: row.share_id,
expiresAt: asDate(row.expires_at),
createdAt: asDate(row.created_at),
}
}
/**
* One primary round trip for the normal cookie path. It uses only the cookie
* hash and joins ready/unexpired site, session, share, and file metadata.
*/
export const loadAuthorizedPrivateSiteWithExecutor = async (
executor: PrivateSitesQueryExecutor,
siteId: string,
sessionSecretHash: string,
): Promise<AuthorizedPrivateSite | null> => {
const rows = await executor<AuthorizedPrivateSiteRow[]>`
SELECT
site.site_id, site.owner_did, site.name, site.file_count, site.total_bytes,
site.state, site.expires_at, site.created_at, site.updated_at,
file.path AS file_path, file.size AS file_size, file.mime_type AS file_mime_type
FROM private_sites AS site
JOIN private_site_sessions AS private_session ON private_session.site_id = site.site_id
LEFT JOIN private_site_shares AS share ON share.share_id = private_session.share_id
LEFT JOIN private_site_files AS file ON file.site_id = site.site_id
WHERE site.site_id = ${siteId}
AND private_session.secret_hash = ${sessionSecretHash}
AND private_session.expires_at > NOW()
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND (
(private_session.kind = 'owner' AND private_session.owner_did = site.owner_did AND private_session.share_id IS NULL)
OR (
private_session.kind = 'share'
AND private_session.share_id IS NOT NULL
AND share.site_id = site.site_id
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
)
)
ORDER BY file.path ASC
`
const first = rows[0]
if (!first) return null
return {
site: mapSite(first),
files: rows.flatMap((row) =>
row.file_path === null || row.file_size === null
? []
: [{ path: row.file_path, size: Number(row.file_size), mimeType: safeMimeType(row.file_mime_type) }],
),
}
}
export const loadAuthorizedPrivateSite = async (
siteId: string,
sessionSecretHash: string,
): Promise<AuthorizedPrivateSite | null> => await loadAuthorizedPrivateSiteWithExecutor(sql, siteId, sessionSecretHash)
export async function createSession(input: {
sessionId: string
secretHash: string
siteId: string
kind: GrantKind
ownerDid: string | null
shareId: string | null
expiresAt: Date
}): Promise<boolean> {
const rows = await sql`
INSERT INTO private_site_sessions (session_id, secret_hash, site_id, kind, owner_did, share_id, expires_at)
SELECT ${input.sessionId}, ${input.secretHash}, site.site_id, ${input.kind}, ${input.ownerDid}, ${input.shareId}, ${input.expiresAt}
FROM private_sites AS site
LEFT JOIN private_site_shares AS share ON share.share_id = ${input.shareId}
WHERE site.site_id = ${input.siteId}
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND (
(${input.kind} = 'owner' AND ${input.ownerDid} = site.owner_did AND ${input.shareId} IS NULL)
OR (
${input.kind} = 'share'
AND ${input.shareId} IS NOT NULL
AND share.site_id = site.site_id
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
)
)
RETURNING session_id
`
return rows.length > 0
}
export interface PrivateHandoffExchangeInput {
siteId: string
secretHash: string
sessionId: string
sessionSecretHash: string
expiresAt: Date
}
export type PrivateHandoffExchangeResult = { kind: 'owner'; ownerDid: string } | { kind: 'share'; shareId: string }
/** Atomically validate+consume a handoff and mint its session on the primary. */
export async function exchangePrivateHandoff(
input: PrivateHandoffExchangeInput,
): Promise<PrivateHandoffExchangeResult | null> {
const rows = await sql<Array<{ kind: string; owner_did: string | null; share_id: string | null }>>`
WITH consumed AS (
UPDATE private_site_handoffs AS handoff
SET consumed_at = NOW()
FROM private_sites AS site
WHERE handoff.secret_hash = ${input.secretHash}
AND handoff.site_id = ${input.siteId}
AND handoff.consumed_at IS NULL
AND handoff.expires_at > NOW()
AND site.site_id = handoff.site_id
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND (
(handoff.owner_did IS NOT NULL AND handoff.owner_did = site.owner_did AND handoff.share_id IS NULL)
OR (
handoff.share_id IS NOT NULL
AND handoff.owner_did IS NULL
AND EXISTS (
SELECT 1
FROM private_site_shares AS share
WHERE share.share_id = handoff.share_id
AND share.site_id = site.site_id
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
)
)
)
RETURNING handoff.site_id, handoff.owner_did, handoff.share_id
), session AS (
INSERT INTO private_site_sessions (session_id, secret_hash, site_id, kind, owner_did, share_id, expires_at)
SELECT
${input.sessionId},
${input.sessionSecretHash},
consumed.site_id,
CASE WHEN consumed.owner_did IS NULL THEN 'share' ELSE 'owner' END,
consumed.owner_did,
consumed.share_id,
${input.expiresAt}
FROM consumed
RETURNING kind, owner_did, share_id
)
SELECT kind, owner_did, share_id FROM session
`
const row = rows[0]
if (!row) return null
if (row.kind === 'owner' && row.owner_did) return { kind: 'owner', ownerDid: row.owner_did }
if (row.kind === 'share' && row.share_id) return { kind: 'share', shareId: row.share_id }
return null
}
export interface PrivateShareTokenExchangeInput {
siteId: string
tokenHash: string
sessionId: string
sessionSecretHash: string
expiresAt: Date
}
export type PrivateShareTokenExchangeResult =
| { kind: 'share'; shareId: string }
| { kind: 'audienceMismatch'; audienceDid: string }
/**
* Validates a token and mints an unscoped session in one statement. A scoped
* token returns its sign-in instruction only when the token itself is live.
*/
export const exchangePrivateShareTokenWithExecutor = async (
executor: PrivateSitesQueryExecutor,
input: PrivateShareTokenExchangeInput,
): Promise<PrivateShareTokenExchangeResult | null> => {
const rows = await executor<Array<{ kind: string; share_id: string; audience_did: string | null }>>`
WITH candidate AS (
SELECT site.site_id, share.share_id, share.audience_did
FROM private_sites AS site
JOIN private_site_shares AS share ON share.site_id = site.site_id
WHERE site.site_id = ${input.siteId}
AND share.token_hash = ${input.tokenHash}
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
LIMIT 1
), created AS (
INSERT INTO private_site_sessions (session_id, secret_hash, site_id, kind, owner_did, share_id, expires_at)
SELECT ${input.sessionId}, ${input.sessionSecretHash}, candidate.site_id, 'share', NULL, candidate.share_id, ${input.expiresAt}
FROM candidate
WHERE candidate.audience_did IS NULL
RETURNING share_id
)
SELECT 'share'::TEXT AS kind, created.share_id, NULL::TEXT AS audience_did
FROM created
UNION ALL
SELECT 'audienceMismatch'::TEXT AS kind, candidate.share_id, candidate.audience_did
FROM candidate
WHERE candidate.audience_did IS NOT NULL
`
const row = rows[0]
if (!row) return null
if (row.kind === 'share') return { kind: 'share', shareId: row.share_id }
if (row.kind === 'audienceMismatch' && row.audience_did !== null) {
return { kind: 'audienceMismatch', audienceDid: row.audience_did }
}
return null
}
export const exchangePrivateShareToken = async (
input: PrivateShareTokenExchangeInput,
): Promise<PrivateShareTokenExchangeResult | null> => await exchangePrivateShareTokenWithExecutor(sql, input)
/** Compatibility primitive; the atomic exchange above is preferred. */
export async function consumeHandoff(
secretHash: string,
siteId: string,
): Promise<{ siteId: string; ownerDid: string | null; shareId: string | null } | null> {
const rows = await sql<Array<{ site_id: string; owner_did: string | null; share_id: string | null }>>`
UPDATE private_site_handoffs AS handoff
SET consumed_at = NOW()
FROM private_sites AS site
WHERE handoff.secret_hash = ${secretHash}
AND handoff.site_id = ${siteId}
AND handoff.consumed_at IS NULL
AND handoff.expires_at > NOW()
AND site.site_id = handoff.site_id
AND site.state = 'ready'
AND (site.expires_at IS NULL OR site.expires_at > NOW())
AND (
(handoff.owner_did IS NOT NULL AND handoff.owner_did = site.owner_did AND handoff.share_id IS NULL)
OR (
handoff.share_id IS NOT NULL
AND handoff.owner_did IS NULL
AND EXISTS (
SELECT 1
FROM private_site_shares AS share
WHERE share.share_id = handoff.share_id
AND share.site_id = site.site_id
AND share.revoked_at IS NULL
AND (share.expires_at IS NULL OR share.expires_at > NOW())
)
)
)
RETURNING handoff.site_id, handoff.owner_did, handoff.share_id
`
const row = rows[0]
return row ? { siteId: row.site_id, ownerDid: row.owner_did, shareId: row.share_id } : null
}
export async function touchShare(shareId: string): Promise<void> {
try {
await sql`UPDATE private_site_shares SET last_used_at = NOW() WHERE share_id = ${shareId}`
} catch {}
}