Postgresql

PostgreSQL Best Practices

Comprehensive guidelines when working with PostgreSQL database to build Gram 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.
  • Do not use CHECK constraints for pure enumeration / value validation (e.g. CHECK (status IN ('active', 'inactive'))). Validate allowed values in application code, where they can evolve without a migration. Exception: keep CHECKs 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.

• 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.

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.

Change tracking

All tables should have created_at and updated_at columns:

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:

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:

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.

Gram uses Atlas 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.

• 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.

  -- 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.

• 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).