wellformed

import type Database from 'better-sqlite3';
import { openSqlite } from '../db/driver.js';

/**
 * The control plane.
 *
 * Exactly one of these exists per installation. It holds the things that span
 * workspaces — accounts, which workspaces exist, who belongs to which, and the
 * sessions and invites that connect the two. It holds no customer data: every
 * table, row, view and audit line lives in the workspace's own database file.
 *
 * Its tables are unprefixed. A workspace database shares its namespace with
 * user-created tables and therefore needs the `_wellformed_` prefix; this one never
 * does, so the plain names read better and mean what they say.
 */
export interface ControlDb {
  sqlite: Database.Database;
}

interface Migration {
  version: number;
  name: string;
  up: string;
}

/** Forward-only, same rule as the workspace migrations: never edit a shipped one. */
const MIGRATIONS: Migration[] = [
  {
    version: 1,
    name: 'accounts, workspaces, memberships',
    up: `
      CREATE TABLE account (
        id            TEXT PRIMARY KEY,
        email         TEXT NOT NULL UNIQUE,
        name          TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        disabled      INTEGER NOT NULL DEFAULT 0,
        created_at    TEXT NOT NULL DEFAULT (datetime('now')),
        last_seen_at  TEXT
      );

      CREATE TABLE workspace (
        id         TEXT PRIMARY KEY,
        slug       TEXT NOT NULL UNIQUE,
        name       TEXT NOT NULL,
        /* Relative to the tenant directory, or absolute for an adopted file. */
        db_file    TEXT NOT NULL UNIQUE,
        status     TEXT NOT NULL DEFAULT 'active',
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        created_by TEXT
      );

      CREATE TABLE membership (
        workspace_id TEXT NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
        account_id   TEXT NOT NULL REFERENCES account(id) ON DELETE CASCADE,
        role         TEXT NOT NULL,
        created_at   TEXT NOT NULL DEFAULT (datetime('now')),
        PRIMARY KEY (workspace_id, account_id)
      );
      CREATE INDEX idx_membership_account ON membership(account_id);

      CREATE TABLE session (
        token_hash TEXT PRIMARY KEY,
        account_id TEXT NOT NULL REFERENCES account(id) ON DELETE CASCADE,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        expires_at TEXT NOT NULL,
        user_agent TEXT
      );
      CREATE INDEX idx_session_account ON session(account_id);

      CREATE TABLE invite (
        id           TEXT PRIMARY KEY,
        token_hash   TEXT NOT NULL UNIQUE,
        workspace_id TEXT NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
        email        TEXT NOT NULL,
        role         TEXT NOT NULL,
        invited_by   TEXT,
        created_at   TEXT NOT NULL DEFAULT (datetime('now')),
        expires_at   TEXT NOT NULL,
        accepted_at  TEXT,
        accepted_by  TEXT
      );
      CREATE INDEX idx_invite_workspace ON invite(workspace_id, accepted_at);
    `,
  },
  {
    version: 2,
    name: 'account tokens: password reset and email verification',
    up: `
      /*
       * One table for both, because they are the same object: a random secret,
       * stored hashed, that stands in for the account exactly once and then
       * expires. Only the purpose and the lifetime differ.
       */
      CREATE TABLE account_token (
        id         TEXT PRIMARY KEY,
        token_hash TEXT NOT NULL UNIQUE,
        account_id TEXT NOT NULL REFERENCES account(id) ON DELETE CASCADE,
        purpose    TEXT NOT NULL,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        expires_at TEXT NOT NULL,
        used_at    TEXT
      );
      CREATE INDEX idx_account_token_account ON account_token(account_id, purpose, used_at);

      /* Null means "never confirmed", including for every account made before this. */
      ALTER TABLE account ADD COLUMN verified_at TEXT;
    `,
  },
  {
    version: 3,
    name: 'billing: a plan per workspace',
    up: `
      /*
       * Billing hangs off the workspace, because a workspace is what a customer
       * buys: one database, one bill, one thing to cancel. Nothing here belongs
       * to an account — the same person can be a guest in a paid workspace and
       * never see an invoice for it.
       *
       * Existing workspaces default to 'free' with no trial, which is the
       * honest reading of "they were here before there was a price".
       */
      ALTER TABLE workspace ADD COLUMN plan TEXT NOT NULL DEFAULT 'free';
      /* active | trialing | past_due | cancelled */
      ALTER TABLE workspace ADD COLUMN billing_status TEXT NOT NULL DEFAULT 'active';
      ALTER TABLE workspace ADD COLUMN trial_ends_at TEXT;
      ALTER TABLE workspace ADD COLUMN period_end TEXT;
      ALTER TABLE workspace ADD COLUMN cancel_at_period_end INTEGER NOT NULL DEFAULT 0;
      /* Whoever is taking the money, and their ids for this workspace. */
      ALTER TABLE workspace ADD COLUMN provider TEXT;
      ALTER TABLE workspace ADD COLUMN provider_customer_id TEXT;
      ALTER TABLE workspace ADD COLUMN provider_subscription_id TEXT;

      /*
       * Every webhook that has been applied. Providers retry, and they are
       * right to: the only safe way to receive money-shaped messages is to be
       * able to receive the same one twice. The PRIMARY KEY is the idempotency.
       */
      CREATE TABLE billing_event (
        id           TEXT PRIMARY KEY,
        provider     TEXT NOT NULL,
        kind         TEXT NOT NULL,
        workspace_id TEXT,
        received_at  TEXT NOT NULL DEFAULT (datetime('now')),
        payload      TEXT NOT NULL
      );
      CREATE INDEX idx_billing_event_workspace ON billing_event(workspace_id, received_at);
    `,
  },
  {
    version: 4,
    name: 'agent keys: what an AI assistant signs in with',
    up: `
      /*
       * Hashed like a session and a one-shot token, and for the same reason: a
       * leaked control database must not be replayable. What makes this one
       * different is that it is long-lived and bound to a workspace, so the two
       * things a session carries implicitly — who, and for how long — are
       * columns here instead.
       *
       * 'ceiling' is a cap, not a grant. The role that applies is the lower of
       * it and the account's membership, resolved on every request, so a key
       * can never outlive or exceed the access of the person who made it.
       */
      CREATE TABLE agent_key (
        id           TEXT PRIMARY KEY,
        token_hash   TEXT NOT NULL UNIQUE,
        account_id   TEXT NOT NULL REFERENCES account(id) ON DELETE CASCADE,
        workspace_id TEXT NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
        label        TEXT NOT NULL,
        ceiling      TEXT NOT NULL DEFAULT 'viewer',
        created_at   TEXT NOT NULL DEFAULT (datetime('now')),
        last_used_at TEXT,
        /* Null means it lives until revoked. */
        expires_at   TEXT,
        revoked_at   TEXT
      );
      CREATE INDEX idx_agent_key_workspace ON agent_key(workspace_id, revoked_at);
      CREATE INDEX idx_agent_key_account ON agent_key(account_id);
    `,
  },
  {
    version: 5,
    name: 'billing: who the invoices are addressed to',
    up: `
      /*
       * The address the bill goes to, held by the workspace rather than borrowed
       * from whichever admin happened to click Upgrade.
       *
       * That borrowing was the one place billing still reached into an account,
       * against the rule stated in migration 3. It matters at handover: an admin
       * leaves, their mailbox is closed, and the invoices for a workspace that is
       * still being paid for keep going to it. Usually the payer is a company
       * anyway, so the address wanted here — finance@, accounts@ — is not a
       * person's to begin with.
       *
       * Null means "never set", and checkout asks for one rather than guessing.
       */
      ALTER TABLE workspace ADD COLUMN billing_email TEXT;
    `,
  },
  {
    version: 6,
    name: 'a workspace keeps its books in one currency',
    up: `
      /*
       * Currency columns used to be dollars, hard-coded in the formatter, which
       * is only right for the customers who happen to be American. It is a
       * property of the business, not of the column: one workspace, one set of
       * books, one sign in front of every amount in it.
       *
       * Existing workspaces move to the euro along with the default, rather
       * than being frozen at the dollar they never chose. Anyone who did mean
       * dollars says so once, in workspace settings.
       */
      ALTER TABLE workspace ADD COLUMN currency TEXT NOT NULL DEFAULT 'EUR';
    `,
  },
  {
    version: 7,
    name: 'the landing page as an instrument',
    up: `
      /*
       * Which lines of the landing page a visitor was shown, and what became of
       * them. The pool of lines lives in the shared package; only the ids are
       * here, so rewording the site never touches this table — see the rule
       * about permanent ids in shared/voice.ts.
       *
       * 'engaged_at' is the bot filter, and it is load-bearing rather than
       * hygiene. The measure is durable use *per impression*, so the
       * denominator is half the number; if crawlers count as impressions the
       * denominator is mostly crawlers and every weight is noise around zero.
       * A row is created when the page is served and only enters the fit once
       * the browser says a person actually looked at it.
       *
       * SET NULL rather than CASCADE: an account deleted outright leaves an
       * impression that really did happen and now has no use to show for it,
       * which is a truthful zero. Losing the row would quietly shrink the
       * denominator instead.
       */
      CREATE TABLE voice_impression (
        id         TEXT PRIMARY KEY,
        /* JSON array of item ids. */
        items      TEXT NOT NULL,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        engaged_at TEXT,
        account_id TEXT REFERENCES account(id) ON DELETE SET NULL
      );
      CREATE INDEX idx_voice_impression_fit ON voice_impression(engaged_at, created_at);
      CREATE INDEX idx_voice_impression_account ON voice_impression(account_id);

      /*
       * One row per account per day it wrote something, anywhere.
       *
       * The reward the page is tuned against is durable use, and this is the
       * only durable-use signal that lives in the control plane. Counting it
       * from the workspace audit logs would mean opening every workspace file
       * to score a landing page, which is a bad trade and a worse dependency.
       *
       * Days rather than actions on purpose: it should not matter whether
       * someone's work happens to arrive as one paste or four hundred edits.
       */
      CREATE TABLE account_day (
        account_id TEXT NOT NULL REFERENCES account(id) ON DELETE CASCADE,
        day        TEXT NOT NULL,
        PRIMARY KEY (account_id, day)
      ) WITHOUT ROWID;
    `,
  },
  {
    version: 8,
    name: 'an account reads in one language',
    up: `
      /*
       * The reader's locale, on the account rather than the workspace, because
       * it is a property of the person: the same Dutch bookkeeper reads Dutch
       * in every workspace they open, and their English colleague reads the
       * same books in English. The workspace keeps only the currency.
       *
       * Null means "never chosen" — the browser's language decides, which for
       * every account made before this migration is also the honest answer.
       * The set of valid values lives in shared/locale.ts; anything else in
       * this column reads as null rather than crashing a sign-in.
       */
      ALTER TABLE account ADD COLUMN locale TEXT;
    `,
  },
  {
    version: 9,
    name: 'the language belongs to the workspace',
    up: `
      /*
       * Migration 8 put the locale on the account, reasoning that the words in
       * the chrome belong to the reader. That was wrong in practice: a
       * workspace is full of proper names — tables, columns, views — written
       * in one language, and chrome in another language around them reads like
       * two products taped together. So the language moves to where the
       * currency already lives: one workspace, one language, one way of
       * writing its numbers and dates, the same for everyone who opens it.
       *
       * NOT NULL with a default, unlike the account column it replaces:
       * outside a workspace the browser decides, but a workspace always
       * answers. Existing workspaces get English — the language they have
       * been speaking all along; anyone who means Dutch says so once, in
       * workspace settings, next to the currency.
       */
      ALTER TABLE workspace ADD COLUMN locale TEXT NOT NULL DEFAULT 'en';
      ALTER TABLE account DROP COLUMN locale;
    `,
  },
];

export function openControlDatabase(file: string): ControlDb {
  const control: ControlDb = { sqlite: openSqlite({ file }) };
  migrateControl(control);
  return control;
}

export function migrateControl(control: ControlDb): void {
  control.sqlite.exec(`
    CREATE TABLE IF NOT EXISTS control_migration (
      version    INTEGER PRIMARY KEY,
      name       TEXT NOT NULL,
      applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  const applied = new Set(
    control.sqlite
      .prepare<[], { version: number }>('SELECT version FROM control_migration')
      .all()
      .map((r) => r.version),
  );

  const record = control.sqlite.prepare('INSERT INTO control_migration (version, name) VALUES (?, ?)');

  for (const m of MIGRATIONS) {
    if (applied.has(m.version)) continue;
    const run = control.sqlite.transaction(() => {
      control.sqlite.exec(m.up);
      record.run(m.version, m.name);
    });
    run();
  }
}

export function closeControlDatabase(control: ControlDb): void {
  control.sqlite.close();
}

/** Run `fn` inside a transaction on the control database. */
export function transactControl<T>(control: ControlDb, fn: () => T): T {
  const run = control.sqlite.transaction(fn);
  return run() as T;
}