Helpthread · zaridan · Jul 10, 2026 · Jul 10, 2026 · Jul 10, 2026
@@ -18,6 +18,7 @@
     "test:coverage": "vitest run --coverage"
   },
   "dependencies": {
+    "@electric-sql/pglite": "^0.5.4",
     "postal-mime": "^2.7.5"
   },
   "devDependencies": {

@@ -46,8 +46,8 @@ Rationale: charter §2's "boringly faithful on mail semantics" principle draws t
 ## 5. Edge cases & open questions
 
 - **Multiple valid tokens across `References` pointing at DIFFERENT conversations.** Not observed. Per rule 1's most-recent-first scan, the first valid token wins by construction. **OPEN QUESTION:** confirm most-recent-wins is intended — plausible since it reflects what the customer is immediately replying to, but needs its own acceptance fixture before it's load-bearing.
-- **A valid token to a CLOSED/archived conversation.** Not observed. **OPEN QUESTION:** reopen, or start a new conversation referencing it? Reopen matches the charter's Help Scout-like ease-of-use bar but is undecided.
-- **A valid token to a deleted conversation.** Not observed. Token verifies but its target is gone — must not crash or silently drop mail (invariant #1). **OPEN QUESTION:** likely "create a new conversation, log the orphaned-token event," undecided.
+- **A valid token to a CLOSED/archived conversation.** **RESOLVED** at the store layer (`src/store/conversations.ts`, `ConversationStore.appendThread`): the thread is inserted and the conversation reopens (`status` back to `'open'`). Reopen was the leaning here and is now the implemented behavior, matching the charter's Help Scout-like ease-of-use bar (CHARTER.md §1) — a reply to a resolved ticket lands back in the same conversation rather than forking a duplicate.
+- **A valid token to a deleted conversation.** **RESOLVED** at the store layer: nothing is inserted; `appendThread` returns a `deleted` result and the caller (the mail-ingestion pipeline) is expected to fall back to starting a fresh conversation, so the message is never silently dropped (invariant #1) but also never resurrects a conversation an operator intentionally removed. A missing conversation id (token verifies, but no such conversation row exists at all) is handled the same shape, with a distinct `not-found` result.
 - **Forged-token rate-limiting/alerting.** forged-reply-token.json proves detection works; it says nothing about response. A single forgery is unremarkable; a burst against one conversation or sender is a security signal. **OPEN QUESTION:** threshold/alerting mechanism unspecified — security follow-up, not blocking v1 correctness.
 - **`keyId` rotation.** See §2(d).
 - **Auto-Submitted mail creates conversations.** auto-submitted.json: a message with `Auto-Submitted: auto-replied` was ingested normally, creating conversation 18 — not suppressed. In scope here only insofar as such mail runs through the algorithm above; whether Helpthread should suppress or specially route it (to avoid reply loops when its own auto-response gets auto-answered) is cross-referenced to a future auto-responder spec.

@@ -0,0 +1,63 @@
+# Conversation Store Spec
+
+Status: draft, implemented (HT-14). Governs how conversations and threads are
+persisted once a threading decision (specs/mail/threading.md) has been made.
+
+## 1. Scope
+
+A conversation has many threads; a thread is exactly one message (inbound
+customer mail, or outbound agent/assistant mail). This spec covers the store
+layer only — `src/store/conversations.ts`, `ConversationStore`. It does not
+decide which conversation a message belongs to; that decision is
+`decideThreading` (`src/mail/thread.ts`), a pure function with no I/O. This
+layer persists what it's handed and owns exactly one further decision: what
+happens when the target conversation isn't in a state that can simply accept
+a new thread (§3).
+
+## 2. Operations
+
+- **`createConversation(input)`** — creates a conversation and its first
+  thread in one transaction. Atomic: a failure inserting the first thread
+  (e.g. a constraint violation) leaves zero conversation rows, never a
+  conversation with no threads.
+- **`appendThread(conversationId, thread)`** — adds a thread to an existing
+  conversation, applying the status policy in §3. Also bumps the
+  conversation's `updated_at` on any successful insert.
+- **`getConversation(conversationId)`** — reads one conversation with its
+  threads ordered oldest-first (`created_at, id`, the `id` tiebreak keeping
+  order stable for threads inserted within the same timestamp tick). `null`
+  if the conversation doesn't exist.
+
+## 3. Status policy on append
+
+`appendThread` resolves specs/mail/threading.md §5's open question on
+replying to a closed/deleted/missing conversation:
+
+| conversation status | effect |
+|---|---|
+| `open` | thread inserted |
+| `closed` | thread inserted, conversation reopened (`status` → `open`) |
+| `deleted` | nothing inserted; `{ ok: false, reason: 'deleted' }` |
+| missing (no such id) | nothing inserted; `{ ok: false, reason: 'not-found' }` |
+
+Reopen-on-reply matches the charter's Help Scout-like ease-of-use bar
+(CHARTER.md §1): a customer reply to a resolved ticket should land back in
+the same conversation, not silently vanish or fork a duplicate. A deleted or
+missing target is different — there is no live conversation to reopen — so
+the caller (the mail-ingestion pipeline) is expected to fall back to
+starting a fresh conversation rather than resurrecting one an operator
+removed, or one that never existed. Either way, mail is never silently
+dropped (CHARTER.md invariant #1): the result is always a typed outcome the
+caller must handle, never a thrown exception or a swallowed failure.
+
+The whole read-check-write is one transaction, with the conversation row
+locked (`SELECT ... FOR UPDATE`) for its duration, so two concurrent replies
+to the same closed conversation both observe and reopen deterministically
+rather than racing into an inconsistent status.
+
+## 4. Portability
+
+Built entirely on the raw-SQL seam in `src/db/client.ts` (`Db`/`Queryable`):
+parameterized `$1`-style SQL only, no ORM. The same SQL runs against PGlite
+locally/in tests and against Supabase's hosted Postgres in production — see
+that module's doc for why.
@@ -0,0 +1,82 @@
+import { mkdtemp, rm } from 'node:fs/promises'
+import { tmpdir } from 'node:os'
+import path from 'node:path'
+import { afterEach, describe, expect, it } from 'vitest'
+import { createPgliteDb, type Db } from './client.js'
+
+describe('createPgliteDb', () => {
+  let db: Db | undefined
+
+  afterEach(async () => {
+    await db?.close()
+    db = undefined
+  })
+
+  it('creates an in-memory database that can run parameterized queries', async () => {
+    db = await createPgliteDb()
+    const rows = await db.query<{ x: number }>('SELECT $1::int AS x', [42])
+    expect(rows).toEqual([{ x: 42 }])
+  })
+
+  it('with a dataDir, persists data to disk across separate Db instances', async () => {
+    const dataDir = await mkdtemp(path.join(tmpdir(), 'helpthread-pglite-'))
+    try {
+      db = await createPgliteDb({ dataDir })
+      await db.query('CREATE TABLE t (id serial PRIMARY KEY, name text)')
+      await db.query('INSERT INTO t (name) VALUES ($1)', ['persisted'])
+      await db.close()
+      db = undefined
+
+      // A second, independent Db instance pointed at the same dataDir sees
+      // the first instance's writes — proves this is real on-disk
+      // persistence, not just an in-memory handle that happens to reuse
+      // state.
+      db = await createPgliteDb({ dataDir })
+      const rows = await db.query<{ name: string }>('SELECT name FROM t')
+      expect(rows).toEqual([{ name: 'persisted' }])
+    } finally {
+      // Close the open handle before removing its directory — on platforms
+      // that refuse to unlink open files, an rm with the DB still open would
+      // fail teardown.
+      await db?.close()
+      db = undefined
+      await rm(dataDir, { recursive: true, force: true })
+    }
+  })
+
+  it("transaction commits and returns fn's result when fn resolves", async () => {
+    db = await createPgliteDb()
+    await db.query('CREATE TABLE t (id serial PRIMARY KEY, name text)')
+
+    const result = await db.transaction(async (tx) => {
+      await tx.query('INSERT INTO t (name) VALUES ($1)', ['a'])
+      return 'done'
+    })
+
+    expect(result).toBe('done')
+    const rows = await db.query<{ name: string }>('SELECT name FROM t')
+    expect(rows).toEqual([{ name: 'a' }])
+  })
+
+  it('transaction rolls back every write when fn throws', async () => {
+    db = await createPgliteDb()
+    await db.query('CREATE TABLE t (id serial PRIMARY KEY, name text)')
+
+    await expect(
+      db.transaction(async (tx) => {
+        await tx.query('INSERT INTO t (name) VALUES ($1)', ['should-not-survive'])
+        throw new Error('boom')
+      }),
+    ).rejects.toThrow('boom')
+
+    const rows = await db.query<{ name: string }>('SELECT name FROM t')
+    expect(rows).toEqual([])
+  })
+
+  it('close() releases the underlying engine — a query after close rejects', async () => {
+    db = await createPgliteDb()
+    await db.close()
+    await expect(db.query('SELECT 1')).rejects.toThrow()
+    db = undefined
+  })
+})
@@ -0,0 +1,159 @@
+/**
+ * The portable raw-SQL seam — `Db`/`Queryable` — that every store module
+ * (`src/store/**`) is built on.
+ *
+ * Per CHARTER.md §4 ("the engine's core never calls a platform directly"),
+ * storage sits behind a thin interface the project owns. This one is
+ * deliberately THIN: raw parameterized SQL in, plain rows out — no ORM, no
+ * query builder, no schema-mapping magic. The reason is portability, not
+ * minimalism for its own sake: the exact same SQL strings run unmodified
+ * against **PGlite** (`PgliteDb`, this file) locally and in tests, and
+ * against **Supabase's hosted Postgres** in production (a future `Db`
+ * implementation talking to it directly over `pg` or Supabase's REST/edge
+ * connection — not written yet, but the seam is shaped for it today). A
+ * query builder or ORM would tie the codebase to one library's SQL dialect
+ * quirks; writing Postgres SQL directly and keeping the abstraction to
+ * "run this SQL, get these rows" avoids that entirely.
+ *
+ * ## Why PGlite
+ *
+ * PGlite (`@electric-sql/pglite`, dual-licensed Apache-2.0 or the PostgreSQL
+ * License — either may be used) is a WASM build of real
+ * Postgres packaged as an in-process Node/browser library — not a mock, not
+ * SQLite-with-a-Postgres-flavored-dialect. Tests and local dev run against
+ * the genuine Postgres engine (see the version note on {@link createPgliteDb}),
+ * so SQL that passes locally is SQL that behaves the same way against
+ * Supabase, not SQL that merely *resembles* it.
+ *
+ * ## Parameterization is not optional
+ *
+ * Every query in this codebase MUST use `$1, $2, ...` positional
+ * placeholders (Postgres/Supabase-portable — the same placeholder syntax
+ * both backends speak) and pass values via `params`. Values are never
+ * string-interpolated into SQL text — see `src/store/conversations.test.ts`
+ * for an injection-safety test that proves this holds for user-controlled
+ * fields like `customerEmail`.
+ */
+
+import { PGlite } from '@electric-sql/pglite'
+
+/**
+ * A value safe to bind as a query parameter. Deliberately narrow — the set
+ * of JS types `pg`-wire-protocol drivers (and PGlite, which speaks the same
+ * protocol) know how to serialize without ambiguity. Anything richer (a
+ * plain object meant as `jsonb`, for instance) should be `JSON.stringify`'d
+ * by the caller before it reaches `query`, so this seam never has to guess
+ * a caller's serialization intent.
+ */
+export type SqlValue = string | number | boolean | null | Date | Uint8Array
+
+/** One result row: column name to value, shape unknown until the caller narrows it. */
+export type Row = Record<string, unknown>
+
+/**
+ * The minimal query surface — what both a top-level `Db` and an in-flight
+ * transaction expose. Kept separate from `Db` so that `transaction`'s
+ * callback can be typed to accept exactly this (a transaction handle is
+ * queryable but is not itself something you can open a nested transaction
+ * on or close).
+ */
+export interface Queryable {
+  /**
+   * Run one parameterized SQL statement and return its result rows.
+   * `params[i]` binds to `$${i + 1}` in `sql`. Never interpolate untrusted
+   * values into `sql` itself — always bind them through `params`.
+   */
+  query<T = Row>(sql: string, params?: SqlValue[]): Promise<T[]>
+}
+
+/**
+ * A top-level database handle: `Queryable` plus transaction control and
+ * lifecycle management. This is the interface store modules depend on —
+ * never `PgliteDb` or `PGlite` directly — so a future Supabase-backed `Db`
+ * implementation is a drop-in replacement.
+ */
+export interface Db extends Queryable {
+  /**
+   * Run `fn` inside a single database transaction. If `fn` throws (or its
+   * returned promise rejects), the transaction is rolled back and the
+   * error propagates — no partial writes survive. If `fn` resolves, the
+   * transaction commits and its return value is passed through.
+   */
+  transaction<T>(fn: (tx: Queryable) => Promise<T>): Promise<T>
+
+  /** Release the underlying connection/engine. Safe to call once, at shutdown. */
+  close(): Promise<void>
+}
+
+/**
+ * `Db` implementation backed by an in-process PGlite instance — real
+ * Postgres compiled to WASM, not a mock or a SQLite stand-in. See the
+ * module doc for why this is the right local/test backend for SQL that must
+ * also run unmodified against Supabase.
+ */
+export class PgliteDb implements Db {
+  readonly #pglite: PGlite
+
+  constructor(pglite: PGlite) {
+    this.#pglite = pglite
+  }
+
+  async query<T = Row>(sql: string, params: SqlValue[] = []): Promise<T[]> {
+    const result = await this.#pglite.query<T>(sql, params)
+    return result.rows
+  }
+
+  async transaction<T>(fn: (tx: Queryable) => Promise<T>): Promise<T> {
+    return this.#pglite.transaction(async (tx) => {
+      const queryable: Queryable = {
+        query: async <U = Row>(sql: string, params: SqlValue[] = []) => {
+          const result = await tx.query<U>(sql, params)
+          return result.rows
+        },
+      }
+      return fn(queryable)
+    })
+  }
+
+  async close(): Promise<void> {
+    await this.#pglite.close()
+  }
+}
+
+/**
+ * Create a `Db` backed by PGlite: in-memory when `options.dataDir` is
+ * omitted (the right choice for tests — fast, fully isolated, nothing to
+ * clean up), file-backed via PGlite's Node filesystem adapter when given a
+ * `dataDir` path (for local development, where data should survive a
+ * restart).
+ *
+ * Deliberately does NOT run migrations — callers call {@link migrate}
+ * (`src/db/migrate.ts`) explicitly. Keeping schema setup out of this
+ * factory means a caller can open a `Db` against an already-migrated
+ * database (e.g. a long-lived local dev file) without re-running migration
+ * logic on every connect, and keeps "connect" and "ensure schema" as two
+ * separately testable steps.
+ *
+ * Verified against the installed PGlite 0.5.4 (bundling PostgreSQL 18):
+ * `PGlite.create()` with no `dataDir` argument is in-memory, and
+ * `PGlite.create(dataDir)` persists to that directory via PGlite's Node
+ * filesystem backend — no `memory://`/`idb://` URL prefix needed on either
+ * path in Node (those prefixes are for selecting a filesystem backend in a
+ * browser, where Node's plain directory semantics don't apply).
+ *
+ * The in-memory-vs-file choice branches on whether `dataDir` was *provided*
+ * (`!== undefined`), not on its truthiness — an explicitly passed empty
+ * string is a misconfiguration (a persistence path was intended but is
+ * blank), so it is rejected loudly rather than silently degrading to an
+ * ephemeral in-memory database that would drop data on restart.
+ */
+export async function createPgliteDb(options?: { dataDir?: string }): Promise<Db> {
+  const dataDir = options?.dataDir
+  if (dataDir !== undefined && dataDir.trim() === '') {
+    throw new Error(
+      'createPgliteDb: `dataDir` was provided but empty — pass a real directory path for a file-backed database, or omit `dataDir` entirely for an in-memory one.',
+    )
+  }
+  const pglite = dataDir !== undefined ? await PGlite.create(dataDir) : await PGlite.create()
+  return new PgliteDb(pglite)
+}
@@ -0,0 +1,13 @@
+/**
+ * Barrel for the DB layer (`src/db/**`). Store modules (`src/store/**`) and
+ * anything wiring up a database connection import from here — never reach
+ * into `client.ts`/`migrate.ts` directly, and never import `@electric-sql/pglite`
+ * outside this directory (see `src/db/client.ts` for why the raw-SQL seam
+ * exists: the same SQL must run unmodified against a future Supabase-backed
+ * `Db`, so nothing above this barrel should know PGlite exists).
+ */
+
+export type { Db, Queryable, Row, SqlValue } from './client.js'
+export { createPgliteDb, PgliteDb } from './client.js'
+export type { Migration } from './migrate.js'
+export { migrate } from './migrate.js'