# SchemaLens — AI Coding Agent Skill

TRIGGER when: the project has a `.schemalens.yml` config file, `schema/` directory with SQL files, or the user mentions SchemaLens, database schema tracking, schema version control, schema pull, schema deploy, or schema diff.

## What is SchemaLens

SchemaLens is schema version control for PostgreSQL and Supabase. It tracks database state as plain SQL files in a `schema/` directory — like Git for your database structure. It captures tables, views, functions, policies, indexes, triggers, enums, and extensions.

Key concept: SchemaLens tracks **state** (what the schema looks like now), not **transitions** (how it got there). Schema files are the declarative source of truth.

## CLI Command Reference

All commands require `@schemalens/cli` to be installed (`npm install -g @schemalens/cli`). Check with `npx schemalens --version` before running any command.

```
schemalens init                                                # Interactive setup wizard — creates schema/_meta/.schemalens.yml
schemalens pull --env <env>                                    # Introspect live DB and write SQL files to schema/<env>/
schemalens status --env <env>                                  # Show diff between local files and live DB (read-only, no changes)
schemalens diff --from <env> --to <env>                        # Compare schema files between two environments
schemalens deploy --env <env>                                  # Dry-run: show planned SQL without executing
schemalens deploy --env <env> --confirm                        # Apply the deploy plan to the database
schemalens deploy --env <env> --confirm --allow-destructive    # Allow DROP operations (tables, columns, etc.)
schemalens deploy --env <env> --confirm --rename tbl.old:new   # Confirm a column rename instead of DROP+ADD
schemalens build --migrations <path>                           # Apply migration files to a blank DB and snapshot the resulting schema (no live DB needed)
schemalens build --migrations <path> --env <env>               # Write snapshot to schema/<env>/ (default: schema/local/)
schemalens build --migrations <path> --db <conn-string>        # Apply against a specific blank local database instead of in-memory pg-mem
schemalens ci --env <env>                                      # Non-interactive CI mode: pull + sync + check
schemalens ci --env <env> --block-destructive                  # CI mode: exit code 2 if destructive changes detected
schemalens init-action                                         # Generate a GitHub Actions workflow for CI checks
schemalens upgrade                                             # Upgrade the CLI to the latest version (auto-detects npm/pnpm/yarn/bun)
schemalens upgrade --dry-run                                   # Show what would be upgraded without installing
schemalens upgrade --pm <manager>                              # Force package manager (npm, pnpm, yarn, bun)
```

## Schema Directory Structure

After `schemalens pull`, files are organized under `schema/<env>/`:

```
schema/
  _meta/
    .schemalens.yml              # Configuration file
  <env>/                         # e.g. dev/, staging/, prod/
    extensions/enabled.sql       # CREATE EXTENSION statements
    enums/<name>.sql             # CREATE TYPE ... AS ENUM
    tables/<table>/
      definition.sql             # CREATE TABLE with columns and constraints
      indexes.sql                # CREATE INDEX statements (non-primary)
      policies.sql               # ALTER TABLE ENABLE RLS + CREATE POLICY
      triggers.sql               # CREATE TRIGGER statements
    views/<name>.sql             # CREATE OR REPLACE VIEW
    functions/<name>.sql         # CREATE OR REPLACE FUNCTION
```

Each file starts with a two-line header:
```sql
-- object: <type> . <schema>.<name>
-- version: auto . <timestamp>
```

The SQL body after line 2 is the actual DDL. These files are idempotent (use IF NOT EXISTS, OR REPLACE, etc.).

## Configuration

Configuration lives at `schema/_meta/.schemalens.yml`:

```yaml
version: 1
schema_dir: schema
allow_destructive: false              # When false, deploy blocks DROP operations

environments:
  dev:
    connection_string: env(DATABASE_URL)        # env() resolves environment variables
  staging:
    connection_string: env(DATABASE_URL_STAGING)
  prod: {}                                      # Empty = linked mode (fetches credentials from SaaS)

# Optional: connect to SchemaLens SaaS for dashboard + passwordless mode
project_id: <uuid>                              # From Project Settings in the dashboard
api_key: env(SCHEMALENS_API_KEY)                # Generated in Project Settings
```

Key points:
- `env(VAR_NAME)` resolves environment variables at runtime
- When `project_id` and `api_key` are set without `connection_string`, the CLI uses "linked mode" and fetches credentials from the SaaS securely
- `allow_destructive: false` is the default; DROP operations require the `--allow-destructive` flag

## Reading Schema Files for Context

When the user asks about their database structure, read files in `schema/<env>/` instead of requiring a live connection:

- **Table structure**: Read `schema/<env>/tables/<name>/definition.sql`
- **RLS policies**: Read `schema/<env>/tables/<name>/policies.sql`
- **Indexes**: Read `schema/<env>/tables/<name>/indexes.sql`
- **Triggers**: Read `schema/<env>/tables/<name>/triggers.sql`
- **Functions**: Read `schema/<env>/functions/<name>.sql`
- **Views**: Read `schema/<env>/views/<name>.sql`
- **Enums**: Read `schema/<env>/enums/<name>.sql`
- **Extensions**: Read `schema/<env>/extensions/enabled.sql`

To discover which environment directories exist, list `schema/` subdirectories (excluding `_meta`).

## Workflows

### First-time setup

1. Run `schemalens init` (interactive wizard)
2. Set required environment variables (e.g. `DATABASE_URL`)
3. Run `schemalens pull --env dev` to create the baseline snapshot
4. Commit the `schema/` directory to git

### After any database change (the daily loop)

1. Make the schema change (via SQL, Supabase Studio, migration file, etc.)
2. Run `schemalens pull --env dev` to capture the new state
3. Optionally run `schemalens status --env dev` to verify clean (0 diffs)
4. Commit schema files alongside migration files

### Adding or modifying a table via AI agent

1. Edit the SQL definition file at `schema/<env>/tables/<name>/definition.sql`
2. Run `schemalens deploy --env <env>` to see the dry-run plan
3. Show the plan to the user and wait for their approval
4. Only after approval: `schemalens deploy --env <env> --confirm`
5. Run `schemalens pull --env <env>` to capture the actual state (the DB may add defaults, constraints, etc.)

### Deploying to a remote environment

1. Always start with a dry run: `schemalens deploy --env staging`
2. Show the plan output to the user and wait for confirmation
3. Only after review: `schemalens deploy --env staging --confirm`
4. After successful deploy: `schemalens pull --env staging` and commit

### Building a schema snapshot from migration files (no live DB)

Use `schemalens build` when the source of truth is a directory of SQL migration files (e.g. Supabase `supabase/migrations/`, Prisma, raw `.sql` files) and you want a declarative state snapshot without touching a remote database.

1. Point at the migrations directory:
   ```bash
   schemalens build --migrations supabase/migrations
   ```
2. By default this applies migrations in lexicographic order to an in-memory `pg-mem` database, introspects the result, and writes files to `schema/local/`.
3. Override the target environment with `--env`:
   ```bash
   schemalens build --migrations supabase/migrations --env dev
   ```
4. To use a real blank local Postgres instead of `pg-mem` (e.g. when migrations use features `pg-mem` doesn't support), pass a connection string:
   ```bash
   schemalens build --migrations supabase/migrations --db postgres://localhost:5432/scratch
   ```
   The CLI also honors `DATABASE_URL` if `--db` is omitted.
5. Commit `schema/<env>/` and `schema/_meta/migrations.lock.json` (which tracks applied migrations) to git.

When to prefer `build` over `pull`:
- You don't have a live database to introspect (fresh clone, CI, offline dev)
- Migration files are the authoritative source and you want the snapshot to stay in lock-step with them
- You want deterministic, reproducible schema snapshots across machines

### Upgrading the CLI

Run `schemalens upgrade` to fetch the latest version from the npm registry and reinstall via the detected package manager (npm, pnpm, yarn, or bun). Use `--dry-run` first if you want to preview the command, and `--pm <manager>` to force a specific installer. If SchemaLens is installed locally in a project rather than globally, the command warns and suggests `npm update @schemalens/cli` inside the project instead.

### Comparing environments

```bash
schemalens diff --from staging --to prod
```

Output shows objects that are: only in source, only in target, different, or identical.

### Handling merge conflicts in schema files

Schema files are generated, not hand-edited. On conflict:
1. Accept either side of the conflict
2. Run `schemalens pull --env dev` to regenerate from the live database
3. Stage and commit

### Column rename workflow

When a column name changes, `deploy` detects it as a possible rename vs. DROP+ADD:
```bash
schemalens deploy --env staging --confirm --rename users.name:full_name
```

### Using with Supabase CLI

SchemaLens and Supabase CLI are complementary:
- **Supabase CLI** handles migration authoring (`supabase db diff`, `supabase migration new`, `supabase db push`)
- **SchemaLens** handles state tracking (`schemalens pull`, `schemalens status`, `schemalens deploy`)

Daily rhythm: `supabase db diff && supabase db reset && schemalens pull --env dev && git add && git commit`

## Safety Rules

**IMPORTANT: Follow these rules strictly when executing SchemaLens commands.**

- **NEVER** run `schemalens deploy --confirm` without first running the dry-run (`schemalens deploy --env <env>`) and showing the output to the user.
- **NEVER** pass `--allow-destructive` unless the user has explicitly acknowledged that DROP operations are acceptable for this specific deployment.
- **NEVER** commit files containing database credentials (`.env`, `deploy.log`, connection strings).
- **ALWAYS** run `schemalens pull --env <env>` after a successful deploy to capture the final state.
- **ALWAYS** dry-run before confirm. The two-step process is mandatory.
- **ALWAYS** add `schema/_meta/deploy.log` and `.env*` to `.gitignore`.
- **When deploying to production**, add extra caution: confirm the environment name with the user, show the diff against staging first if available, and require explicit approval before proceeding.

## Troubleshooting

| Error | Cause | Fix |
|-------|-------|-----|
| `Config not found at ...` | No `.schemalens.yml` in project | Run `schemalens init` |
| `Environment variable "..." is not set` | Missing env var referenced by `env()` | Set the variable in your shell or `.env` file |
| `Environment "..." not found. Available: ...` | Typo in `--env` value | Use one of the available environment names from config |
| `Destructive operations are not allowed` | `allow_destructive: false` and plan has DROP operations | Review the plan; use `--allow-destructive` only if intentional |
| `API key is invalid or expired` | SaaS auth failure | Regenerate the API key in the SchemaLens dashboard |
| `Could not reach SchemaLens SaaS` | Network/URL issue | Check internet connection; verify `SCHEMALENS_BASE_URL` if set |
| Connection refused / timeout | Wrong DATABASE_URL or DB not running | Verify connection string; ensure the database is accessible |