benjivers/notes Public Public: anyone can read this; nobody can change it.

/**

 * The database: one SQLite file under FORGE_APP_DATA, so production and

 * every preview each have their own. WAL so readers never wait on the

 * writer (the terminal is a second process on the same file), foreign keys

 * on, and a forward-only list of migrations applied once, in a

 * transaction, when the file is opened.

 *

 *   bun run src/db.ts --check    the schema version and an integrity check

 */

import { Database } from "bun:sqlite";

import { mkdirSync } from "node:fs";

import { join } from "node:path";

import { dataDir } from "./env.ts";

/** Append, never edit: a migration that has run is history. */

export const migrations: string[] = [

  `CREATE TABLE note (

    id INTEGER PRIMARY KEY,

    body TEXT NOT NULL CHECK (length(body) BETWEEN 1 AND 500),

    author_id TEXT NOT NULL,

    author_name TEXT NOT NULL,

    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))

  )`,

  `CREATE INDEX note_author ON note (author_id)`,

  // Who tried to change what, and whether they were let. Appended by

  // src/audit.ts, never edited; read by admins at GET /api/audit.

  `CREATE TABLE audit (

    id INTEGER PRIMARY KEY,

    at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

    who_id TEXT,

    who_name TEXT,

    role TEXT NOT NULL,

    resource TEXT NOT NULL,

    verb TEXT NOT NULL,

    target TEXT,

    allowed INTEGER NOT NULL CHECK (allowed IN (0, 1)),

    outcome TEXT NOT NULL

  )`,

  // The vault: a note is a titled markdown page, its title unique in its

  // author's vault (any case), so a [[link]] names exactly one. The

  // scaffold's 500-character notes become the bodies of titled ones.

  `CREATE TABLE page (

    id INTEGER PRIMARY KEY,

    title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200),

    body TEXT NOT NULL DEFAULT '' CHECK (length(body) <= 200000),

    pinned INTEGER NOT NULL DEFAULT 0 CHECK (pinned IN (0, 1)),

    author_id TEXT NOT NULL,

    author_name TEXT NOT NULL,

    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))

  )`,

  `INSERT INTO page (id, title, body, author_id, author_name, created_at, updated_at)

    SELECT id, 'Note ' || id, body, author_id, author_name, created_at, updated_at FROM note`,

  `DROP TABLE note`,

  `ALTER TABLE page RENAME TO note`,

  `CREATE UNIQUE INDEX note_title ON note (author_id, title COLLATE NOCASE)`,

  `CREATE INDEX note_recent ON note (author_id, updated_at)`,

  // The app's own accounts, for people without a forge account (src/accounts.ts).

  // A session keeps only its token's hash.

  `CREATE TABLE account (

    id INTEGER PRIMARY KEY,

    email TEXT NOT NULL UNIQUE CHECK (length(email) BETWEEN 3 AND 320),

    name TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 60),

    password_hash TEXT NOT NULL,

    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))

  )`,

  `CREATE TABLE session (

    token_hash TEXT PRIMARY KEY,

    account_id INTEGER NOT NULL REFERENCES account (id) ON DELETE CASCADE,

    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

    expires_at TEXT NOT NULL

  )`,

  `CREATE INDEX session_account ON session (account_id)`,

  // Shared folders (src/spaces.ts): a folder of a git repository that its

  // members write in together. Its notes are ordinary notes whose vault is

  // the space — author_id `space:<id>` — so every vault query holds as it

  // is; `path` is the note's file in the repository, and the synced_* pair

  // is what the repository last had (src/sync.ts), so a difference is an

  // edit not committed yet.

  `CREATE TABLE space (

    id INTEGER PRIMARY KEY,

    name TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 80),

    repo TEXT NOT NULL,

    branch TEXT NOT NULL,

    folder TEXT NOT NULL,

    owner_id TEXT NOT NULL,

    owner_name TEXT NOT NULL,

    join_code TEXT UNIQUE,

    head TEXT,

    synced_at TEXT,

    sync_error TEXT,

    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))

  )`,

  `CREATE TABLE space_member (

    space_id INTEGER NOT NULL REFERENCES space (id) ON DELETE CASCADE,

    who_id TEXT NOT NULL,

    who_name TEXT NOT NULL,

    joined_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

    PRIMARY KEY (space_id, who_id)

  )`,

  `CREATE INDEX space_member_who ON space_member (who_id)`,

  `ALTER TABLE note ADD COLUMN path TEXT`,

  `ALTER TABLE note ADD COLUMN synced_path TEXT`,

  `ALTER TABLE note ADD COLUMN synced_oid TEXT`,

  `CREATE UNIQUE INDEX note_path ON note (author_id, path) WHERE path IS NOT NULL`,

  // A shared note removed here whose file the repository still has: the

  // next commit deletes it.

  `CREATE TABLE space_gone (

    space_id INTEGER NOT NULL REFERENCES space (id) ON DELETE CASCADE,

    path TEXT NOT NULL,

    PRIMARY KEY (space_id, path)

  )`,

];

export function openDb(dir = dataDir): Database {

  mkdirSync(dir, { recursive: true });

  const db = new Database(join(dir, "app.db"));

  db.exec("PRAGMA journal_mode = WAL");

  db.exec("PRAGMA foreign_keys = ON");

  db.exec("PRAGMA busy_timeout = 5000");

  migrate(db);

  return db;

}

/** Apply the migrations past the recorded version, all or none. Returns the version. */

export function migrate(db: Database): number {

  db.exec(

    "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')))",

  );

  return db.transaction(() => {

    const current = db

      .query<

        { version: number },

        []

      >("SELECT coalesce(max(version), 0) AS version FROM schema_version")

      .get()!.version;

    migrations.slice(current).forEach((sql, i) => {

      db.exec(sql);

      db.query("INSERT INTO schema_version (version) VALUES (?1)").run(

        current + i + 1,

      );

    });

    return migrations.length;

  })();

}

export function check(db: Database): { version: number; integrity: string } {

  const version = db

    .query<

      { version: number },

      []

    >("SELECT max(version) AS version FROM schema_version")

    .get()!.version;

  const integrity = db

    .query<{ integrity_check: string }, []>("PRAGMA integrity_check")

    .get()!.integrity_check;

  return { version, integrity };

}

if (import.meta.main) {

  const db = openDb();

  const { version, integrity } = check(db);

  console.log(`schema version ${version}`);

  if (process.argv.includes("--check")) {

    console.log(`integrity ${integrity}`);

    process.exit(integrity === "ok" ? 0 : 1);

  }

}