server/db.mjs

// Postgres manifest store (optional). When DATABASE_URL is set, project manifests live in a `projects`
// table (the whole manifest as a jsonb column, owner_id mirrored out for fast per-user filtering)
// instead of JSON files on disk. Media files (assets/audio) still live on disk for now — object
// storage (R2) is a later step. When DATABASE_URL is NOT set, projects.mjs falls back to file storage.
//
//   editor/.env:  DATABASE_URL=postgresql://user:pass@host:5432/dbname?sslmode=require   (Neon / Supabase / local)
import pg from "pg";

const { Pool } = pg;
let pool = null;

export const pgEnabled = () => !!process.env.DATABASE_URL;

// TLS: required for hosted Postgres, skipped for local. Use a localhost/private-IP DENYLIST (default
// SSL on) rather than a vendor allowlist, so Azure/DO/GCP/etc. work without being individually listed.
const needsSSL = () => {
  const u = process.env.DATABASE_URL || "";
  if (/sslmode=disable/i.test(u)) return false;
  if (/sslmode=require|ssl=true|ssl=1/i.test(u)) return true;
  const host = (u.match(/@([^:/?]+)/) || [])[1] || "";
  const local = /^(localhost|127\.0\.0\.1|\[?::1\]?)$/i.test(host)
    || /^(10\.|192\.168\.|172\.(1[6-9]|2\d|3[01])\.)/.test(host);
  return !!host && !local;
};

function getPool() {
  if (!pool) {
    pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 8,
      ssl: needsSSL() ? { rejectUnauthorized: false } : undefined,
      connectionTimeoutMillis: 5000,  // a dead/paused host fails in 5s, not the ~2min kernel TCP timeout
      idleTimeoutMillis: 30000,
      statement_timeout: 15000,        // a runaway query can't pin a pool slot forever
    });
    // CRITICAL: without this listener a dropped idle connection (hosted PG suspends idle sockets)
    // emits 'error' with no handler → Node re-throws it as an uncaught exception and kills the process.
    pool.on("error", (err) => console.error("[pg] idle client error:", err.message));
  }
  return pool;
}

// graceful shutdown — close the pool so dev hot-reloads / restarts don't leak server-side connections
let _shutHooked = false;
function hookShutdown() {
  if (_shutHooked) return; _shutHooked = true;
  const close = () => { const p = pool; pool = null; if (p) p.end().catch(() => {}); };
  process.once("SIGINT", () => { close(); process.exit(0); });
  process.once("SIGTERM", () => { close(); process.exit(0); });
}

let schemaReady = null;
export function ensureSchema() {
  if (!schemaReady) {
    hookShutdown();
    schemaReady = getPool().query(`
      CREATE TABLE IF NOT EXISTS projects (
        id          text PRIMARY KEY,
        owner_id    text,
        manifest    jsonb NOT NULL,
        created_at  timestamptz NOT NULL DEFAULT now(),
        updated_at  timestamptz NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS projects_owner_idx ON projects (owner_id);

      -- abuse prevention: hashed identity signals per user/device (never raw PII) ...
      CREATE TABLE IF NOT EXISTS usage_identity_signals (
        id               bigserial PRIMARY KEY,
        user_id          text NOT NULL,
        visitor_id       text,
        ip_hash          text,
        fingerprint_hash text,
        email_hash       text,
        phone_hash       text,
        device_key       text,
        created_at       timestamptz NOT NULL DEFAULT now(),
        last_seen_at     timestamptz NOT NULL DEFAULT now(),
        UNIQUE (user_id, device_key)
      );
      CREATE INDEX IF NOT EXISTS uis_fp_idx  ON usage_identity_signals (fingerprint_hash);
      CREATE INDEX IF NOT EXISTS uis_vid_idx ON usage_identity_signals (visitor_id);
      CREATE INDEX IF NOT EXISTS uis_ip_idx  ON usage_identity_signals (ip_hash);

      -- ... and the per-device free-quota bucket (a shared key across the device's accounts)
      CREATE TABLE IF NOT EXISTS quota_usage (
        id           bigserial PRIMARY KEY,
        identity_key text UNIQUE NOT NULL,
        usage_count  integer NOT NULL DEFAULT 0,
        quota_limit  integer NOT NULL DEFAULT 100,
        reset_at     timestamptz,
        created_at   timestamptz NOT NULL DEFAULT now(),
        updated_at   timestamptz NOT NULL DEFAULT now()
      );

      -- AI request + cost log (Bundle 3)
      CREATE TABLE IF NOT EXISTS ai_request_log (
        id          bigserial PRIMARY KEY,
        user_id     text,
        route       text,
        model       text,
        in_tokens   integer,
        out_tokens  integer,
        est_cost    numeric(12,6),
        duration_ms integer,
        ok          boolean,
        created_at  timestamptz NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS ai_log_user_idx ON ai_request_log (user_id);
      CREATE INDEX IF NOT EXISTS ai_log_time_idx ON ai_request_log (created_at);

      -- priority support tickets (paid customers escalate here; the owner sees them in the dashboard)
      CREATE TABLE IF NOT EXISTS support_tickets (
        id          text PRIMARY KEY,
        user_id     text,
        tier        text,
        email       text,
        subject     text,
        message     text,
        transcript  jsonb,
        status      text NOT NULL DEFAULT 'open',
        created_at  timestamptz NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS tickets_time_idx ON support_tickets (created_at);
    `).catch((e) => { schemaReady = null; throw e; }); // let a transient failure retry next call
  }
  return schemaReady;
}

// generic query helper for the abuse / logging services (manifest CRUD has its own functions above)
export async function pgQuery(text, params) {
  await ensureSchema();
  return getPool().query(text, params);
}

// --- manifest CRUD (the manifest is the whole project JSON; owner_id is mirrored to a column) ---

export async function pgRead(id) {
  await ensureSchema();
  const { rows } = await getPool().query("SELECT manifest FROM projects WHERE id = $1", [id]);
  return rows[0]?.manifest ?? null; // jsonb → JS object (node-pg parses it)
}

export async function pgExists(id) {
  await ensureSchema();
  const { rows } = await getPool().query("SELECT 1 FROM projects WHERE id = $1", [id]);
  return rows.length > 0;
}

export async function pgWrite(m) {
  await ensureSchema();
  await getPool().query(
    `INSERT INTO projects (id, owner_id, manifest, created_at, updated_at)
       VALUES ($1, $2, $3::jsonb, COALESCE($4::timestamptz, now()), now())
     ON CONFLICT (id) DO UPDATE
       SET owner_id = EXCLUDED.owner_id, manifest = EXCLUDED.manifest, updated_at = now()`,
    [m.id, m.ownerId ?? null, JSON.stringify(m), m.createdAt || null],
  );
  return m;
}

export async function pgList(userId) {
  await ensureSchema();
  // userId null → everything (dev/admin); else this user's rows PLUS shared/ownerless ones
  const { rows } = await getPool().query(
    `SELECT manifest FROM projects
       WHERE $1::text IS NULL OR owner_id IS NULL OR owner_id = $1
       ORDER BY updated_at DESC`,
    [userId ?? null],
  );
  return rows.map((r) => r.manifest);
}

export async function pgRemove(id) {
  await ensureSchema();
  const { rowCount } = await getPool().query("DELETE FROM projects WHERE id = $1", [id]);
  return rowCount > 0;
}