Lone Badger Tech

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:

ToolDoes
project_infoThe database dialect, environments, variable names and plan size
plan_show, read_changeThe plan, and a change's scripts
add_changeAdd a change with its deploy, revert and verify scripts in one call, like trek add
write_change_script, update_changeEdit a change that hasn't been deployed: a script, or its !notx, !irreversible, requires and note
rework_changeSupersede a deployed change, like trek rework
write_repeatableCreate or replace a view, procedure, function or environment script
check, lint_sqlplan check with each lint finding's code, file and line; lint SQL before writing it
renderA script with an environment's variables filled in
guideThe 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 with trek deploy, trek revert --last 1, trek deploy, then trek verify.
  • Run trek plan check in CI so lint errors and undefined variables never merge.
  • Check that the assistant didn't edit a change that is already deployed: trek status flags 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

  1. Create the change with the CLI (or the add_change MCP 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 to trek.plan.

  2. Write deploy.sql, revert.sql and, when useful, verify.sql.
  3. Run trek plan check (or the check MCP tool) and fix every error and warning it reports.
  4. Stop there. A person deploys with trek deploy. Don't run deploy, revert, rebase, repair or baseline unless 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.
  • --requires lists 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.sql if there is nothing worth checking.
  • A change runs in one transaction. Don't write BEGIN TRANSACTION, COMMIT or ROLLBACK. 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 with trek add <name> --notx and write the scripts so they can be run again (IF NOT EXISTS in deploy, IF EXISTS in revert), because a failure part way leaves partial work. The linter (TREK001) tells you when this is needed; with the MCP server, set it with update_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 no revert.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; write customer).
  • 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.sql or comments: edit it, then a person runs trek 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 ..., and DROP ... IF EXISTS then CREATE for objects that have no OR REPLACE.
  • MySQL: CREATE OR REPLACE VIEW ..., and DROP PROCEDURE|FUNCTION|TRIGGER IF EXISTS then CREATE.
  • 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 FUNCTION and CREATE TRIGGER must be the first statement in their batch.
  • Guard objects that may exist with IF OBJECT_ID(N'dbo.Customer', N'U') IS NULL or DROP ... 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 = OFF to 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 and DO blocks use dollar quoting ($$ ... $$); semicolons inside them don't split statements.
  • Use IF EXISTS / IF NOT EXISTS freely; DDL is transactional, so a failed change leaves nothing behind.
  • verify.sql: a DO block 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 between DELIMITER // and DELIMITER ; lines, as in the mysql client; 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 USE another (TREK009). trek's own tables are trek_* 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, and CREATE OR REPLACE for 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; use JSON_VALUE).

SQLite

  • Statements end with ;. Semicolons inside strings, comments and CREATE TRIGGER ... BEGIN ... END; bodies don't split statements.
  • There are no schemas: write customer, or main.customer. trek's own tables are trek_* in the same file.
  • ALTER TABLE can only rename a table or column, add a column (nullable, or NOT NULL with a constant default) and, from SQLite 3.35, drop a simple column. sqlite.minVersion in 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: create new_<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 !notx and wrap the rebuild in PRAGMA foreign_keys = OFF; BEGIN; ... COMMIT; PRAGMA foreign_keys = ON; (PRAGMA foreign_keys does nothing inside trek's transaction, TREK001).
  • Repeatables: there is no CREATE OR REPLACE; write DROP VIEW IF EXISTS v; then CREATE 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, ATTACH and PRAGMA journal_mode need !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.

CodeProviderMeaning and fix
TREK000SQL ServerThe script didn't parse, so lint was skipped. Check the syntax.
TREK001allA 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.
TREK002SQL ServerUSE switches databases. Remove it.
TREK003allBEGIN/COMMIT/ROLLBACK in a transactional change. Remove it.
TREK004SQL ServerSYSTEM_VERSIONING = OFF without turning it back on. Turn it back on in the same script.
TREK005PostgreSQL, SQLite, MySQL, MariaDBA 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.
TREK007PostgreSQLAn enum value added with ADD VALUE is used in the same change. Move that use to a later change.
TREK008SQLiteSQL 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.
TREK009MySQL, MariaDBUSE 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

CommandNeeds a databaseUse
trek plan check --jsonnoErrors and lint findings for the whole project
trek plan show --jsonnoEvery change with its dependencies, flags and tags
trek render <change> [--kind revert] -e <env>noThe SQL an environment will receive
trek ai-guidenoThis guide, matching the installed version
trek status -e <env> --jsonyesWhat 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.