Migrations: `unique` on a `@tenant(root)` schema is scoped to `_tenant` and never enforced

[rrrodzilla]

Filing this as a question rather than a bug report, because the current behavior may be intentional and I could not find a test that pins it either way.

A unique field on a @tenant(root) schema gets a tenant-scoped unique index rather than a table-level constraint. On PostgreSQL the scoping column is _tenant, which on root-tenant rows is NULL, and Postgres treats NULLs as distinct — so the index never rejects anything and the field is effectively not unique at all.

Root-tenant rows are also the rows most likely to need global uniqueness: they are the tenants, so their identifying fields (short code, slug, domain) have to be unique across the whole table by definition. There is no outer tenant to scope them to.

Reproduce

@version(1)
@display("name")
@tenant(root)
schema Organization {
    name: text(max: 200) required unique
    short_code: text(max: 16) required unique
}

Apply, then as platform_admin:

POST /api/v1/forge/schemas/Organization/entities
{"fields": {"name": "Acme", "short_code": "ACME"}}   -> 201

POST /api/v1/forge/schemas/Organization/entities
{"fields": {"name": "Acme", "short_code": "ACME"}}   -> 201   (expected 409)

Two rows now exist with identical values in both unique columns.

The emitted DDL is:

CREATE UNIQUE INDEX IF NOT EXISTS "uniq_Organization_short_code"
  ON "Organization" ("_tenant", "short_code");

rather than the ALTER TABLE … ADD CONSTRAINT … UNIQUE ("short_code") a non-tenanted schema gets.

Where it comes from

On v0.37.2 (623d128):

crates/schema-forge-core/src/types/schema_definition.rs:86

matches!(a, Annotation::Tenant(_))

is_tenanted() matches on the presence of a Tenant annotation, not on its kind, so @tenant(root) and @tenant(parent: X) are indistinguishable to it.

crates/schema-forge-core/src/migration.rs:539 (and again at :635) feeds that straight through as the index scope:

let per_tenant = schema.is_tenanted();

and crates/schema-forge-postgres/src/codegen.rs:230 branches on it:

if per_tenant {
    format!("CREATE UNIQUE INDEX IF NOT EXISTS \"{constraint_name}\" ON \"{table}\" (\"_tenant\", \"{field}\");")
} else {
    format!("ALTER TABLE \"{table}\" ADD CONSTRAINT \"{constraint_name}\" UNIQUE (\"{field}\");")
}

The scoping column is empty on these rows because nothing fills it: crates/schema-forge-acton/src/access.rs:361 inject_tenant_on_create takes tenant_chain.last() from the caller's claims and has no root case. A platform_admin has an empty chain, so _tenant is NULL. A tenant-scoped caller creating a root-tenant row would get their own tenant id stamped there, which partitions the index by the creator rather than by anything meaningful — so the constraint is either inert or wrong depending on who writes.

There is a codegen test around codegen.rs:925-965 that builds a TenantKind::Root schema and asserts the per-tenant index form, but its assertions are about DDL ordering (the _tenant column must be added before an index referencing it — the #56 regression), so the Root/per-tenant pairing there reads as incidental to what it is checking rather than as a deliberate pin of this behavior. Happy to be told otherwise.

Question

Should is_tenanted() — or at least the per_tenant decision at migration.rs:539 — distinguish TenantKind::Root from TenantKind::Parent, and emit a table-level UNIQUE constraint for root-tenant schemas?

That is what "unique" means for a tenant-identifying field, and it would make the annotation enforce something on PostgreSQL rather than nothing. If instead the intent is that root-tenant rows carry a meaningful _tenant, then inject_tenant_on_create needs a root case, and the current NULL is the bug.

Either way it would be worth documenting, since the DSL gives no hint that unique behaves differently under @tenant(root), and the failure is silent — the migration applies cleanly and duplicates are simply accepted.

Workaround

Adding a plain SQL unique constraint by hand outside the migration works, but it is invisible to schemaforge migrate and a later RemoveUnique/AddUnique cycle may not account for it.

Environment

• SchemaForge v0.37.2 (623d128)
• schema-forge-postgres backend, PostgreSQL 16
• Not verified against the SurrealDB or MSSQL backends; DEFINE INDEX … UNIQUE may or may not have the same NULL semantics.