Generate fresh-install DDL from schema.ts so indexes cannot drift

[Artmann]

What's wrong

schema.ts and tables.ts are two hand-maintained descriptions of the same schema, and they have already drifted:

• tables.ts:22 declares FOREIGN KEY (chatId) REFERENCES chats(id) ON DELETE CASCADE
• schema.ts:41-42 declares .references(() => chatsTable.id) with no onDelete

The guard test that is supposed to catch this compares column names only (reconcile-columns.test.ts:177-179). Types, NOT NULL, DEFAULT, foreign keys and indexes are all unchecked.

The live hole is indexes. There are four index(...) declarations in schema.ts:65-69,104-109 and four CREATE INDEX IF NOT EXISTS in tables.ts:52-60,88-96, and reconcileColumns handles columns only (reconcile-columns.ts:38-52). So an index added to schema.ts alone reaches no database and no test fails. This drift class already caused a shipped 500 — documented in the comment at reconcile-columns.ts:9-17.

Proposed change

Keep the createTables(database) signature, but generate its body from appTables via getTableConfig, sharing the column-fragment builder with addColumnStatement (reconcile-columns.ts:58-75), which already emits type / NOT NULL / DEFAULT.

One trap to watch: the ALTER path synthesizes a backfill default ('' / 0) for $defaultFn NOT NULL columns. The CREATE path must not inherit that, or fresh installs get databaseId TEXT NOT NULL DEFAULT ''.

Scope

The tables.ts body, a small exported helper in reconcile-columns.ts, plus tests. Zero call sites change.

Risks

Bounded to fresh installs receiving never-shipped DDL. Safety net: keep today's DDL as a test fixture and assert pragma_table_info + pragma_index_list equality between a fixture-built and a generator-built in-memory database.

Validation

Add the pragma_table_info / pragma_index_list equality test described above. Extend the guard test beyond column names.

Blocked on

Two decisions have to be made in order:

1. Do the dead-chats/messages deletion first — removing the only foreign key makes the generator strictly simpler.
2. This overlaps with the "delete the unreachable drizzle/ folder" issue. Deleting drizzle/ is unconditionally correct; whether the documentation should then point at a generator or at hand-written DDL depends on whether this issue is taken. Decide this one first.

---

Found in a codebase-wide simplification audit (F-S03-b). Confidence: medium — not a line-count win; the payoff is eliminating a drift class that has already shipped a bug.