DB Designer

gistfile1.txt

INCLUDE:
- 1. PRD document
- 2. OpenAPI.json file
OUTPUT:
- 1. db.sql file with DDL to run on Supabase. 
- 2. JSON mapping of Openapi (Schemas and endpoint parameters) to db.sql
SPEC:
- Outcomes
- - SQL File broken into 5 distinct groups (DDL statements, building if not exist enums, core tables, auth/RBAC tables, with soft delete mechanism, RLS enabled, policies for ALL authorized supabase users), Remember all logic stays on backend and not on DB. DB is only a storage layer 
- - Mapping JSON which maps ALL POSSIBLE from openapi.json attached Endpoint | parameter | param type | field | table, eg. /student | parent_name | body | student.parent.name | parent 
- Constraints
- - Produce a 3NF PostgreSQL schema in Supabase where OpenAPI is the de facto source of truth for data (use PRD only for intent) by decomposing OpenAPI resources into a normalized relational model
- - Enforce Supabase Auth/RLS, indirect RBAC, DAG integrity, soft-delete safety, and concurrency-safe unlock.
- - The DB schema must store and retrieve all OpenAPI fields/relationships without drift. If PRD conflicts with OpenAPI: align to OpenAPI and flag mismatch in audit.
- - Flattened OpenAPI resources must be split into atomic tables; references via FKs (no embedded duplicates beyond keys). Do not store computed values (e.g., progress %). Use views or runtime computation.
RLS Basis: Policies must rely on auth.uid() (and optionally auth.jwt()), but Admin/roles are DB-backed (do not rely on generic JWT role). profiles → user_types ↔️ permissions → task_types ← tasks
- - Regular access gated by event membership via a bridge table (e.g., event_memberships).
- - No repeated groups/transitive dependencies. No stored derived metrics (views allowed).
- Validations
- - Ensure all endpoints and related parameters can be written/read from the DB and the SQL created for DB is executed and runs successfully
- - Ensure 3NF by properly splitting the Flat endpoint structure
- - Ensure RBAC is stored in DB and does not depends on Supabase Auth
- - Ensure all outputs are double checked to map to openapi.json and the generated sql file