satus.
§POST|~/blog||5 min read

Multi-column uniques, and why satus v0.x skips them

satus 0.3.11 reads only single-column UNIQUE constraints. It skips multi-column constraints and unique indexes, and a run that violates one is rolled back.

@passkeybridge/satus@0.3.11 reads exactly one kind of uniqueness from your schema: a UNIQUE constraint on a single column. A unique (workspace_id, email) is skipped, and so is any uniqueness declared with create unique index instead of a constraint. The README lists the first under "Honest limits (v0.x)"; the second is in a comment in the introspection query. This post shows both with the shipped CLI and the catalog, then what Postgres does when a seed collides with a rule satus did not read. Every transcript below ran on PostgreSQL 17.11 with psql 17.11, against @passkeybridge/satus@0.3.11, with no provider key in the environment.

The schema

Three tables, each declaring uniqueness a different way:

create table workspaces (
  id   integer generated always as identity primary key,
  name text not null
);

create table members (
  id           integer generated always as identity primary key,
  workspace_id integer not null references workspaces(id),
  email        text not null,
  role         text not null,
  constraint members_workspace_email_key unique (workspace_id, email)
);

create table api_keys (
  id     integer generated always as identity primary key,
  label  text not null
);
create unique index api_keys_label_lower_idx on api_keys (lower(label));
create unique index api_keys_label_idx on api_keys (label);

members has a two-column constraint that allows one membership per email per workspace. api_keys has two unique indexes and no constraint: one on lower(label), one on label itself.

What the dry run reports

$ satus generate --dry-run --rows 25

satus generate
  schema:   public
  profile:  saas
  provider: openai
  model:    gpt-4o-mini
  rows:     25 per table
  tables:   api_keys -> workspaces -> members

  api_keys                         25 rows  ~$0.0018
  workspaces                       25 rows  ~$0.0018
  members                          25 rows  ~$0.0036

  estimated cost: $0.0072

  simulating + validating...
  api_keys . (dry-run)
  workspaces . (dry-run)
  members . (dry-run)

  ✓ no validation findings across 3 tables

The dry run reports no findings, and it would report none whatever the simulated rows contained in those columns. The validator's coverage list in validate.ts names "Single-column UNIQUE duplicates within the batch" as checked and "Multi-column UNIQUE constraints" as out of scope for v0.3.x. It can only check uniqueness the introspection step found, and here it found none.

What introspection reads

This is the uniques query from introspect.ts, with the schema parameter filled in as 'public':

select
  cls.relname as table_name,
  att.attname as column_name
from pg_constraint con
join pg_class cls     on cls.oid = con.conrelid
join pg_namespace ns  on ns.oid = cls.relnamespace
join pg_attribute att on att.attrelid = cls.oid and att.attnum = con.conkey[1]
where con.contype = 'u'
  and cardinality(con.conkey) = 1
  and ns.nspname = 'public'
  and not cls.relispartition;

$ psql $DATABASE_URL -f uniques.sql
 table_name | column_name 
------------+-------------
(0 rows)

Two filters exclude them. contype = 'u' restricts it to unique constraints in pg_constraint, and a bare create unique index creates no row there. cardinality(con.conkey) = 1 drops every constraint over more than one column. The comment above the query in the source gives the reason for the second: "Multi-col uniques require coordinated generation across columns; we skip them in v0.x to keep the failure surface small."

Every unique index Postgres will enforce on these tables is in pg_index:

select c.relname as table_name,
       i.relname as index_name,
       x.indnkeyatts as key_columns,
       x.indexprs is not null as has_expression,
       con.conname is not null as is_constraint
from pg_index x
join pg_class i on i.oid = x.indexrelid
join pg_class c on c.oid = x.indrelid
join pg_namespace n on n.oid = c.relnamespace
left join pg_constraint con on con.conindid = x.indexrelid and con.contype = 'u'
where n.nspname = 'public' and x.indisunique and not x.indisprimary
order by 1, 2;

$ psql $DATABASE_URL -f all-uniques.sql
 table_name |         index_name          | key_columns | has_expression | is_constraint 
------------+-----------------------------+-------------+----------------+---------------
 api_keys   | api_keys_label_idx          |           1 | f              | f
 api_keys   | api_keys_label_lower_idx    |           1 | t              | f
 members    | members_workspace_email_key |           2 | f              | t
(3 rows)

Postgres enforces all three, and satus reads none of them. api_keys_label_idx covers one plain column, and it is skipped only because it was declared as an index. The same rule as a constraint is read:

$ psql $DATABASE_URL -c "alter table workspaces add constraint workspaces_name_key unique (name)"
ALTER TABLE
$ psql $DATABASE_URL -f uniques.sql
 table_name | column_name 
------------+-------------
 workspaces | name
(1 row)

A single-column unique index on a plain column can be turned into a constraint in place with unique using index, and satus 0.3.11 then reads it. An expression index cannot:

alter table api_keys add constraint api_keys_label_key unique using index api_keys_label_idx;
alter table api_keys add constraint api_keys_label_lower_key unique using index api_keys_label_lower_idx;

$ psql $DATABASE_URL -f convert.sql
psql:convert.sql:1: NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "api_keys_label_idx" to "api_keys_label_key"
ALTER TABLE
psql:convert.sql:2: ERROR:  index "api_keys_label_lower_idx" contains expressions
LINE 1: alter table api_keys add constraint api_keys_label_lower_key...
                                 ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.
$ psql $DATABASE_URL -f uniques.sql
 table_name | column_name 
------------+-------------
 workspaces | name
 api_keys   | label
(2 rows)

The index is renamed to match the constraint, which matters if a migration elsewhere refers to it by its old name. Case-insensitive uniqueness through lower(label) stays an index, and satus 0.3.11 does not read it.

What a collision does to a run

Postgres enforces every one of those indexes on insert, whoever wrote the row. satus writes a run in a single transaction, so a collision with a rule satus did not read fails the run and rolls back every row it wrote. The same sequence in plain SQL:

begin;
insert into workspaces (name) values ('acme'), ('globex');
insert into members (workspace_id, email, role)
select id, 'ana@example.com', 'member' from workspaces;
insert into members (workspace_id, email, role)
select id, 'ana@example.com', 'owner' from workspaces where name = 'acme';
rollback;
select count(*) from workspaces;

$ psql $DATABASE_URL -f dup.sql
BEGIN
INSERT 0 2
INSERT 0 2
psql:dup.sql:6: ERROR:  duplicate key value violates unique constraint "members_workspace_email_key"
DETAIL:  Key (workspace_id, email)=(1, ana@example.com) already exists.
ROLLBACK
 count 
-------
     0
(1 row)

The same email in two workspaces is allowed; the second membership in acme is not. After the rollback, both tables are empty. The run fails with an error and the database is left unchanged, so the run has to be started again. The environment these transcripts come from has no provider key, so this post contains no output from a real satus generate run.

NULL is distinct by default

A composite unique constraint with a nullable column accepts rows that differ only by a NULL. This is Postgres behavior and applies to any writer, satus included. By default Postgres treats two NULLs as distinct in a unique constraint, so these rows do not conflict:

create table invites (
  workspace_id integer not null references workspaces(id),
  email        text,
  constraint invites_workspace_email_key unique (workspace_id, email)
);
insert into workspaces (name) values ('initech');
insert into invites (workspace_id, email)
select id, null from workspaces, generate_series(1, 3);
select workspace_id, email, count(*) from invites group by 1, 2;
alter table invites drop constraint invites_workspace_email_key;
alter table invites add constraint invites_workspace_email_key
  unique nulls not distinct (workspace_id, email);

$ psql $DATABASE_URL -f nulls.sql
CREATE TABLE
INSERT 0 1
INSERT 0 3
 workspace_id | email | count 
--------------+-------+-------
            3 |       |     3
(1 row)

ALTER TABLE
psql:nulls.sql:12: ERROR:  could not create unique index "invites_workspace_email_key"
DETAIL:  Key (workspace_id, email)=(3, null) is duplicated.

Three invites for the same workspace with no email, all accepted. NULLS NOT DISTINCT, available since PostgreSQL 15, makes Postgres treat NULLs as equal for this constraint, and Postgres refuses to add it while the duplicates exist. The new workspace got id 3, not 1, because the rolled-back transaction above had already drawn 1 and 2 from the identity sequence, and sequence values are not returned on rollback.

What would change in satus

Multi-column unique constraints are item 2 of Phase 2 in the roadmap. They are not in 0.3.11, and neither is reading uniqueness from bare unique indexes. Until then, run the pg_index query above against your schema: every row it returns with is_constraint false or key_columns above 1 is a rule Postgres enforces and satus does not plan for.

Run the dry run against your own schema first. It needs no key and writes nothing, and the quickstart has the install line.

Sources

published 2026-10-30 · satus.sh · ·seeding ·postgres ·constraints ·unique
← all posts