Databases and migrations

For database issues, confirm the connection target before inspecting the schema and data. The current template has two D1 databases: DB stores application data, and ANALYTICS_DB stores analytics data. Local and remote environments each have independent state.

1. Identify the database you are inspecting

In wrangler.jsonc, binding is the name used to access the database in code, database_name is the resource name, and database_id identifies the remote resource. The CLI adjusts resource names to match the project name, so do not always look for saavo-template-db.

These local checks are read-only and do not change the schema:

npx wrangler d1 execute DB --local --command "SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;"
npx wrangler d1 execute ANALYTICS_DB --local --command "SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;"

After confirming the account and resource ID, explicitly use --remote for remote queries:

npx wrangler d1 execute DB --remote --command "SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;"

Do not omit the environment flag and guess which environment the command will affect. See D1 Wrangler commands for command arguments.

2. no such table or no such column

First check which database owns the table named in the error, then determine whether a migration was missed.

For a local project, run:

npm run db:migrate:local

For a production project, after confirming the remote target, migration contents, and compatibility, run:

npm run db:migrate:remote

Each command processes both databases in its respective environment, not just DB. The script runs them sequentially. If the first database succeeds and the second fails, you cannot assume that neither database changed.

For missing columns, also check whether only the initialization SQL was changed. Adding a column to the baseline file does not automatically add it to an existing database. Schema changes must be recorded in schema/migrations.

Inspect the table schema to confirm the result, for example:

PRAGMA table_info('saas_user');

Once the schema meets the code's requirements, retry the original application request. A migration command finishing without an error is not sufficient verification on its own.

3. A nonempty database is missing its baseline table

The migration script checks the database as follows:

1. If there are no user-defined tables, run the database's initialization script.
2. If user-defined tables and the corresponding baseline table exist, continue applying migrations.
3. If user-defined tables exist but the baseline table is missing, stop.

The third case may indicate a binding to another project, an incomplete import, or an earlier destructive schema operation. Save the table list, check the resource ID and previous operations, then choose a recovery plan.

The script uses the presence of saas_user or analytics_session to identify the baseline. This is not a full schema validation. Do not bypass the check by manually creating an empty table with the same name.

4. A migration fails or deployment is interrupted

Record the database that failed, migration filename, first SQL error, and execution time. Check filenames in the migration directory:

schema/migrations/0001-db-add-example.sql
schema/migrations/0002-analytics-add-example.sql

The prefix must contain four digits. The db or analytics segment in the middle determines which database the migration belongs to. An incorrectly named file may be rejected by the check script or excluded from the relevant database's migration set.

After confirming that d1_migrations exists, run this read-only query against the target database:

SELECT id, name, applied_at
FROM d1_migrations
ORDER BY id DESC
LIMIT 20;

Inspect the actual schema as well. Earlier migrations that completed are not rolled back when a later migration fails. Updates across the two databases are not a single transaction either.

The current deploy:update runs remote migrations before deploying the new Worker. Dropping or renaming columns still used by the old code can cause a production incident before the new version is deployed. Design migrations with both versions in mind.

5. Recently saved data cannot be found

Check the following in order:

1. Did the write request actually succeed, or does the response body report a business failure?
2. Do reads and writes use the same environment, binding, and resource ID?
3. Does the data need to pass through a webhook or Queue before it is written?
4. Does the query use an incorrect user ID, connection ID, status, or time range?
5. Are time fields compared as Unix milliseconds, without treating seconds as milliseconds?

Do not export session or user tables with SELECT * for troubleshooting. Query only the IDs, statuses, and timestamps needed for the current issue to keep tokens, hashes, and encrypted data out of shared logs.

6. Unique or foreign key constraints fail

A unique constraint failure may come from a duplicate request or an incorrectly designed business key. Payments and Reaction each have their own idempotency boundaries. Identify the violated index first, then trace the source of the business operation.

For a foreign key failure, check that the parent record exists, writes occur in the correct order, and the data was not accidentally written to another database. Do not disable foreign key checks to let invalid data into the database.

Before fixing payment or access data in particular, understand the relationships between the event ledger, transaction details, roles, and entitlements. Manually deleting a record may cause the system to treat an already completed operation as a first execution, resulting in duplicate grants.

Verify the fix

Confirm the schema and migration records in the target database, retry a minimal business operation, then query its status. If migrations were involved, also confirm that existing users can still read and update their old data.

See the database reference for field details and Production databases and migrations for the order of production changes.