Data Model Reviewer

Data Model Review — projects + tasks (FinTrack Platform)

Engine: Postgres 15 · Tenancy: Shared-schema multi-tenant · Status: Conditional approval

Findings Table

Critical 1 — Missing tenant_id on tasks

Every multi-tenant table must carry tenant_id directly. Relying on a join through projects to scope reads means a single missing WHERE clause leaks data across tenants. Postgres RLS policies also require a tenant column on the row itself to be enforceable.

ALTER TABLE tasks ADD COLUMN tenant_id uuid NOT NULL;
CREATE INDEX tasks_tenant_id_idx ON tasks (tenant_id);

-- Backfill from projects, then enforce
UPDATE tasks t SET tenant_id = p.tenant_id FROM projects p WHERE t.project_id = p.id;

-- Add a CHECK that tenant_id matches the parent project
ALTER TABLE tasks ADD CONSTRAINT tasks_tenant_matches_project
  CHECK (true) NOT VALID; -- enforced via app trigger or RLS

Critical 2 — Missing FK + cascade on tasks.project_id

tasks references projects.id by convention only. Deleting a project leaves orphan tasks; bulk-deleting tenants via cascade is impossible.

ALTER TABLE tasks
  ADD CONSTRAINT tasks_project_id_fkey
  FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

Normalize vs Denormalize — task.status

Prefer the CHECK constraint over a Postgres ENUM type. ENUMs are harder to reorder, harder to remove from, and harder to introspect from ORMs — the CHECK approach pays a tiny insert-time cost in exchange for far cheaper schema evolution.

ALTER TABLE tasks
  ADD CONSTRAINT tasks_status_allowed
  CHECK (status IN ('todo', 'in_progress', 'blocked', 'done', 'cancelled'));

Recommended Indexes

Audit / Soft Delete Pattern

Apply to both tables:

ALTER TABLE projects
  ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN deleted_at timestamptz;

-- Partial unique index so a deleted project's slug can be reused
CREATE UNIQUE INDEX projects_tenant_slug_active_idx
  ON projects (tenant_id, slug)
  WHERE deleted_at IS NULL;

Drop the proposed projects.archived boolean — deleted_at IS NOT NULL covers the same semantics with one less column and one less code path.

Migration Risk

Before / After Summary

Review against a hypothetical schema for illustration. Run the additive migration on a staging copy before scheduling production cutover.

This sample illustrates the skill's output format. Names, metrics, and operational details are illustrative unless the artifact explicitly analyzes public information.