POSTGRESQL INTEGRITY
Database Standards
Protect tenant isolation, referential integrity, performance, and recoverability in PostgreSQL.
Production stability and security notice
Development changes must preserve working functionality, Provider isolation, role-based access, HIPAA auditability, released Mobile compatibility, and integration stability. Never deploy AI-generated or unreviewed code directly to production.
Core Rules
| Topic | Standard |
|---|---|
| Provider ownership | Provider-owned tables must contain an indexed providerId where applicable. |
| Primary keys | Use stable unique identifiers and never expose predictable identifiers as the only authorization control. |
| Foreign keys | Use relations that preserve ownership and prevent orphaned records. |
| Indexes | Index Provider scope, frequently filtered dates, statuses, and relationship keys. |
| Effective dates | Use explicit start and end dates for rates, authorizations, tax rules, and time-sensitive configuration. |
| Soft deletion | Prefer deactivation or archived state where history must remain available. |
| Transactions | Use transactions for related writes that must succeed or fail together. |
SQL Change Process
- Update schema.prisma.
Define the final model and relations.
- Write and run manual SQL.
Create or alter tables explicitly after review and backup.
- Run Prisma Generate.
Refresh the generated client after the database structure matches the schema.
No Prisma migrations
True Care System database changes follow schema.prisma + manually reviewed SQL + Prisma Generate. Do not run Prisma migration commands.