postgresql
/postgresql
Use when working with the Speakeasy PostgreSQL database: schema design, server/database/schema.sql, migrations, atlas.sum, sqlc queries, indexes, foreign keys, CHECK constraints, or reviewer suggestions to add constraints
- Author
- speakeasy-api ↗
- Repository
- gram ↗
- Licence
- AGPL-3.0
- Last repository change
- 27.10.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 1
No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.
AGPL-3.0, copyleft: przeróbki udostępniane innym muszą mieć tę samą licencję.
What it does and when to use it
Use when working with the Speakeasy PostgreSQL database: schema design, server/database/schema.sql, migrations, atlas.sum, sqlc queries, indexes, foreign keys, CHECK constraints, or reviewer suggestions to add constraints
Original author description
Use when working with the Speakeasy PostgreSQL database: schema design, server/database/schema.sql, migrations, atlas.sum, sqlc queries, indexes, foreign keys, CHECK constraints, or reviewer suggestions to add constraints
How it works
- When to Apply
- Rules
- Schema design rules
- Reviewing schema changes
- Database migrations
- Writing queries with SQLc
SKILL.md preview
---
name: postgresql
description: Use when working with the Speakeasy PostgreSQL database: schema design, server/database/schema.sql, migrations, atlas.sum, sqlc queries, indexes, foreign keys, CHECK constraints, or reviewer suggestions to add constraints
---
# PostgreSQL Best Practices
Comprehensive guidelines when working with PostgreSQL database to build Speakeasy which include rules for schema design, database migration and application logic. All rules are kept in a rules folder with names of each rule outlined below (e.g. `rules/<rule-name>.md`).
## When to Apply
Reference these guidelines when:
- Creating database migrations
- Writing queries used in application code especially with SQLc
- Updating existing database schemas
- Creating pull requests that involve database changes
## Rules
- **Code Formatting and Comments:**
- Maintain consistent code formatting using a tool like `pgformatter` or similar.
- Use clear and concise comments to explain complex logic and intentions. Update comments regularly to avoid confusion.
- Use inline comments sparingly; prefer block comments for detailed explanations.
- Write comments in plain, easy-to-follow English.
- Add a space after line comments (`-- a comment`); do not add a space for commented-out code (`--raise notice`).
- Keep comments up-to-date; incorrect comments are worse than no comments.
- **Naming Conventions:**
- Use `snake_case` for identifiers (e.g., `user_id`, `customer_name`).
- Use plural nouns for table names (e.g., `customers`, `products`).
- Use consistent naming conventions for functions, procedures, and triggers.
- Choose descriptive and meaningful names for all database objects.
- **Data Integrity and Data Types:**
- Use appropriate data types for columns to ensure data integrity (e.g., `INTEGER`, `VARCHAR`, `TIMESTAMP`).
- Use constraints (e.g., `NOT NULL`, `FOREIGN KEY`) to enforce data integrity but not for UNIQUE-ness — that is enforced with unique indexes.
- Keep `CHECK` constraints to simple single-column shape checks: a non-empty string (`CHECK (name <> '')`) or a length bound (`CHECK (char_length(slug) <= 64)`). Do not encode enums (`CHECK (status IN ('active', 'inactive'))`), numeric ranges, or rules that relate columns. Features are still being shaped, and every rule held in the schema turns each change into a migration that locks the table while it validates. Validate those rules in application code, where rows are built or parsed, and test them there. Decline review suggestions, including from review bots, to add or rely on such constraints, and do not describe their absence as a gap. **Exception:** keep `CHECK`s that are structural to the Class Table Inheritance (CTI) pattern, e.g. pinning a subtype's discriminator (`CHECK (provider = 'aws_kms')`) so the composite foreign key back to the supertype enforces 1:1 semantics.
- Define primary keys for all tables.
- Use foreign keys to establish relationships between tables.
- Utilize domains to enforce data type constraints reusable across multiple columns.
- All foreign keys constraints must ALWAYS specify an `ON DELETE SET NULL` clause.
- **Indexing:**
- Create indexes on columns frequently used in `WHERE` clauses and `JOIN` conditions.
- Avoid over-indexing, as it can slow down write operations.
- Consider using partial indexes for specific query patterns.
- Use appropriate index types (e.g., `B-tree`, `Hash`, `GIN`, `GiST`) based on the data and query requirements.
- When a unique key exists mainly to be a foreign-key target (e.g. a composite `(organization_id, id)` key that a tenant-scoped child composite-FKs to for tenancy pinning), declare it as a `CREATE UNIQUE INDEX`, not a table-level `UNIQUE` constraint. Postgres accepts a non-partial, plain-column unique index as an FK target. This matters when adding the key to an existing table: `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE` takes an `ACCESS EXCLUSIVE` lock and builds the index synchronously, whereas a `CREATE UNIQUE INDEX` (which Atlas automatically emits as `CONCURRENTLY` per its `concurrent_index` policy) does not. Trade-off: a concurrent index makes the migration non-transactional (Atlas emits `-- atlas:txmode none` for you), so keep such migrations minimal.
- Adding a `CHECK` or `FOREIGN KEY` constraint to an existing table takes an `ACCESS EXCLUSIVE` lock while Postgres scans every existing row, blocking reads and writes for the duration. Atlas reports this as lint PG305 (`CHECK`) or PG306 (`FOREIGN KEY`) but has no diff policy that avoids it, so the online-safe two-step form has to be hand-written: see the `NOT VALID` recipe under Database migrations.
- **Schema evolution:**
- Use expand-contract pattern instead of removing existing columns from a schema. Introduce new columns instead when appropriate.
- ALWAYS call out when making a backwards incompatible schema change.
- Suggest running `mise db:diff <migration-name>` after making schema changes to generate a migration file. Replace `<migration-name>` with a clear snake-case migration id such as `users-add-email-column`.
- If you need to undo a migration then run: 1. `mise run db:reset` 2. `mise run db:migrate` to re-run all migrations from the beginning.
<relevant-tasks>
- `mise run db:diff <name-of-migrations>`: Create a database migration
- `mise run db:reset`: Drop the database and re-create it. No migrations applied at this point.
- `mise run db:migrate`: Run all pending database migrations. If you have just reset the database, this will run all migrations from the beginning.
</relevant-tasks>
## Schema design rules
### Multi-tenancy by project
When creating any tables, add a non-nullable column named `project_id` of type `uuid` with a foreign key constraint to the `projects` table. If appropriate to the nature and usage patterns of the table also include `organization_id TEXT NOT NULL` column.
### Data classification
Every column must document what it holds with a comment whose last non-empty line is `@access: <class>`, where the class is one of `confidential` (internal IDs, timestamps, Speakeasy-controlled enums, counts, short labels), `confidential-pii` (emails, personal names, IPs, person IDs in external IdPs), `opaque-confidential` (short customer- or producer-chosen identifiers and labels support needs: tool, skill, agent, MCP server and client names, span and event names, producer session IDs, attribute keys), `secret-restricted` (credentials, tokens and their hashes, encrypted auth material), `restricted` (billing-system IDs, customer-configured endpoints, other known-sensitive or uncertain data) or `opaque-restricted` (arbitrary customer, user or model content: messages, prompts, tool payloads, customer JSON, free-form descriptions). The names follow the policy levels (confidential, restricted); `-pii`, `secret-` and `opaque-` narrow them. Descriptive prose may precede the marker:
```sql
COMMENT ON COLUMN chat_messages.content IS 'Raw message text.
@access: opaque-restricted';
```
Classify by reading the code that writes the column, because column names are often misleading, and choose the stricter class when unsure. The class records what the data is; consumers such as read-only SQL agents decide which classes they read. The server test suite in CI fails when a column lacks a valid marker (run `go test ./internal/dataclassification/...` from `server/` locally; needs Docker). Classes and parsing live in `server/internal/dataclassification`.
### Change tracking
All tables should have `created_at` and `updated_at` columns:
```sql
create table if not exists example (
-- ...
created_at timestamptz not null default clock_timestamp(),
updated_at timestamptz not null default clock_timestamp() on update clock_timestamp(),
-- ...
);
```
### Always soft delete
A nullable `deleted_at` column may be added to tables to perform soft deletes:
```sql
create table if not exists example (
-- ...
deleted_at timestamptz,
deleted boolean not null generated always as (deleted_at is not null) stored,
-- ...
);
```
Deleting rows with `DELETE FROM table` is not strongly discouraged. Instead,
use:
```sql
UPDATE example SET deleted_at = clock_timestamp() WHERE id = ?;
```
### File structure
`server/database/schema.sql` is DDL only — no `DO`, `ALTER`, or other procedural blocks. Declare tables in dependency order so every `FOREIGN KEY` resolves inline; if a target is declared later, move it up.
### Constraint naming
All constraints should be named with this format:
```
{tablename}_{columnname(s)}_{suffix}
```
Where suffix is:
- `key` for a unique constraint
- `fkey` for a foreign key constraint
- `idx` for any other kind of index
- `check` for a check constraint
- `excl` for an exclusion constraint
- `seq` for an sequences
## Reviewing schema changes
### Backwards compatibity
Ensure that all schema changes are designed for backwards compatibility.
These are examples of terrible practices to avoid:
- Adding a non-nullable column to an existing table.
- Removing a column from a table.
- Changing the data type of an existing column.
- Renaming an existing column.
- Changing the meaning or usage of an existing column.
- Adding unique constraints or indexes to existing columns without considering the impact on existing data and queries.
Instead, strongly consider these better alternatives:
- Adding nullable columns to existing tables.
- Deprecating columns by making them nullable.
- Using expand-contract pattern for evolving schemas without causing outages.
## Database migrations
These rules apply any time you touch `server/migrations/`, `atlas.sum`, or `server/database/schema.sql`. They are non-negotiable.
Speakeasy uses [Atlas](https://atlasgo.io) in versioned mode. Two file kinds are involved, and they are not the same thing:
- `server/database/schema.sql` is the **SDL** — the declarative, desired-state schema. This is the file you edit.
- `server/migrations/*.sql` are the **DDL** diff Atlas generates from that schema. Running `mise db:diff <name>` computes the delta (e.g. `ALTER TABLE ... ADD COLUMN ...`), writes a new timestamped migration file, and updates `atlas.sum`.
Rules:
- **Migrations ship in their own PR.** No application/business-logic code, no backfills, no unrelated changes alongside. Shipping migrations with business logic risks outages — the server can query a schema that has not rolled out yet — and makes the PR hard to revert.
- **Migration files and `atlas.sum` are produced only by the Atlas CLI (`mise run db:diff`).** Never hand-edit, rename, or rehash them. There is exactly one sanctioned exception: the `NOT VALID` constraint pattern in the last rule below.
- **Migration files contain only DDL — never DML.** Backfills and other data manipulation (`INSERT` / `UPDATE` / `DELETE`) do not belong in a migration file. Data migrations live in application code, not migrations.
- **Follow expand-contract.** Never drop a column or table in the same migration that adds others. If a column is unwanted, mark it nullable with a comment and leave it for a later contract migration; sticking around for a few days is fine.
- **Dropping a column that `SELECT *` or `RETURNING *` reads.** sqlc expands `*` when it generates code, so every pod built before the drop names the column and fails once it is gone. In the code PR that stops using the column, add `ALTER TABLE <table> DROP COLUMN <column>;` to `server/database/sqlc_retired_columns.sql` (create it if missing) and list that file after `schema.sql` in the `schema:` of each `sqlc.yaml` package that queries the table. The regenerated code then never names the column. Deploy that PR before the drop migration, and do not ship the drop until no image built before that PR is still a rollback target. Remove the statement (and the file and `sqlc.yaml` entries once empty) in the drop PR.
- **Never run agents (or any tooling) against dev or prod databases.** Local databases only.
- **Out-of-order timestamps:** if `mise lint:migrations` (or CI) reports a migration timestamp at or before the latest on `main`, do NOT rename the file. Delete the offending migration on your branch, rebase/merge `main`, then re-run `mise db:diff <name>` so the migration is regenerated on top with a fresh timestamp.
- **Migration merge conflicts:** never resolve them by hand. Delete your migrations, rebase/merge `main`, then re-run `mise db:diff` so your changes are recreated on top.
- **Adding a `CHECK` or `FOREIGN KEY` constraint to an existing table** triggers Atlas lint PG305 / PG306: the plain `ADD CONSTRAINT` scans the whole table under an `ACCESS EXCLUSIVE` lock, blocking reads and writes for the duration of the scan. Both are warnings and do not fail CI. Whether to avoid the scan is a question of table size and nothing else. A constraint over a column added in the same migration still triggers the same scan, it just cannot fail (unless that column has a `DEFAULT`, in which case existing rows are checked against the default value). For small or empty tables, accept the warning and say so in the PR description. For large or hot tables, use the two-step `NOT VALID` pattern:
1. Edit `server/database/schema.sql` and run `mise run db:diff <name>` as usual.
2. Append `NOT VALID` to each generated `ADD CONSTRAINT` clause in place, leaving Atlas's combined `ALTER TABLE` intact, then add one `ALTER TABLE ... VALIDATE CONSTRAINT ...;` statement per constraint after it. Do not break the generated `ALTER TABLE` into separate statements: each statement takes its own `ACCESS EXCLUSIVE` lock, and splitting a paired `DROP CONSTRAINT` / `ADD CONSTRAINT` opens a window where the table has no constraint at all.
3. Make `-- atlas:txmode none` the first line of the file. The two statements must not share a transaction: inside one transaction the `ACCESS EXCLUSIVE` lock taken by the `ADD` is held until commit, so the validation scan runs under the full lock anyway and the split gains nothing. `mise lint:migrations` enforces this directive.
4. Run `mise run db:hash` to re-hash `atlas.sum`. Until you do, every Atlas command fails on a checksum mismatch.
```sql
-- atlas:txmode none
-- Atlas generates:
-- ALTER TABLE "t" ADD CONSTRAINT "t_x_check" CHECK (...), ADD COLUMN "y" text NULL;
ALTER TABLE "t" ADD CONSTRAINT "t_x_check" CHECK (...) NOT VALID, ADD COLUMN "y" text NULL;
ALTER TABLE "t" VALIDATE CONSTRAINT "t_x_check";
```
The end state is identical to the one-step form, so later `mise run db:diff` runs see no drift (CI verifies this). `VALIDATE CONSTRAINT` scans under `SHARE UPDATE EXCLUSIVE`, which allows concurrent reads and writes.
Three things the pattern does not buy you:
- **`NOT VALID` does not mean unenforced.** It skips the scan of existing rows only. Every subsequent `INSERT` and `UPDATE` is checked immediately, including updates to rows that already violate the constraint. Because migrations ship ahead of application code, only add a constraint that currently deployed code already satisfies.
- **A failed validation is expensive to recover from.** Production applies migrations through the Atlas Operator, and agents may never touch dev or prod databases, so a failed `VALIDATE` blocks every later migration until someone repairs the data by hand. Confirm there are no violating rows before shipping instead of planning to fix them afterwards.
- **Regenerating the migration silently reverts the edit.** The out-of-order and merge-conflict rules above both regenerate the file, which brings back the plain one-step form. Re-apply steps 2 through 4 every time you regenerate.
A constraint declared inside a brand-new `CREATE TABLE` does not trigger PG305 / PG306 and needs no change.
## Writing queries with SQLc
All SQLc queries live in `**/queries.sql` files in the codebase. This is an important convention to maintain.
When writing SQLc queries, follow these guidelines:
- Use descriptive names for queries and parameters.
- Write clear and efficient SQL queries that follow best practices for performance and readability.
- Consume the corresponding database schema to understand what tables, columns, relationships and indexes exist.
- CRITICAL: No matter the query, it MUST ALWAYS be scoped to a `project_id` to explicitly limit the scope of writes.
<relevant-tasks>
- `mise run infra:start`: bring up the local Postgres/ClickHouse/etc containers — required before running sqlc, since sqlc connects to the database to type-check queries.
- `mise run gen:sqlc-server`: generates Go code from SQLc queries (requires the local database from `mise run infra:start`).
</relevant-tasks>
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · speakeasy-api/gram | 100 | AGPL-3.0 |
| TerminalSkills/skills | 100 | Apache-2.0 |
| iliaal/whetstone | 100 | MIT |
| itechmeat/llm-code | 100 | MIT |
| claude-dev-suite/claude-dev-suite | 100 | MIT |
| yvssdheeraj/opencode-engineering-harness | 100 | MIT |
| G1Joshi/Agent-Skills | 100 | MIT |
| bcastelino/agent-skills-kit | 100 | MIT |
| MARUCIE/openclaw-foundry | 100 | MIT |
Database snapshot: 13.11.2026 · package version da49243db9ad (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 95database-fundamentals
Reviews schema design, SQL queries, ORM patterns. Use when junior creates schema, writes queries, adds migrations, works with Prisma/MongoDB/PostgreSQL, or asks "is this SQL safe", "N+1", "index". - 100db-migration
Creates goose SQL migrations and sqlc queries from the entity model, then regenerates the type-safe Go database code. Use when the user asks to "create a migration", "update the schema", "set up database tables", "write a goose migration", "add sqlc queries", or mentions schema… - 100database-schema
Implement and review Cursor BYOK SQLite schema changes. Use when adding or changing tables, columns, indexes, constraints, foreign keys, SQLx migrations, persistence mappings, or database fixtures under server. - 100designing-database-schemas
Use when designing or changing a relational database schema (PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, CockroachDB) - new tables, columns, keys, foreign keys, constraints or indexes, a many-to-many or membership table, money or time columns, or a migration that alters… - 100database-design
Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries. - 87postgres-database-migration
Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns…