Backend Dev

gistfile1.txt

INCLUDE:
- 1. PRD document
- 2. OpenAPI.json file
- 3. DB as SQL file
- 4. A mapping JSON file mapping openapi and sql
OUTPUT:
- 1. Modular production-level FastAPI, SQLAlchemy, Alembic code following best practice, and model/schema/route/crud folders with relevant files in them to build auth and backend app for "LinkDem" - A tool to help 1 - 10 member teams manage small 10 - 20 task events.
SPEC:
- Outcomes
- - Python FastAPI app implementing the attached PRD document, treating the OpenAPI and SQL as the primary source of truth with mapping JSON tying them together. 
- Constraints
- - Expose API with Verbatim adherence to OpenAPI (endpoints, schemas, status/error codes). The frontend is already built and expects the attached openapi.json exposed by this app
- - Apply the provided db.sql schema via Alembic migrations. Some or all tables might be already created so migrations generated should build what is not created
- - Map to DB using guidance from mapping.json with Supabase credentials provided below
- - Supabase Auth for authentication and RBAC/roles/levels/scopes stays in DB for authorization. Auth between frontend and backend happens via JWT Auth endpoints which handle the mapping internally; do not depend on custom role claims in JWT for role use DB.
- - Build /login to authenticate using Supabase Auth user pass given below (signup/login via Supabase, JWT validation from Supabase tokens)
- - Error handling must be explicit and high-quality including DB/RLS/constraint failures must be translated into the OpenAPI-defined error responses (no raw 500s).
- - Build /routes - endpoint definitions only (thin orchestration), /schemas - request/response DTOs (Pydantic v2), /models - ORM models mirroring DB tables, /crud - mappings between schemas ↔️ models + query primitives; no business orchestration, and /services - business logic + RBAC checks + DAG/task orchestration (uses crud).
- - Provide reproducible migrations (e.g., Alembic) compatible with Supabase Postgres.
- - DB Querries have a long round trip so group data using joins and instead of loop of single fetch combined data. Use eager loading / preloading / batched fetches; list endpoints must have bounded query counts (no per-row loops).
- - Any multi-table write must be atomic (single DB transaction); partial failures forbidden.
- - Supabase Configuration
- - - SUPABASE_URL - https://xxx.supabase.co
- - - SUPABASE_ANON_KEY - eyJ...zVk
- - - SUPABASE_SERVICE_ROLE_KEY - eyJ...Spo
- - - DATABASE_URL (PostgreSQL connection string) - pos...res
- - - Password - DBPass
- - - Supabase auth user - email
- - - Supabase user pass - pass
- Validations
- - Ensure code is layered routes/schemas/models/crud/services with clear responsibility boundaries.
- - Ensure eager loading/batching. Centralized policy deps. Reproducible migrations.
- - Ensure /docs expose the Swagger interface and the openapi.json form fastaopi matches the openapi.json we provided
- - Ensure the triggers are handled by Python code, like on task updated, introduce the resultant tasks if the prerequisite tasks are done or update audit tables on task update/state change.
- - ensure database init creates sample workflows and a global admin called user@example.com (already exists on Supabase) and goes ahead to test both work as well