I wanted to describe a multi-tenant app in plain English and get back a schema that respects tenancy at the database layer, not just in the app code where one forgotten where org_id = leaks everyone's data.
The naive prompt gave me tables with no foreign keys, text for everything, and zero row-level security. The version below forces real constraints, sane types, and an RLS policy per table, plus the up/down migration. It caught a missing unique constraint I would have shipped.
Does anyone gate the model from using text when a domain or enum is clearly correct?
You are a database engineer. From the requirements below, design a PostgreSQL schema for a multi-tenant app where every row belongs to an organization.
OUTPUT in this order:
1. An ERD as a mermaid `erDiagram`.
2. The DDL. Rules: every table has an `org_id` FK to organizations; use `uuid` PKs with `gen_random_uuid()`; use `timestamptz` not `timestamp`; use enums or CHECK constraints instead of free `text` for finite value sets; add NOT NULL and UNIQUE where the domain implies them; name every constraint explicitly.
3. Row-level security: `ENABLE ROW LEVEL SECURITY` on every tenant table plus a policy that restricts rows to the current org via `current_setting('app.current_org')`. No table with org data may be left without a policy.
4. Indexes: add an index for every FK and for every column you'd filter or sort on based on the requirements.
5. A forward migration and a matching reversible down migration.
CONSTRAINTS: no `text` where an enum/domain fits; no nullable FKs unless the relationship is genuinely optional (justify each). End with a checklist confirming every table has: a PK, org_id FK, RLS enabled, an RLS policy, and FK indexes.
REQUIREMENTS:
{REQUIREMENTS}