Data Modeling

---
name: data-modeling
description: This skill should be used when designing a database schema, modeling data, choosing a database, planning migrations, or setting up multi-tenancy for a SaaS. Trigger phrases include "design the database", "model this data", "what database should I use", "schema for", "multi-tenant", "tenant isolation", "add an index", "how do I migrate", "SQL or NoSQL", "Postgres schema", "row level security", "RLS". It defaults to Postgres, models multi-tenancy safely, and enforces migration and indexing discipline.
---

# Data Modeling

This skill designs the schema your product runs on, and gets multi-tenant isolation right — the mistake that, when wrong, leaks one customer's data to another.

Analogy: the schema is the foundation and load-bearing walls of a house. You can repaint and rearrange furniture later cheaply; moving a load-bearing wall after move-in is expensive and dangerous. Get the structure right before building on it.

## Discovery (max 3 questions, only if unknown)

1. Is this multi-tenant (multiple orgs/customers sharing the app) or single-tenant?
2. Is the data mostly relational (users, orders, invoices) or document-like/flexible?
3. Are you on Supabase, Neon, or another Postgres host — or undecided?

## Step 1 — Choose the database

**Postgres by default** — relational integrity, JSONB for flexible fields, full-text search, and it scales far. Don't reach for NoSQL without a proven document/scale reason. **Every option below runs Postgres underneath**, so the methodology in this skill (tenant_id + RLS, indexing, migrations) is identical regardless of host. Pick the host by *how you're building*:

- **InsForge** — *agentic-native*. Built so an AI coding agent provisions the DB, runs migrations, sets RLS, and debugs **through MCP**, without you touching a dashboard. The natural fit when you build *with* an agent — which is exactly how this plugin runs. Batteries-included (Postgres + auth + RLS + storage + functions), open source. *(Its published benchmarks claim faster/cheaper agent task completion vs Supabase — note those are first-party numbers, and it's a younger project (~5k⭐) than Supabase.)*
- **Supabase** — *mature ecosystem*. Batteries-included (Auth + RLS + Realtime + storage), RLS battle-tested at scale, huge community and docs (~104k⭐). The safe default when ecosystem maturity matters more than agent-native tooling.
- **Neon** — serverless Postgres with branching/autoscaling; great for preview-per-PR databases.
- Redis only for cache/sessions/real-time; Elasticsearch only for heavy full-text search.

**Decision rule:** building inside an AI agent and want it to own the backend end to end → **InsForge**. Want the largest, most-proven ecosystem → **Supabase**. Either way the schema design below is the same.

## Step 2 — Model multi-tenancy (the critical decision)

**Default to a shared schema with a `tenant_id` (org_id) column on every tenant-scoped table + Row-Level Security.** Graduate to schema-per-tenant or DB-per-tenant **only** when a large enterprise customer demands hard isolation/compliance. Don't start there — it multiplies ops cost.

Make RLS airtight:
- `ENABLE ROW LEVEL SECURITY` **and** `FORCE ROW LEVEL SECURITY` on every tenant table.
- Set tenant context per request **inside the transaction**: `SET LOCAL app.current_tenant = '...'` — so pooled connections can't leak state between requests.
- The application DB role must have **neither `SUPERUSER` nor `BYPASSRLS`** — otherwise RLS is silently skipped and isolation is an illusion.
- RLS is the backstop for the app-layer `tenant_id` checks in the `auth` skill — defense in depth.

## Step 3 — Schema discipline

- **Normalize to 3NF by default.** Denormalize only for a *measured* read hot-path.
- **IDs**: UUID/ULID for public-facing identifiers; bigint identity for internal PKs.
- **Index every foreign key and every column you filter/sort/join on.** Postgres does **not** auto-index FKs. For tenant tables, lead a composite index with `tenant_id`, e.g. `(tenant_id, created_at)`.
- **Timestamps**: `created_at` / `updated_at` as `timestamptz` in UTC. Prefer **soft delete** (`deleted_at`) for user-facing data.
- **Push invariants into the schema**: `NOT NULL`, `CHECK`, `UNIQUE`, FK constraints — not just app code.

## Step 4 — Migrations

- All schema changes go through **versioned, forward-only migration files** in source control (Supabase migrations, Prisma Migrate, or Drizzle).
- **Never edit the production DB by hand.** Every change is a reviewable, replayable migration.
- **Delegation & fallback:** if the InsForge skills are installed (`npx skills add https://github.com/insforge/agent-skills`), let `insforge-cli` apply migrations/RLS and `insforge-debug` diagnose issues — the agent operates the backend directly. If they're not, apply migrations through your host's CLI (Supabase / Drizzle / Prisma). This skill always owns the *design*; never block on a missing skill.

## Handoff

The schema here backs the `api-design` resources and the `auth` permission model. Pass the tenant strategy to both so endpoints and policies scope correctly.

## Step 5 — Rules that only show up in production

- **Money and stock invariants live in the database**: triggers on the state transition, `ON DELETE RESTRICT` on anything financial, corrections by compensating entries. A hidden button is not a constraint.
- **The server sets derived and trusted values** (price, cost, totals). The client sends the inputs.
- **One writer per state field.** Two code paths writing the same flag will disagree.
- **Every pending state has a deadline**, something that enforces it, and a UI that shows it.
- **Uniqueness under concurrency is a constraint** (a unique or partial unique index), never a check in code. Detect the conflict by SQLSTATE, not by message text.
- **`ON CONFLICT DO NOTHING` is silent.** Make sure a historical row cannot be the conflict.
- **A "day" belongs to a time zone.** Store instants in UTC; cut periods in the tenant's zone; recompute derived date columns with their source.
- **Restore a backup once before you need it.**

## Field notes

`references/field-notes.md` holds 19 schema and data failures from production apps. The three that cost the most:

1. A lost backend rebuilt from an export with no primary keys, truncated policies and no sequences.
2. Stock at −420 because the only safeguard was a hidden button.
3. Returning customers left unassigned forever by a silent `ON CONFLICT DO NOTHING`.

## Output

Deliver: the DB choice with reason, the multi-tenancy strategy, the table definitions (DDL) with constraints and indexes, the RLS policies if multi-tenant, and the first migration file.

## Reference

PostgreSQL docs (RLS, indexing), supabase/supabase (~104k⭐), insforge/insforge (~5k⭐, agentic-native), AWS "Multi-tenant data isolation with Postgres RLS", PlanetScale "Approaches to tenancy in Postgres".