feat: make automatic table creation an explicit option and build tables from their model #31

Open
opened 2026-11-14 08:11:04 +00:00 by claude-bot · 0 comments
Member

Context

When a record is written or read, the SQL backend adapters create missing tables on the fly (_ensureTable in @quatrain/backend-postgres, its equivalent in @quatrain/backend-sqlite). They also create the tables that object properties refer to, so that joins do not fail.

This behaviour conflicts with several rule units of the AGENTS.okf knowledge base. The rule names below refer to its content/ tree.

Problems, by rule

Fail-Fast Contracts & Configuration (architecture/fail-fast-contracts.md)

  • Silent fallback. When the class a property refers to is not registered in Core, the adapter creates a substitute table holding only id and name. Any later use of the real model fails with column "…" does not exist. The failure is order-dependent: it broke CI on an empty database when a test whose model only referred to users ran before the tests using the User model.
  • No explicit configuration. Automatic creation always runs; it cannot be declared on or off. With a restricted database role (no CREATE right, as row isolation requires) it can only fail late, at the first query.

PostgreSQL DDL Migrations & Lowercase Naming Standards (backend-workers/postgresql-ddl-and-naming.md)

  • Schema outside version control. The rule gives every domain concept a dedicated, version-controlled, idempotent DDL .sql file. Tables created at run time bypass it.
  • Quoted identifiers. Generated statements quote table and column names (CREATE TABLE "user" ("name" TEXT)); the rule forbids double quotes and requires strictly lowercase names. Note: the user collection is a reserved word and only works quoted.
  • Timestamps without time zone. Date-time properties become TIMESTAMP; the rule requires TIMESTAMP WITH TIME ZONE.
  • Column types. Every property type should map to the recommended column type; today strings, maps and enumerations all become TEXT, identifiers VARCHAR(255).

Dependency Injection & DRY (architecture/dependency-injection-and-dry.md)

  • The property-type to column-type mapping is written three times in backend-postgres (_ensureTableByName, generateCreateSql, generateDeltaSql) and again in backend-sqlite.

Structured Logging (architecture/structured-logging.md)

  • The creation path reports errors with raw console.log(err) / console.error(err).

Expected behaviour

  1. A global configuration flag enables or disables automatic table creation, for every SQL adapter. It is validated in the constructor of AbstractBackendAdapter, as the fail-fast rule requires for configuration of abstract classes.
  2. Disabled: a missing table raises an explicit error naming the table and pointing to migrations. Nothing is created.
  3. Enabled: a created table respects its model — every property, the recommended column type for each property type, UNIQUE for unique properties, lowercase unquoted identifiers, time-zone-aware timestamps.
  4. Every dynamic creation leaves a replayable migration file. When the adapter creates a table at run time, it also writes the matching SQL migration file: one .sql file per table, idempotent (CREATE TABLE IF NOT EXISTS, constraints and indexes guarded the same way), so it can be committed and replayed on any environment to obtain the same schema. A table never exists in a database without its DDL existing as a file. This reuses the migration generation of @quatrain/backend-migrations rather than a second mechanism.
  5. Never a placeholder: if the model of a referred table cannot be resolved, fail with an error naming the unresolved class.
  6. One mapping from property type to column type per adapter, shared by automatic creation and by migration generation, so both always produce the same schema.
  7. Errors go through Backend.error with a structured payload (collection, table, cause).

Decisions to take

  • Where dynamic-creation migration files are written (configured directory, required when the flag is enabled; fail fast if it is missing or not writable) and how they are named and ordered.

  • What happens when a migration file already exists for the table but differs from the model.

  • Default value of the flag. Today's behaviour is "enabled"; the DDL rule argues for "disabled" outside development and tests.

  • The user collection name, incompatible with unquoted identifiers: rename (breaking) or documented exception.

  • Whether changing column types of generated tables needs a migration path for existing databases.

Acceptance

  • Flag documented in the README of each SQL adapter; both positions covered by tests (TDD & Test Co-Location: unit tests beside the code, database tests in the integration suite).
  • On an empty database the test suites pass in any order, without registering classes as a workaround.
  • A reference to an unregistered class fails fast with an actionable message.
  • tsc compiles with zero errors; no as any added (Strict Typing, Interfaces & Zero-Any Policy).
  • Creating a table dynamically produces its .sql migration file; replaying that file on an empty database yields the same table, and replaying it twice changes nothing.
  • Generated DDL contains no double-quoted identifier and no TIMESTAMP without time zone.
## Context When a record is written or read, the SQL backend adapters create missing tables on the fly (`_ensureTable` in `@quatrain/backend-postgres`, its equivalent in `@quatrain/backend-sqlite`). They also create the tables that object properties refer to, so that joins do not fail. This behaviour conflicts with several rule units of the AGENTS.okf knowledge base. The rule names below refer to its `content/` tree. ## Problems, by rule ### Fail-Fast Contracts & Configuration (`architecture/fail-fast-contracts.md`) - **Silent fallback.** When the class a property refers to is not registered in `Core`, the adapter creates a substitute table holding only `id` and `name`. Any later use of the real model fails with `column "…" does not exist`. The failure is order-dependent: it broke CI on an empty database when a test whose model only *referred* to users ran before the tests using the `User` model. - **No explicit configuration.** Automatic creation always runs; it cannot be declared on or off. With a restricted database role (no `CREATE` right, as row isolation requires) it can only fail late, at the first query. ### PostgreSQL DDL Migrations & Lowercase Naming Standards (`backend-workers/postgresql-ddl-and-naming.md`) - **Schema outside version control.** The rule gives every domain concept a dedicated, version-controlled, idempotent DDL `.sql` file. Tables created at run time bypass it. - **Quoted identifiers.** Generated statements quote table and column names (`CREATE TABLE "user" ("name" TEXT)`); the rule forbids double quotes and requires strictly lowercase names. Note: the `user` collection is a reserved word and only works quoted. - **Timestamps without time zone.** Date-time properties become `TIMESTAMP`; the rule requires `TIMESTAMP WITH TIME ZONE`. - **Column types.** Every property type should map to the recommended column type; today strings, maps and enumerations all become `TEXT`, identifiers `VARCHAR(255)`. ### Dependency Injection & DRY (`architecture/dependency-injection-and-dry.md`) - The property-type to column-type mapping is written three times in `backend-postgres` (`_ensureTableByName`, `generateCreateSql`, `generateDeltaSql`) and again in `backend-sqlite`. ### Structured Logging (`architecture/structured-logging.md`) - The creation path reports errors with raw `console.log(err)` / `console.error(err)`. ## Expected behaviour 1. **A global configuration flag** enables or disables automatic table creation, for every SQL adapter. It is validated in the constructor of `AbstractBackendAdapter`, as the fail-fast rule requires for configuration of abstract classes. 2. **Disabled:** a missing table raises an explicit error naming the table and pointing to migrations. Nothing is created. 3. **Enabled:** a created table respects its model — every property, the recommended column type for each property type, `UNIQUE` for unique properties, lowercase unquoted identifiers, time-zone-aware timestamps. 4. **Every dynamic creation leaves a replayable migration file.** When the adapter creates a table at run time, it also writes the matching SQL migration file: one `.sql` file per table, idempotent (`CREATE TABLE IF NOT EXISTS`, constraints and indexes guarded the same way), so it can be committed and replayed on any environment to obtain the same schema. A table never exists in a database without its DDL existing as a file. This reuses the migration generation of `@quatrain/backend-migrations` rather than a second mechanism. 5. **Never a placeholder:** if the model of a referred table cannot be resolved, fail with an error naming the unresolved class. 6. **One mapping** from property type to column type per adapter, shared by automatic creation and by migration generation, so both always produce the same schema. 7. Errors go through `Backend.error` with a structured payload (collection, table, cause). ## Decisions to take - Where dynamic-creation migration files are written (configured directory, required when the flag is enabled; fail fast if it is missing or not writable) and how they are named and ordered. - What happens when a migration file already exists for the table but differs from the model. - Default value of the flag. Today's behaviour is "enabled"; the DDL rule argues for "disabled" outside development and tests. - The `user` collection name, incompatible with unquoted identifiers: rename (breaking) or documented exception. - Whether changing column types of generated tables needs a migration path for existing databases. ## Acceptance - Flag documented in the README of each SQL adapter; both positions covered by tests (TDD & Test Co-Location: unit tests beside the code, database tests in the integration suite). - On an empty database the test suites pass in any order, without registering classes as a workaround. - A reference to an unregistered class fails fast with an actionable message. - `tsc` compiles with zero errors; no `as any` added (Strict Typing, Interfaces & Zero-Any Policy). - Creating a table dynamically produces its `.sql` migration file; replaying that file on an empty database yields the same table, and replaying it twice changes nothing. - Generated DDL contains no double-quoted identifier and no `TIMESTAMP` without time zone.
claude-bot changed title from feat(backend): make automatic table creation a global option and build related tables from their model to feat: make automatic table creation an explicit option and build tables from their model 2026-11-14 08:12:38 +00:00
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
quatrain/core#31
No description provided.