AI assistants
Let Claude, Copilot, Cursor and other AI tools write changes that trek can certify.
AI coding assistants write good SQL, but they don't know trek's rules: named changes in the plan, a revert for every deploy, one transaction per change, and never editing what has been deployed. trek gives them those rules three ways. Use any or all of them.
AGENTS.md in every project
trek init and trek import write an AGENTS.md into the project folder: a short version of the rules, filled in for the project's database. Claude Code, Copilot, Cursor, Codex and most other assistants read AGENTS.md on their own. Pass --no-agents-file to skip it, and keep or edit it like any other file: trek never overwrites one that exists. Older projects can get one by running trek init again in the folder; it only creates files that are missing.
The full guide
trek ai-guide > docs/trek-ai-guide.md # the guide for the installed version
trek ai-guide prints the guide below as Markdown, matching the installed version, for assistants that can't browse. Every page of this site is also available as Markdown (add index.md to its address), and llms.txt lists them all.
The MCP server
trek mcp runs a Model Context Protocol server over stdio for one project folder. Assistants that support MCP then add changes, write scripts and run the checks through tools instead of guessing at file formats and command output:
| Tool | Does |
|---|---|
project_info | The database dialect, environments, variable names and plan size |
plan_show, read_change | The plan, and a change's scripts |
add_change | Add a change with its deploy, revert and verify scripts in one call, like trek add |
write_change_script, update_change | Edit a change that hasn't been deployed: a script, or its !notx, !irreversible, requires and note |
rework_change | Supersede a deployed change, like trek rework |
write_repeatable | Create or replace a view, procedure, function or environment script |
check, lint_sql | plan check with each lint finding's code, file and line; lint SQL before writing it |
render | A script with an environment's variables filled in |
guide | The guide below (also a resource, trek://guide, and a write-migration prompt) |
The server reads and writes files in the project folder only. It never connects to a database and never needs a password, so an assistant can draft changes but a person deploys them with trek deploy.
Claude Code, from the repository root:
claude mcp add trek -- trek mcp -C db
Claude Desktop (claude_desktop_config.json), Cursor (.cursor/mcp.json) and other clients that use the same format:
{{
"mcpServers": {{
"trek": {{ "command": "trek", "args": ["mcp", "-C", "/path/to/repo/db"] }}
}}
}}
VS Code (.vscode/mcp.json):
{{
"servers": {{
"trek": {{ "type": "stdio", "command": "trek", "args": ["mcp", "-C", "${{workspaceFolder}}/db"] }}
}}
}}
Installed as a .NET tool, trek is on the path; with the downloaded executable, give its full path as command.
Review what the assistant wrote
- Read every
revert.sql: it must undo the deploy exactly. Try it locally withtrek deploy,trek revert --last 1,trek deploy, thentrek verify. - Run
trek plan checkin CI so lint errors and undefined variables never merge. - Check that the assistant didn't edit a change that is already deployed:
trek statusflags it as edited after deploy.
This guide is for AI coding assistants (and people) writing database changes in a Badger Trek (trek) project. Follow it exactly: trek certifies every change by hash, so a mistake in a deployed change can't be fixed by editing it later.
The project
trek.yaml provider (sqlserver, postgresql, mysql, mariadb or sqlite), environments, variables
trek.plan every change, one per line, in deploy order
changes/<name>/
deploy.sql makes the change
revert.sql undoes exactly what deploy.sql did
verify.sql optional: raises an error if the change is not in effect
repeatable/ views, procedures, functions, triggers: re-applied whenever they change
environments/<env>/ repeatables that run only in that environment (seed data, test users)
Read trek.yaml first to learn the provider: provider: sqlserver means T-SQL, provider: postgresql means PostgreSQL, provider: mysql means MySQL, provider: mariadb means MariaDB, provider: sqlite means SQLite. Never put connection strings, passwords or secrets in scripts.
Badger Trek was called Badger SQL Migrate (command bsm, NuGet Badger.SqlMigrate) up to 0.3.0. A folder with bsm.yaml and bsm.plan is an old project: don't run trek init over it. Tell the person it needs upgrading (https://lonebadger.tech/badger-trek/docs/upgrading/): the files are renamed, and each database's journal is moved once with SQL that trek prints. Never run that SQL yourself, and don't edit deployed changes to rename -- bsm: directives unless the person asks.
Adding a change
Create the change with the CLI (or the
add_changeMCP tool). Don't write plan lines by hand:trek add add-customer-email --requires create-customer --note "email for receipts"This creates
changes/add-customer-email/with the three scripts and appends a line totrek.plan.- Write
deploy.sql,revert.sqland, when useful,verify.sql. - Run
trek plan check(or thecheckMCP tool) and fix every error and warning it reports. - Stop there. A person deploys with
trek deploy. Don't rundeploy,revert,rebase,repairorbaselineunless the user asked you to, and never against an environment other than their local one.
Names and dependencies
- Names are short, lower-case, hyphenated and describe the change:
create-customer,add-order-status-index,backfill-customer-region. Letters, digits,-,_and.only. --requireslists changes that must be deployed first, usually the change that created the table you alter. Required changes must already be in the plan.- One logical change per change. Several small changes are better than one large one: each can be reverted on its own.
Rules for the scripts
- revert.sql undoes exactly what deploy.sql did, in reverse order. Deploy creates a table and an index; revert drops the index, then the table. Deploy adds a column; revert drops it. Deploy changes data; revert restores it, or the change is marked irreversible.
- verify.sql raises an error when the change is missing. A query that merely returns no rows passes. Delete
verify.sqlif there is nothing worth checking. - A change runs in one transaction. Don't write
BEGIN TRANSACTION,COMMITorROLLBACK. A failure rolls back the whole change, so scripts don't need cleanup code. - Statements that can't run in a transaction need
--notx. Examples:CREATE INDEX CONCURRENTLY,ALTER DATABASE, full-text indexes. Create the change withtrek add <name> --notxand write the scripts so they can be run again (IF NOT EXISTSin deploy,IF EXISTSin revert), because a failure part way leaves partial work. The linter (TREK001) tells you when this is needed; with the MCP server, set it withupdate_change(notx: true). - Irreversible changes: when undoing really isn't possible (dropping data that can't be restored), use
trek add <name> --irreversible. There is then norevert.sql. Prefer reversible designs: rename or copy before you drop. - Don't use
USE(SQL Server, MySQL, MariaDB) or\connect(PostgreSQL). trek deploys to the environment's database. - Schema-qualify every object:
dbo.Customer,public.customer(MySQL, MariaDB and SQLite: don't; writecustomer). - Data changes belong in changes too, written to be correct on every environment's data. Use variables for values that differ between environments.
Never edit a deployed change
Once a change is deployed anywhere, its scripts are frozen: if a single character changes, its hash no longer matches and every deploy stops. To change what a deployed change did:
- New behaviour (another column, a new procedure body): add a new change. For a definition that can be run again,
trek rework <name>adds<name>-v2, whose deploy script starts as a copy to edit and whose revert restores the old version. - A harmless fix to a deployed change's
revert.sql,verify.sqlor comments: edit it, then a person runstrek repair <name>in each environment. Don't use repair to change what deploy does.
You can't see which changes are deployed from the files alone. When unsure, ask the user, or have them run trek status. Changes that are new in the current branch and not yet in the main branch are usually safe to edit.
Repeatables
Views, stored procedures, functions and triggers go in repeatable/, not in changes, written so they can run again:
- SQL Server:
CREATE OR ALTER VIEW|PROCEDURE|FUNCTION|TRIGGER ... - PostgreSQL:
CREATE OR REPLACE VIEW|FUNCTION|PROCEDURE ..., andDROP ... IF EXISTSthenCREATEfor objects that have noOR REPLACE. - MySQL:
CREATE OR REPLACE VIEW ..., andDROP PROCEDURE|FUNCTION|TRIGGER IF EXISTSthenCREATE. - MariaDB:
CREATE OR REPLACE VIEW|PROCEDURE|FUNCTION|TRIGGER ....
Name them by path, for example repeatable/views/active_customer.sql. trek applies them after the changes, whenever their text differs from what was last applied. When one repeatable uses another, declare it on the first line:
-- trek:requires views/active_customer.sql functions/full_name.sql
List columns explicitly in views (no SELECT *): a view that selects every column depends on all of them, so a later change can't drop or retype a column the view never needed.
Tables, columns, indexes and data are never repeatables. Anything a change needs while it deploys (a function used in a computed column or a default) must be created by a change, because repeatables run after changes.
Scripts under environments/<env>/ run only in that environment, after the shared repeatables. Write them so they can run again (MERGE, INSERT ... ON CONFLICT DO NOTHING, IF NOT EXISTS).
Variables
Scripts are preprocessed before they run. {name} and $(name) are replaced with variables from trek.yaml or built-ins such as {env} (environment name) and {db} (database name). Use them for names and values that differ between environments. An unknown variable is an error; write {{name}} for literal braces. JSON ('{"a":1}'), format strings ('{0}') and ODBC escapes are left alone. trek render <change> -e <env> shows the exact SQL an environment will receive.
SQL Server
- Split batches with a line containing only
GO, as SSMS does.CREATE VIEW,CREATE PROCEDURE,CREATE FUNCTIONandCREATE TRIGGERmust be the first statement in their batch. - Guard objects that may exist with
IF OBJECT_ID(N'dbo.Customer', N'U') IS NULLorDROP ... IF EXISTS. - verify.sql:
IF COL_LENGTH('dbo.Customer', 'Email') IS NULL THROW 50000, 'Customer.Email is missing', 1; - Temporal tables: when you set
SYSTEM_VERSIONING = OFFto alter a table, turn it back on in the same script (TREK004).
Example change add-customer-email:
-- deploy.sql
ALTER TABLE dbo.Customer ADD Email nvarchar(320) NULL;
GO
CREATE UNIQUE INDEX UX_Customer_Email ON dbo.Customer (Email) WHERE Email IS NOT NULL;
-- revert.sql
DROP INDEX UX_Customer_Email ON dbo.Customer;
GO
ALTER TABLE dbo.Customer DROP COLUMN Email;
-- verify.sql
IF COL_LENGTH('dbo.Customer', 'Email') IS NULL
THROW 50000, 'dbo.Customer.Email is missing', 1;
Converted projects
A project written by trek convert schema has CONVERSION.md at its root and convert.json next to trek.yaml. Scripts marked /* REVIEW trek convert: ... */ were translated from the other database and need a person's judgement: resolve the issue the comment describes, then delete the comment (and its line in the file's header). Don't edit convert.json: trek convert data uses it to map names the way the schema was converted.
PostgreSQL
- Statements end with
;. Function bodies andDOblocks use dollar quoting ($$ ... $$); semicolons inside them don't split statements. - Use
IF EXISTS/IF NOT EXISTSfreely; DDL is transactional, so a failed change leaves nothing behind. - verify.sql: a
DOblock that raises:DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'customer' AND column_name = 'email') THEN RAISE EXCEPTION 'customer.email is missing'; END IF; END $$; ALTER TYPE ... ADD VALUE: the new enum value can't be used until the change commits, so use it in a later change (TREK007).- psql meta-commands work:
\i,\ir,\set,:'var',\if/\endif,\copy ... from 'file.csv',\gexec. These are refused (TREK005):\connect,\gset,\copy ... to,\o,\!,\prompt, and backquoted shell commands.
Example change add-customer-email:
-- deploy.sql
ALTER TABLE public.customer ADD COLUMN email text;
CREATE UNIQUE INDEX customer_email_key ON public.customer (email) WHERE email IS NOT NULL;
-- revert.sql
DROP INDEX public.customer_email_key;
ALTER TABLE public.customer DROP COLUMN email;
MySQL and MariaDB
- Statements end with
;. A procedure, function, trigger or event body with several statements goes betweenDELIMITER //andDELIMITER ;lines, as in themysqlclient; without them the body ends at its first;(TREK005). - DDL commits as it runs and can't be rolled back: a change that fails part-way keeps what already ran. Keep each change to one table change, use
IF EXISTS/IF NOT EXISTS, and write a verify script. - A schema is a database, and trek works in the environment's database: don't qualify names with a database, and don't
USEanother (TREK009). trek's own tables aretrek_*in the same database. - verify.sql: a statement that fails when the change is missing, such as
SELECT email FROM customer LIMIT 0; - MariaDB has sequences (
CREATE SEQUENCE,NEXTVAL(seq)),RETURNING, andCREATE OR REPLACEfor stored programs; MySQL has none of these. MySQL has functional indexes (KEY ((lower(email)))) and the->>JSON operator; MariaDB has neither (index a generated column; useJSON_VALUE).
SQLite
- Statements end with
;. Semicolons inside strings, comments andCREATE TRIGGER ... BEGIN ... END;bodies don't split statements. - There are no schemas: write
customer, ormain.customer. trek's own tables aretrek_*in the same file. ALTER TABLEcan only rename a table or column, add a column (nullable, orNOT NULLwith a constant default) and, from SQLite 3.35, drop a simple column.sqlite.minVersionin trek.yaml (default "3.26") is the oldest SQLite that opens the database: use only SQL it understands (lint warns, TREK008), so below 3.35 drop a column by rebuilding the table. For anything else (type, constraint, primary key, foreign key changes) rebuild the table: createnew_<table>, copy the rows, drop the old table, rename, and re-create its indexes, triggers and the views that use it. If other tables have foreign keys to it, the change must be!notxand wrap the rebuild inPRAGMA foreign_keys = OFF; BEGIN; ... COMMIT; PRAGMA foreign_keys = ON;(PRAGMA foreign_keysdoes nothing inside trek's transaction, TREK001).- Repeatables: there is no
CREATE OR REPLACE; writeDROP VIEW IF EXISTS v;thenCREATE VIEW v ...(and the same for triggers). - verify.sql: select from what must exist; SQLite raises "no such table/column" when it doesn't:
SELECT email FROM customer LIMIT 0; - sqlite3 dot-commands (
.read,.mode,.import) are not SQL and are refused (TREK005).VACUUM,ATTACHandPRAGMA journal_modeneed!notx.
Example change add-customer-email:
-- deploy.sql
ALTER TABLE customer ADD COLUMN email TEXT;
CREATE UNIQUE INDEX customer_email_key ON customer (email) WHERE email IS NOT NULL;
-- revert.sql
DROP INDEX customer_email_key;
ALTER TABLE customer DROP COLUMN email;
Linter codes
trek plan check reports these. Errors must be fixed; fix warnings unless the user says otherwise.
| Code | Provider | Meaning and fix |
|---|---|---|
| TREK000 | SQL Server | The script didn't parse, so lint was skipped. Check the syntax. |
| TREK001 | all | A statement can't run in a transaction. Mark the change !notx (update_change with notx: true, or --notx on trek add) and make it re-runnable. |
| TREK002 | SQL Server | USE switches databases. Remove it. |
| TREK003 | all | BEGIN/COMMIT/ROLLBACK in a transactional change. Remove it. |
| TREK004 | SQL Server | SYSTEM_VERSIONING = OFF without turning it back on. Turn it back on in the same script. |
| TREK005 | PostgreSQL, SQLite, MySQL, MariaDB | A psql command trek can't run, or unbalanced \if/\endif; on SQLite, a sqlite3 dot-command (.read, .mode); on MySQL and MariaDB, a client command (source) or a stored program body without DELIMITER. Remove it, or add the delimiter. |
| TREK007 | PostgreSQL | An enum value added with ADD VALUE is used in the same change. Move that use to a later change. |
| TREK008 | SQLite | SQL newer than sqlite.minVersion in trek.yaml (RETURNING, DROP COLUMN, STRICT, ->> ...). Rewrite it for the older engine; raise minVersion only if the user says every program that opens the database has that SQLite. |
| TREK009 | MySQL, MariaDB | USE switches databases, and would send the rest of the change and trek's journal entry there. Remove it. |
Generating a change from a database (person-run)
When a person has already made schema edits in their local database, trek add <name> --from-diff writes deploy.sql and revert.sql by comparing that database with the plan. Review the output: it is a starting point, and data migrations still need writing by hand.
Commands for checking your work
| Command | Needs a database | Use |
|---|---|---|
trek plan check --json | no | Errors and lint findings for the whole project |
trek plan show --json | no | Every change with its dependencies, flags and tags |
trek render <change> [--kind revert] -e <env> | no | The SQL an environment will receive |
trek ai-guide | no | This guide, matching the installed version |
trek status -e <env> --json | yes | What is deployed (ask the user before connecting) |
--json prints machine-readable output on stdout; warnings and errors go to stderr. Exit code 0 means success.
MCP server
trek mcp -C <project folder> runs a Model Context Protocol server over stdio with the tools project_info, plan_show, read_change, add_change, write_change_script, update_change, rework_change, write_repeatable, check, lint_sql, render and guide. It only reads and writes project files; it never connects to a database.