spb/sp-drive

1import postgres from 'postgres';2import { config } from './config.ts';3import * as m001 from './migrations/001_init.ts';45export const sql = postgres(config.databaseUrl, { max: 12, idle_timeout: 30, onnotice: () => {}, types: { bigint: postgres.BigInt } });6export type Sql = typeof sql;7/** A transaction handle or the pool: every query helper takes one so it can join the caller's transaction. */8export type Db = postgres.Sql | postgres.TransactionSql;9export const tx = <T>(fn: (t: postgres.TransactionSql) => Promise<T>): Promise<T> => sql.begin(fn) as Promise<T>;1011export const MIGRATIONS = [m001];1213export async function migrate(target?: string): Promise<string[]> {14  await sql`create table if not exists schema_migrations (id text primary key, applied_at timestamptz not null default now())`;15  const done: string[] = [];16  // one migrator at a time (API and worker may start together)17  await sql.begin(async (t) => {18    await t`select pg_advisory_xact_lock(7420660001)`;19    const applied = new Set((await t<{ id: string }[]>`select id from schema_migrations`).map((r) => r.id));20    for (const m of MIGRATIONS) {21      if (applied.has(m.id)) continue;22      await t.unsafe(m.up);23      await t`insert into schema_migrations (id) values (${m.id})`;24      done.push(m.id);25      if (m.id === target) break;26    }27  });28  return done;29}30/** Revert the most recent migration (data in its tables is lost: only on explicit operator request). */31export async function migrateDown(): Promise<string | null> {32  return sql.begin(async (t) => {33    await t`select pg_advisory_xact_lock(7420660001)`;34    const [last] = await t<{ id: string }[]>`select id from schema_migrations order by id desc limit 1`;35    const m = MIGRATIONS.find((x) => x.id === last?.id);36    if (!m) return null;37    await t.unsafe(m.down);38    await t`delete from schema_migrations where id = ${m.id}`;39    return m.id;40  }) as Promise<string | null>;41}4243/** Serialises every change to one owner's tree (names, moves, quota) inside the caller's transaction. */44export const lockOwner = (t: Db, ownerId: string) => t`select pg_advisory_xact_lock(hashtextextended(${'spd-tree:' + ownerId}, 0))`;4546export interface Actor { kind: 'user' | 'anonymous' | 'system'; userId?: string | null; ip?: string; userAgent?: string }47export const SYSTEM: Actor = { kind: 'system' };48/** Structured audit trail. Never pass document content, tokens or passwords in `meta`. */49export async function audit(t: Db, actor: Actor, action: string, o: { ownerId?: string | null; targetType?: string; targetId?: string; meta?: Record<string, unknown> } = {}): Promise<void> {50  await t`insert into audit_events (actor_kind, actor_user_id, owner_id, action, target_type, target_id, ip, user_agent, meta)51    values (${actor.kind}, ${actor.userId ?? null}, ${o.ownerId ?? actor.userId ?? null}, ${action}, ${o.targetType ?? ''}, ${o.targetId ?? ''},52      ${actor.ip ?? ''}, ${(actor.userAgent ?? '').slice(0, 300)}, ${t.json((o.meta ?? {}) as never)})`;53}54export async function notify(t: Db, userId: string, kind: string, data: Record<string, unknown>): Promise<void> {55  await t`insert into notifications (user_id, kind, data) values (${userId}, ${kind}, ${t.json(data as never)})`;56}57export const isUuid = (s: unknown): s is string => typeof s === 'string' && /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(s);58