Schema conventions that let AI agents write correct code

We use AI coding agents extensively at smplkit — not as a novelty, but as a core part of the development workflow. Claude Code writes implementation code, runs tests, monitors CI, and remediates failures. This is fast when it works, and when it doesn’t, the failure is usually the same one: the agent made a schema assumption that’s wrong.

A column named userId instead of user_id. A table named accounts instead of account. A VARCHAR(255) where we use TEXT. A missing JSONB data column. Each mistake costs a correction cycle — the agent writes code, the test fails, the agent reads the error, tries again. Multiply that across dozens of tables and hundreds of queries, and inconsistency turns into a tax on every working hour.

So we wrote down the rules. All of them, in enough detail that an agent — or a new human — produces correct schema code on the first try.

Naming

Table names are singular. account, not accounts. A table holds rows and each row is one entity; account_user for the join table. This is the single rule agents most often guess wrong, because half the tutorials on the internet pluralize.

Column names are snake_case. created_at, account_id, stripe_customer_id. No camelCase — that’s the userId mistake from the opening paragraph — and no abbreviations beyond the universal ones.

Constrained values are SCREAMING_SNAKE_CASE. ACTIVE, OWNER, API_KEY, MONTHLY — uppercase to visually separate them from freeform text. Enforced in application code rather than as database enums, which means adding a value never requires a migration.

Primary keys

Every table — join tables included — has an id UUID primary key, generated by the application (uuid4()) with a DEFAULT gen_random_uuid() fallback for raw SQL.

UUIDs over auto-increment because they’re globally unique without coordination, safe to generate before the database round-trip, and don’t leak table size or insertion order. And a surrogate id on join tables, even though a composite key on (account_id, user_id) would theoretically do, because single-column PKs keep SQLAlchemy natural, URLs single-valued, and audit-log references simple. An agent that has to decide “composite or surrogate?” per table will decide differently on Tuesdays.

String columns

All strings are TEXT, never VARCHAR(n). PostgreSQL stores them identically — the length is just a check constraint — and length validation belongs in the application layer (Pydantic), where it produces readable error messages and changing a limit doesn’t require a migration.

Standard columns

Every non-join table gets the same seven: id, account_id (tenant scoping), created_at, updated_at (SQLAlchemy onupdate), deleted_at (nullable, soft delete), version (nullable, optimistic concurrency), and data (JSONB, defaults to '{}').

deleted_at and version are there even on tables that don’t use soft deletes or concurrency control yet. They’re NULL, they cost nothing, and their absence later means a migration at exactly the moment you don’t want one. The data column is the document-style escape hatch: experimental fields live there until they prove important enough to earn real columns.

For an agent, the bookkeeping columns stop being a judgment call — copy the seven.

Foreign keys

Real FK constraints inside a service boundary; the database enforces integrity wherever both tables share a database. Logical foreign keys across services: the account_id column exists and is always populated, but no constraint crosses a service boundary — the provisioning and introspection contracts enforce that integrity instead. And every FK column gets an explicit index, because PostgreSQL doesn’t auto-index foreign keys and an agent (or a human) who assumes it does will ship a table scan.

Why this matters more for agents than for humans

A developer writing a new model makes dozens of micro-decisions: name format, column types, which standard columns, enum-or-text. Humans absorb the answers by osmosis, reading neighboring code. Agents can do that too — but every inferred convention is a probability, and probabilities miss. Explicit rules turn each guess into a lookup. “Should this be VARCHAR(100) or TEXT?” is not a question when the convention says TEXT, always.

We’ve found that explicit, comprehensive conventions — the kind that feel unnecessarily detailed when you write them — are exactly what agents need. Humans can infer conventions from examples. Agents perform better with explicit rules, and honestly, so do the humans; they’re just more polite about the ambiguity.

The SQLAlchemy layer

The DeclarativeBase subclass derives table names from class names — Account maps to account, ProductInstance to product_instance — so __tablename__ boilerplate disappears and naming can’t drift. ORM classes take a Model suffix (AccountModel) while Pydantic schemas keep the clean names (Account), which ends the import collisions that FastAPI projects otherwise accumulate.

Since writing the conventions down, schema-related correction loops have mostly disappeared: new tables come out right the first time, queries use the right column names, and migrations autogenerate cleanly because the models follow patterns Alembic expects. The conventions help the humans too — but agents make more schema decisions per hour than any human, so they collect more of the dividend.