The previous article in Programmer Craft in the AI Era discussed API contracts. An API is a service’s promise to the outside. A database is the system’s promise to itself. Fields, indexes, unique constraints, transactions, locks, and migration scripts determine whether data stays consistent, whether the system can handle growth, and whether failures can still be explained afterward.

AI easily treats the database as “a place to store an object”. If a requirement has an order, it creates an order table. If fields are uncertain, it puts them into JSON. If a query is slow, it suggests adding an index. If concurrency breaks, it adds a lock afterward. That order works in demos, but leaves hard technical debt in production.

A database is not a place to dump JSON. It is the least forgiving part of the system.

Table Structure Is Business Rule

Many people treat table structure as a side effect of code. First write objects, then let the ORM generate tables. This is fast, but it easily hides business rules in code and leaves the database with only loose fields.

For example, AI may generate this task table:

create table jobs (
  id uuid primary key,
  user_id uuid not null,
  status text not null,
  payload jsonb not null,
  result jsonb,
  created_at timestamptz not null default now()
);

This table can be used, but it expresses almost no rules:

• status can be any string.
• Whether the same business task can be created twice is unclear.
• Whether a task can be claimed by a worker is unclear.
• Whether retry count, next run time, and error information live inside payload is unknown.
• The index needed for querying pending tasks is not expressed.

A table that looks more like a business system should expose key states and access paths:

create table jobs (
  id uuid primary key,
  user_id uuid not null,
  job_type text not null,
  dedupe_key text not null,
  status text not null check (
    status in ('pending', 'running', 'success', 'failed', 'cancelled')
  ),
  priority integer not null default 0,
  run_at timestamptz not null default now(),
  locked_by text,
  locked_at timestamptz,
  attempt integer not null default 0,
  max_attempts integer not null default 3,
  payload jsonb not null,
  result jsonb,
  last_error text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, job_type, dedupe_key)
);

This table still uses JSON, but JSON no longer carries every responsibility. payload can hold input parameters that change quickly and are rarely queried. Core rules such as state, scheduling, retry, claiming, and deduplication must become explicit fields.

unique (user_id, job_type, dedupe_key) is a business rule, not a performance optimization. It says the same user, same task type, and same dedupe key can only produce one task. Without this constraint, no amount of “check first” code can stop concurrent inserts.

When asking AI to design a table, start with:

Do not give me the ORM model directly.
First list the business invariants of this entity:
- Which fields must be unique?
- Which states are enums?
- Which fields participate in query, sorting, claiming, expiration, and retry?
- Which content can live in JSON, and which must be independent columns?
- Which rules must be guaranteed by database constraints instead of code only?

Database constraints do not slow development down. They pin the most important rules to the system foundation.

JSON Fields Need Boundaries

JSON fields are useful. They fit unstable extension data, raw third-party responses, low-frequency configuration, event payloads, and audit snapshots.

But they are not suitable for carrying all business logic.

A common smell is:

create table tool_calls (
  id uuid primary key,
  user_id uuid not null,
  data jsonb not null,
  created_at timestamptz not null
);

data may contain tool_id, provider_id, status, cost, search_id, latency_ms, and error_code. It saves effort in the short term, but creates several long-term problems:

• Queries need data->>'tool_id', making indexes more complex and easier to miss.
• Field types are unstable; numbers may be written as strings.
• Data quality cannot be guaranteed by constraints.
• Analytics and debugging depend on JSON parsing.
• During refactors, no one knows who uses which JSON path.

If these fields are core query conditions or analytics dimensions, they should be independent columns:

create table tool_calls (
  id uuid primary key,
  user_id uuid not null,
  tool_id text not null,
  provider_id text not null,
  status text not null check (status in ('success', 'failed')),
  latency_ms integer,
  result_count integer,
  search_id uuid,
  error_code text,
  request_payload jsonb not null,
  response_summary jsonb,
  created_at timestamptz not null
);

JSON can remain, but it moves back to extension payload and raw snapshot.

To decide whether a field should be a column, ask five questions:

Question	If yes
Will it often appear in where conditions?	Make it a column
Will it often be used for sorting or pagination?	Make it a column
Will it participate in group by analytics?	Make it a column
Does it need unique or foreign-key constraints?	Make it a column
Would a type error cause a business incident?	Make it a column

When asking AI to design JSON fields, be explicit:

Split fields into three categories:
1. Fields that must be independent columns.
2. Extension fields that can live in jsonb.
3. Fields that should not be stored or must be masked.
Explain the query, constraint, or analytics need behind each independent column.

Once this step is done, the table is much less likely to become a universal junk drawer.

Indexes Start From Query Patterns

AI likes saying “add an index” after a slow query appears. But which index, in what field order, whether it covers sorting, and how it affects writes must all be derived from query patterns.

Suppose the tool-call history page needs a user’s calls for one tool in the last day:

select id, tool_id, status, latency_ms, created_at
from tool_calls
where user_id = :user_id
  and tool_id = :tool_id
  and created_at >= :start
order by created_at desc
limit 100;

A reasonable index is:

create index idx_tool_calls_user_tool_time
on tool_calls (user_id, tool_id, created_at desc);

Why this order? user_id and tool_id are equality filters. created_at handles range and ordering. The database can first locate user and tool, then scan the first 100 rows in descending time.

If another query summarizes yesterday’s call count by provider:

select provider_id, count(*)
from tool_calls
where created_at >= :start
  and created_at < :end
group by provider_id;

The previous index may not help. It starts with user_id, but this query has no user condition. You may need:

create index idx_tool_calls_time_provider
on tool_calls (created_at, provider_id);

Or, more realistically, a daily summary table:

create table provider_daily_stats (
  stat_date date not null,
  provider_id text not null,
  call_count bigint not null,
  success_count bigint not null,
  primary key (stat_date, provider_id)
);

More indexes are not always better. Every index increases write cost, consumes disk, and adds maintenance overhead. A low-selectivity field alone may not be useful; for example, a single-column index on status with only a few values is often weak.

When asking AI to recommend indexes, do not let it look only at table structure. Make it look at queries:

Design indexes from these query patterns:
- where conditions, order by, and limit for each query.
- Estimated data volume and selectivity.
- Which queries each index serves.
- Which queries should not rely on indexes and need summary tables or partitioning.
- Write cost and disk cost.

An index whose field order can be explained is an index that has actually been thought through.

Transaction Boundaries Must Not Cross Slow Operations

Transactions guarantee that a group of database operations succeed or fail together. They are important and easy to misuse.

A dangerous pattern:

async with transaction():
    order = await create_order()
    await reserve_inventory(order)
    await call_payment_api(order)
    await send_notification(order)

This puts external payment and notification calls inside a database transaction. If the external call is slow, the transaction stays open. While the transaction is open, locks may be held and connections occupied. Downstream instability directly drags on the database.

A better boundary is:

async with transaction():
    order = await create_order(status="pending_payment")
    await reserve_inventory(order)
    await outbox.add("payment.requested", {"order_id": order.id})

The transaction only wraps database writes that must stay consistent. Payment is handed to a background task through an outbox. After payment succeeds, another short transaction updates order state.

The principle is simple:

• Put only database operations that must remain consistent inside a transaction.
• Do not make HTTP/RPC calls inside transactions.
• Do not read or write large files inside transactions.
• Do not perform long CPU computation inside transactions.
• Do not wait for user input or external tasks inside transactions.
• Keep transactions short and locks small.

This connects directly to the earlier article on execution models. Slow work should leave both the request path and the database transaction.

When asking AI to write transactional code, require:

Mark the beginning and end of every database transaction.
External HTTP/RPC, file I/O, and long computation are forbidden inside transactions.
Explain which business invariant each transaction protects.
If an external system must be called, use an outbox or task table.

If AI cannot explain what a transaction protects, the transaction is probably there only to make the code look safe.

Locks Are Protocols, Not Patches

When multiple executions update the same data, locks or version control must be considered. A lock is not a patch after a bug. It is a protocol between multiple actors.

For inventory deduction, this is dangerous:

inventory = get_inventory(sku)
if inventory.available < quantity:
    raise OutOfStock()
inventory.available -= quantity
save(inventory)

Two requests can both read available inventory as 10, each deduct 8, and oversell.

One approach is a single atomic update:

update inventories
set available = available - :quantity
where sku = :sku
  and available >= :quantity;

Then check affected rows. If it is 1, deduction succeeded; if 0, inventory was insufficient.

Another approach is an explicit row lock:

select *
from inventories
where sku = :sku
for update;

After taking the lock, check and update. Row locks fit scenarios that need several fields and more complex business judgment, but the lock must be held briefly.

There is also optimistic locking:

update accounts
set balance = balance + :delta,
    version = version + 1
where id = :account_id
  and version = :expected_version;

If the update fails, the version changed; reread or retry.

The tradeoffs:

Method	Good for	Risk
Atomic update	Simple counters, inventory, quota deduction	Business logic cannot be too complex
select for update	Lock one row and perform complex checks	Lock wait, deadlock, long transaction
Optimistic version	Low-conflict, read-heavy scenarios	More retries under high conflict
Unique constraint	Deduplication, idempotency records	Must handle conflict errors
Advisory lock	Cross-row or business-key locking	Misuse can become an implicit global lock

When asking AI to write concurrent updates, be direct:

Do not use non-atomic "read first, then update".
Provide a concurrency-safe plan: atomic update, row lock, optimistic lock, or unique constraint.
Explain lock granularity, lock holding time, deadlock risk, and retry strategy.
Write a test where two concurrent requests deduct inventory at the same time.

Concurrency correctness cannot rely on “it probably will not collide”.

Slow Queries Are Design Problems, Not Only SQL Problems

Slow queries are often not caused by ugly SQL. They come from access patterns that were poorly designed at the start.

Suppose an admin page lists users and shows last login, last purchase, order count, and unread message count for each row. If AI writes it directly, it may become:

users = get_users(page=page)
for user in users:
    user.last_login = get_last_login(user.id)
    user.order_count = count_orders(user.id)
    user.unread_count = count_unread_messages(user.id)

This is classic N+1 querying. A page with 50 users may trigger another 150 queries.

A better design uses batch queries or pre-aggregation:

select
  u.id,
  u.name,
  s.last_login_at,
  s.order_count,
  s.unread_count
from users u
left join user_summary s on s.user_id = u.id
order by u.created_at desc
limit 50;

user_summary can be maintained by async tasks or refreshed by database jobs. The point is not to aggregate a pile of large tables on every list-page request.

When investigating slow queries, check:

• Whether too many columns are returned.
• Whether indexes are missing or field order is wrong.
• Whether sorting requires an extra sort.
• Whether joins use high-cardinality fields without proper indexes.
• Whether N+1 queries exist.
• Whether aggregation is inside a high-frequency request.
• Whether where uses functions and prevents index use.
• Whether partitioning, archiving, or summary tables are needed.

When asking AI to optimize a slow query, do not let it only rewrite SQL:

First analyze this page's access pattern:
- Which fields are displayed on each request?
- Which table does each field come from?
- Which fields can be asynchronously pre-aggregated?
- Which queries must be real-time?
- Does N+1 querying exist?
- Provide expected explain output, index suggestions, and risks under data growth.

Many performance problems must be solved by data modeling, not by fancier SQL.

Migration Scripts Must Be Written for Production Systems

AI generates migrations quickly, but production migrations are not simply alter table add column.

Adding a nullable field is usually safe:

alter table users add column timezone text;

But adding a not null field, adding a default value, changing field type, creating a large index, or backfilling historical data may lock tables, slow writes, or create long transactions.

A safer required-field rollout:

1. Add a nullable field.
2. New code starts writing the new field.
3. A background process backfills old data in batches.
4. Verify no null values remain.
5. Add the not null constraint.

For example:

alter table users add column timezone text;

Do not backfill the whole table in one update:

update users
set timezone = 'Asia/Shanghai'
where timezone is null
  and id > :last_id
order by id
limit 1000;

Different databases support limit in update differently; real implementation must follow the database dialect. The key is batching, small transactions, resumability, and rerunnability.

Creating large indexes also needs care. PostgreSQL usually uses:

create index concurrently idx_tool_calls_user_time
on tool_calls (user_id, created_at desc);

concurrently reduces table-locking risk, but it cannot run inside an ordinary transaction block. The migration tool must support that special behavior.

When asking AI to write migrations, require:

This is a production migration on a large table and must not lock the table for long.
Provide an expand -> backfill -> contract migration plan.
Backfill must be batched, retryable, and observable.
Create indexes concurrently and explain database dialect limitations.
Provide rollback strategy and validation SQL.

A migration script is not a disposable chore. It is part of production change management.

ORM Does Not Replace Understanding the Database

ORMs are convenient, and AI likes them because object relations are clear and code is easy to generate.

But ORMs hide many database facts:

• One line of code may trigger many queries.
• Accessing one attribute may lazy-load.
• Batch updates may become row-by-row updates.
• Transaction scope may be larger than expected.
• Default isolation level may not fit the business.
• JSON fields and complex indexes may not be represented completely.
• Migration scripts may not match the production database dialect.

For example:

orders = session.query(Order).limit(100).all()
for order in orders:
    print(order.user.name)

If user is lazy-loaded, this can trigger 101 queries. AI easily writes this because it looks like ordinary object access.

When asking AI to write ORM code, make query behavior explicit:

Avoid N+1 queries.
Preload user, but only fetch id/name.
Explain roughly what SQL will be generated.
Batch updates must not commit row by row.
Transactions must be explicitly controlled in the service layer.

ORM is a tool, not a substitute for database knowledge. The more you rely on AI-generated ORM code, the more you should ask it to explain the SQL underneath.

A Database-design Prompt for AI

Before writing the data layer, use this prompt:

Do not write ORM models or migrations yet.

Design the database for this feature:
1. List core entities, business invariants, and state machines.
2. Explain which fields must be independent columns, which can live in jsonb, and which must not be stored.
3. Provide table structure, primary keys, unique constraints, check constraints, and foreign-key tradeoffs.
4. List major query patterns: where, order by, limit, group by, and estimated data volume.
5. Design indexes for each query, and explain field order and write cost.
6. Mark transaction boundaries and explain the business invariant protected by each transaction.
7. Analyze concurrent update scenarios and choose atomic update, row lock, optimistic lock, or unique constraint.
8. Explain slow-query risks, N+1 risks, and whether summary tables or partitions are needed.
9. Provide production migration plan: expand, backfill, contract, validation, and rollback.
10. Only then write ORM models, repositories, and tests.

This prompt is long, but databases deserve that length. If the data structure is wrong, every later line of code becomes a patch around it.

Ask These Questions Before Writing a Table

Before writing the data layer, ask:

• Will this field become a query condition later?
• Can this state be restricted by a check constraint?
• Is this uniqueness enforced by code checks or database constraints?
• Does this JSON field hide a core analytics dimension?
• What will this query’s data volume be one year from now?
• Which queries does this index serve, and which writes does it slow down?
• Does this transaction contain slow work?
• What happens if two requests update the same row at the same time?
• Will this migration lock a large table?
• Have I looked at the SQL generated by the ORM?

AI can quickly write tables, indexes, ORM models, and migrations. The programmer’s job is to judge whether they really express business rules and whether they survive concurrency, growth, and production changes.

A database does not become more forgiving because the code was written by AI. It only believes constraints, transactions, indexes, and real data volume.

WeChat

Follow ZiCode on WeChat

If this post was useful, you can follow later updates on WeChat as well.

X / Twitter

Follow @ax2_zicode

Faster technical notes, short thoughts, and new-post alerts are posted on X.

X Open X Profile @ax2_zicode