Cursor rule

1---
2description: "Database rules for PostgreSQL 16, Alembic migrations, SQLAlchemy models, and Row Level Security."
3globs: "alembic/**,**/models.py,**/schemas.py,**/models/**"
4alwaysApply: false
5---
6 
7# Database and SQL Rules
8 
9These rules apply when working on database models, schemas, and migrations.
10 
11## Technology
12 
13- **Database**: PostgreSQL 16
14- **ORM**: SQLAlchemy 2.x
15- **Migrations**: Alembic
16- **RLS**: Row Level Security (MANDATORY)
17 
18## Row Level Security (RLS)
19 
20**CRITICAL**: RLS is mandatory for ALL tables containing user data.
21 
22### RLS Context Helpers
23 
24Every database operation MUST use the appropriate context helper:
25 
26| Helper                | Use Case                     |
27|-----------------------|------------------------------|
28| `with_user_context`   | User-scoped operations       |
29| `with_admin_context`  | Admin-scoped operations      |
30| `with_system_context` | Background/webhook operations|
31 
32### Zero Tolerance
33 
34- No direct session/ORM calls without RLS context wrappers
35- No bypassing RLS policies for convenience
36- No using superuser credentials in application code
37 
38### RLS Validation
39 
40After any schema change, validate RLS:
41- User isolation: User A cannot see User B's data
42- Admin access: Admin can see data within policy scope
43- System context: Background tasks use system context
44 
45## Migration Workflow
46 
47```bash
48# 1. Create migration
49alembic revision --autogenerate -m "description"
50 
51# 2. REVIEW the generated migration (do not blindly apply)
52# 3. Add RLS policies if new table created
53# 4. Test locally
54alembic upgrade head
55 
56# 5. Commit
57git add alembic/versions/
58git commit -m "feat(db): description [{{TICKET_PREFIX}}-XXX]"
59```
60 
61### Migration Rules
62 
63- Never use `DROP TABLE` without a backup strategy
64- Never skip Alembic (no manual DDL in production)
65- Always add proper indexes for query patterns
66- Add RLS policies for every new table with user data
67- Schema changes REQUIRE System Architect approval
68 
69## SQLAlchemy Model Standards
70 
71- Use SQLAlchemy 2.x `Mapped` type annotations
72- Define relationships explicitly
73- Include `created_at` and `updated_at` timestamps on all tables
74- Use UUIDs for primary keys
75 
76## Key References
77 
78- `docs/database/DATA_DICTIONARY.md` -- Schema reference (SINGLE SOURCE OF TRUTH)
79- `docs/database/RLS_DATABASE_MIGRATION_SOP.md` -- Migration SOP (MANDATORY reading)
80- `docs/database/RLS_IMPLEMENTATION_GUIDE.md` -- RLS implementation details
81- `patterns_library/database/rls-migration.md` -- RLS migration pattern
82- `patterns_library/database/prisma-transaction.md` -- Transaction pattern
83