A serial or identity column cannot be declared, so no auto-incrementing integer key is expressible — and it is the only construct still blocking a module in this schema

[jryannel]

BIGSERIAL / SERIAL cannot be declared, and neither can its modern replacement GENERATED … AS IDENTITY. introspect refuses both, honestly and with a good reason (#119/#124 made the serial case reported rather than silently wrong, which is how I found this cleanly) — but the result is that no auto-incrementing integer key can be declared at all.

What sqlb survey says about it

This is the whole point of the survey command, so here is what it found on the shared core/ schema of the multi-app adoption — 27 tables, 14 modules:

## Phase B — per-table isolation

| verdict | tables |
|---|---:|
| clean — imports with nothing dropped | 25 |
| partial — imports, constructs dropped | 2 |
| refused — registry error | 0 |

### Partial

- **activity_log** (8 cols)
    - `id`: column draws its default from a sequence (a serial), which the DSL cannot declare
      `bigint`
- **audit_log** (7 cols)
    - `id`: column draws its default from a sequence (a serial), which the DSL cannot declare
      `bigint`

### By module

| module | tables | clean | partial | refused | gate |
|---|---:|---:|---:|---:|---|
| activity | 1 | 0 | 1 | 0 | **blocked** |
| audit | 1 | 0 | 1 | 0 | **blocked** |
| … the other 12 | | | | | green |

**2 of 14 modules blocked.**

25 of 27 tables are clean. The only construct blocking anything in this entire schema is the serial, and it blocks exactly the two modules that use it. Every other gap I have reported against this codebase is now closed; this is what is left.

Why the workaround is not as cheap as it looks

The obvious substitution is UUIDv7, and for an append-only log it is genuinely defensible — time-ordered, no sequence contention. But it is not free, and the cost is not in the schema:

1. It is an API change, not a storage change. The reader interface is GetByID(ctx context.Context, scope tenancy.Scope, id int64) (Entry, error). Going to UUID changes int64 to string in a public core interface and in every caller — for two cross-cutting modules that several apps consume.
2. The id is load-bearing for ordering, not just identity. These tables order by occurred_at DESC, id DESC, where the id is the tiebreak that makes the order total — without it two rows written in the same millisecond can swap places between two pages of the same cursor walk. UUIDv7 preserves this (lexicographic order is chronological); a UUIDv4 would silently destroy it. That is a sharp edge for anyone taking the obvious workaround without noticing which of the two UUID constructors they reached for.
3. It fails the test the composite-key work established. The v0.8.0 notes put it well: a schema change forced by the declaration language must be defensible if sqlb vanished tomorrow. BIGSERIAL on an audit log is not a legacy accident — it is the smallest, densest monotonically increasing key Postgres offers, on the two tables in the schema that are pure append-only and are read in insertion order. Widening it to 16 bytes per row, on the two highest-volume tables in the system, to satisfy a declaration language, is the same shape of argument that PrimaryKeyColumns and SmallInt were added to retire.

Suggested shape

The same treatment the other five constructs got in v0.8.0:

schema.Serial("id").PrimaryKey()      // serial,    int32
schema.BigSerial("id").PrimaryKey()   // bigserial, int64

and/or the identity spelling, which is what Postgres itself now recommends over serial:

schema.BigInt("id").PrimaryKey().Identity()          // GENERATED BY DEFAULT AS IDENTITY
schema.BigInt("id").PrimaryKey().Identity(schema.Always)

The sequence being a separate object is the real difficulty — Diff has to render CREATE SEQUENCE / OWNED BY, or emit the serial shorthand and let Postgres create it. The identity form may be the cheaper one to implement for exactly that reason: it has no separate object to name, which is why Postgres introduced it.

Either would take this schema from 25/27 to 27/27 and from 2 blocked modules to 0.

Found by sqlb survey on the subject-mono of docs/review-adoption-multi-app.md. Related: #119, #124 (which made the serial case report correctly rather than import wrong), #109, #114 (the same "declarable so it can be gated" argument).

[jryannel]

Second data point, and it makes the case stronger than the original report did.

I have now run sqlb survey over all eleven applications in this platform as well as the shared core — 80 modules, 184 tables. The result:

	modules	tables	clean	partial	refused
core	14	27	27	0	0
11 apps	66	157	156	1	0
total	80	184	183	1	0

One table in the entire platform is not describable, and it is this issue. Every other construct across 184 tables imports clean.

### Partial

- **coprocess_steps** (9 cols)
    - `seq`: column draws its default from a sequence (a serial), which the DSL cannot declare
      `bigint`
    - `coprocess_steps_session_idx`: index covers seq, which was not imported, so the index
      cannot be declared either
      `CREATE INDEX coprocess_steps_session_idx ON public.coprocess_steps USING btree (session_id, seq)`

Two things this adds to the original report:

1. It is not always the primary key's identity — it is ordering, again. The migration says so itself:

-- Backs agentloop.StepStore. seq is the surrogate ordering key: the loop's
-- … can't be the uniqueness/ordering key — seq (a BIGSERIAL) is.
    seq               BIGSERIAL PRIMARY KEY,

That is the third table I have hit where a serial exists because something needs a total order, not because anything needs an integer name for a row. activity_log and audit_log were the other two, and all three took the same substitution for the same reason. A monotonic counter is what the construct is for in this codebase, which is worth knowing when weighing Serial() against .Identity() — either spelling solves it, and the ordering guarantee is what an adopter is actually preserving.

2. It cascades to indexes. coprocess_steps_session_idx is on (session_id, seq), and because seq is not imported the index cannot be declared either. So one undeclarable column takes out a table and an index that has nothing wrong with it. On a table whose access pattern is "this session's steps in order", that index is the table's reason for existing.

For what it is worth, the substitution I applied to activity and audit is available here too and I have not applied it — this module is not on the porting list yet, and with 183 of 184 tables clean it is now the single thing standing between this platform and a fully declarative schema.