From erun-tools
Build a multi-tenant PostgreSQL database module following ERun's blueprint — row-level security, Atlas migrations, UUIDv7 surrogate keys, shared timestamp trigger, separate erun_tenant / erun_operations PostgreSQL roles, and the canonical tenant/issuer/user bootstrap that erun-backend-db captures. Use when the user says "build a multi-tenant postgres database", "create a tenant-scoped postgres schema with row-level security", "set up multi-tenant postgres migrations", "I need an erun-backend-db-shaped module", "build a multi-tenant rls db", or any similar request for a new tenant-scoped PostgreSQL project.
How this skill is triggered — by the user, by Claude, or both
Slash command
/erun-tools:erun-blueprint-rls-dbThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Produce a PostgreSQL database module following ERun's blueprint — the
templates/AGENTS.mdtemplates/atlas.hcltemplates/indexes/user_external_ids.sqltemplates/indexes/users.sqltemplates/rls/_tenant_table.sql.tmpltemplates/rls/context.sqltemplates/rls/user_external_ids.sqltemplates/rls/users.sqltemplates/roles.sqltemplates/tables/_tenant_table.sql.tmpltemplates/tables/tenant_issuers.sqltemplates/tables/tenants.sqltemplates/tables/user_external_ids.sqltemplates/tables/users.sqltemplates/triggers/_table_set_timestamps.sql.tmpltemplates/triggers/erun_set_timestamps.sqltemplates/triggers/tenant_issuers_set_timestamps.sqltemplates/triggers/tenants_set_timestamps.sqltemplates/triggers/user_external_ids_set_timestamps.sqltemplates/triggers/users_set_timestamps.sqlProduce a PostgreSQL database module following ERun's blueprint — the
same shape erun-backend-db captures: Atlas-managed declarative schema,
mandatory row-level security on every tenant-owned table, UUIDv7
surrogate primary keys, shared timestamp trigger, separate erun_tenant
/ erun_operations PostgreSQL roles, and the bootstrap tables
(tenants, tenant_issuers, users, user_external_ids) every
multi-tenant ERun-shaped database needs.
This skill packages ERun's accumulated best practices for multi-tenant Postgres. Do not freelance the patterns; the conventions encoded here are the contract.
Trigger on user phrasings such as:
Before producing files, gather:
acme-db, billing-db). Used as the directory
name. Default to <tenant>-db if a tenant identifier is in scope.uuidv7(). If the user is on PostgreSQL ≤ 17,
surface that and stop — uuidv7() is only native in PG 18+;
back-porting via an extension is a separate decision the user must
make.Do not invent these. Ask the user once, then proceed.
<module-name>/
├── AGENTS.md
├── atlas.hcl
├── schema/
│ ├── tables/
│ │ ├── tenants.sql
│ │ ├── tenant_issuers.sql
│ │ ├── users.sql
│ │ ├── user_external_ids.sql
│ │ └── <user-supplied tables>.sql
│ ├── indexes/
│ │ ├── tenant_issuers.sql
│ │ ├── users.sql
│ │ ├── user_external_ids.sql
│ │ └── <user-supplied tables>.sql
│ ├── triggers/
│ │ ├── erun_set_timestamps.sql
│ │ ├── tenants_set_timestamps.sql
│ │ ├── users_set_timestamps.sql
│ │ └── <user-supplied tables>_set_timestamps.sql
│ ├── rls/
│ │ ├── users.sql
│ │ ├── user_external_ids.sql
│ │ └── <user-supplied tables>.sql
│ ├── fks/
│ │ └── (cross-table foreign keys when clearer outside table files)
│ └── roles.sql
└── migrations/
└── default/
└── (Atlas generates here on first `atlas migrate diff`)
Reference files for the canonical blueprint ship alongside this
SKILL.md under templates/. Use them as the source of truth; do not
freelance the boilerplate.
These come from erun-backend/erun-backend-db/AGENTS.md. Apply every
one when producing files; do not relax them for convenience.
UUID columns with DEFAULT uuidv7().<entity>_id (e.g. tenants.tenant_id),
not generic id. Generic id is reserved for small private join
tables.tenant_id UUID NOT NULL DEFAULT erun_current_tenant_id() REFERENCES tenants(tenant_id).UNIQUE (tenant_id, name), not UNIQUE (name).tenant_id column appears even when another FK could imply the
tenant — tenant scoping must be directly visible in DDL, query
predicates, and indexes.created_at TIMESTAMPTZ and updated_at TIMESTAMPTZ on mutable
domain tables.BEFORE INSERT OR UPDATE triggers calling the shared
erun_set_timestamps() function. Trigger name:
<table>_set_timestamps.created_at and refresh updated_at.ENABLE ROW LEVEL SECURITY
and FORCE ROW LEVEL SECURITY (forced, so even the table owner is
bound).tenant_id = erun_current_tenant_id().
erun_current_tenant_id() returns the value of session setting
erun.tenant_id, or denies access when unset.erun_tenant (the normal
tenant-scoped role) and one for erun_operations (cross-tenant ops
access). Do not put an OR branch in the tenant policy.USING and WITH CHECK clauses, so reads, updates, deletes,
and inserts all enforce tenant boundary.erun_tenant — used by API/worker requests for normal tenant-scoped
access. RLS policies named <table>_tenant_policy.erun_operations — used by ops tooling for cross-tenant access. RLS
policies named <table>_operations_policy.GRANTed both, and switches via
SET LOCAL ROLE erun_tenant (or erun_operations) before
tenant-owned queries.SET LOCAL erun.tenant_id = '<uuid>' after
SET ROLE so erun_current_tenant_id() resolves.atlas.hcl lists schema source files in dependency order:
roles.sql → tables/ → indexes/ → triggers/ → rls/ → fks/.migrations/default/. Do not organize migrations
per table — one chronological stream per dialect.atlas migrate diff --env default after
editing the declarative schema; do not hand-write SQL into
migrations/default/ unless fixing a generator gap.Read back to the user: module name, target dir, tenant-owned table list, PG version. If anything is unclear, ask before producing.
mkdir -p "${target_dir}/${module}/schema"/{tables,indexes,triggers,rls,fks}
mkdir -p "${target_dir}/${module}/migrations/default"
Copy the reference files verbatim, substituting placeholders:
templates/atlas.hcl → ${module}/atlas.hcltemplates/roles.sql → ${module}/schema/roles.sqltemplates/triggers/erun_set_timestamps.sql → ${module}/schema/triggers/erun_set_timestamps.sqltemplates/tables/tenants.sql → ${module}/schema/tables/tenants.sqltemplates/tables/tenant_issuers.sql → ${module}/schema/tables/tenant_issuers.sqltemplates/tables/users.sql → ${module}/schema/tables/users.sqltemplates/tables/user_external_ids.sql → ${module}/schema/tables/user_external_ids.sqltemplates/indexes/tenant_issuers.sql → ${module}/schema/indexes/tenant_issuers.sqltemplates/indexes/users.sql → ${module}/schema/indexes/users.sqltemplates/indexes/user_external_ids.sql → ${module}/schema/indexes/user_external_ids.sqltemplates/triggers/tenants_set_timestamps.sql → ${module}/schema/triggers/tenants_set_timestamps.sqltemplates/triggers/users_set_timestamps.sql → ${module}/schema/triggers/users_set_timestamps.sqltemplates/rls/users.sql → ${module}/schema/rls/users.sqltemplates/rls/user_external_ids.sql → ${module}/schema/rls/user_external_ids.sqltemplates/AGENTS.md → ${module}/AGENTS.mdFor each table name T the user supplied:
${module}/schema/tables/T.sql — table definition. Use
templates/tables/_tenant_table.sql.tmpl as the starting shape;
replace __TABLE__ with T, add the user's domain columns, set
natural-key uniqueness as UNIQUE (tenant_id, …).${module}/schema/indexes/T.sql — secondary indexes (start empty if
the table only needs the implicit indexes from the unique
constraint).${module}/schema/triggers/T_set_timestamps.sql — copy
templates/triggers/_table_set_timestamps.sql.tmpl, replace
__TABLE__.${module}/schema/rls/T.sql — copy
templates/rls/_tenant_table.sql.tmpl, replace __TABLE__.atlas.hclEdit ${module}/atlas.hcl src = […] list to include the new files
in the canonical order: table → indexes → triggers → rls.
cd "${target_dir}/${module}"
atlas schema validate --env default
If atlas is not installed, surface the install hint:
https://release.ariga.io/atlas/atlas-linux-amd64-v1.2.0 (or use the
Homebrew tap).
To exercise the schema against a real database:
atlas migrate diff initial --env default
atlas migrate apply --env default --url "postgres://…"
The first command writes migrations/default/<timestamp>_initial.sql.
Commit it. Subsequent schema edits + atlas migrate diff produce
incremental migration files.
After the first deploy, the database has no tenants. The first
authenticated identity bootstraps the system by creating an
OPERATIONS tenant, mapping the token issuer to that tenant, creating
the first user, and assigning ReadAll + WriteAll roles. See
erun-backend-db/AGENTS.md § "Multi-Tenant Database Plan" for the
rule that this only happens once — after the first tenant exists,
unknown issuers and subjects must remain unauthorized until explicitly
configured.
| Failure mode | Recovery |
|---|---|
Target dir already contains an atlas.hcl | Stop. Offer --force (rewrite) or a new module name. Do not silently overwrite. |
| PostgreSQL < 18 detected | Stop. Explain uuidv7() requires PG 18+ natively; do not silently emit a custom UUIDv7 implementation. |
atlas binary not on PATH at validate time | Skip validate, surface install hint, continue. The produced files are valid even without local Atlas. |
User-supplied table name collides with a bootstrap table (tenants, tenant_issuers, users, user_external_ids) | Stop. The bootstrap names are reserved; ask the user to rename. |
| User-supplied table name is singular or PascalCase | Surface the convention (plural snake_case) and ask the user to confirm or rename. |
OR branch for ops access. That pattern is explicitly
forbidden in erun-backend-db/AGENTS.md § "Row-Level Security".tenants or tenant_issuers behind tenant-scoped RLS.
They are the tenant resolution root and need a different access
model — see the AGENTS.md note about security-definer functions for
issuer lookup.id for domain primary keys. Use explicit
<entity>_id names.npx claudepluginhub sophium/erun --plugin erun-toolsGuides creation, editing, and verification of skills for AI coding agents using test-driven development with subagent scenarios. Use when authoring or debugging skills.