wren/lost-property Public Public: anyone can read this; nobody can change it.

/**

 * The collection: items that were lost and the notes visitors leave on

 * them. One SQLite file under FORGE_APP_DATA; production and each preview

 * get their own, so a preview starts from production's data and never

 * writes back to it.

 */

import { Database } from "bun:sqlite";

import { mkdirSync } from "node:fs";

import { join } from "node:path";

const dataDir = process.env.FORGE_APP_DATA ?? "./data";

mkdirSync(dataDir, { recursive: true });

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

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

// An earlier version of this app kept a flat note table with no item to

// belong to. `CREATE TABLE IF NOT EXISTS` below would leave that shape in

// place forever, so a table missing item_id is moved aside once, keeping

// its rows rather than losing them.

const existingNoteColumns = db.query<{ name: string }, []>("PRAGMA table_info(note)").all();

if (existingNoteColumns.length > 0 && !existingNoteColumns.some((c) => c.name === "item_id")) {

  db.exec("ALTER TABLE note RENAME TO note_legacy");

}

db.exec(

  `CREATE TABLE IF NOT EXISTS item (

    id INTEGER PRIMARY KEY,

    description TEXT NOT NULL,

    created_at TEXT NOT NULL DEFAULT (datetime('now'))

  )`,

);

db.exec(

  `CREATE TABLE IF NOT EXISTS note (

    id INTEGER PRIMARY KEY,

    item_id INTEGER NOT NULL REFERENCES item(id),

    kind TEXT NOT NULL CHECK (kind IN ('sighting', 'match')),

    body TEXT NOT NULL,

    created_at TEXT NOT NULL DEFAULT (datetime('now'))

  )`,

);

export type Item = { id: number; description: string; created_at: string };

export type ItemWithCount = Item & { note_count: number };

export type NoteKind = "sighting" | "match";

export type Note = { id: number; item_id: number; kind: NoteKind; body: string; created_at: string };

export function createItem(description: string): number {

  const row = db

    .query<{ id: number }, [string]>("INSERT INTO item (description) VALUES (?1) RETURNING id")

    .get(description);

  return row!.id;

}

export function getItem(id: number): Item | undefined {

  return db.query<Item, [number]>("SELECT id, description, created_at FROM item WHERE id = ?1").get(id) ?? undefined;

}

export function listItems(): ItemWithCount[] {

  return db

    .query<

      ItemWithCount,

      []

    >(`SELECT item.id, item.description, item.created_at,

              (SELECT count(*) FROM note WHERE note.item_id = item.id) AS note_count

       FROM item ORDER BY item.id DESC LIMIT 100`)

    .all();

}

export function listNotes(itemId: number): Note[] {

  return db

    .query<Note, [number]>("SELECT id, item_id, kind, body, created_at FROM note WHERE item_id = ?1 ORDER BY id ASC")

    .all(itemId);

}

export function addNote(itemId: number, kind: NoteKind, body: string): void {

  db.query("INSERT INTO note (item_id, kind, body) VALUES (?1, ?2, ?3)").run(itemId, kind, body);

}