What makes a good family-practice database

A family-practice database has to do something nominally simple: hold the clinic's patients, services, hours, reviews, and incoming bookings, with isolation between tenants. Most clinic-website databases — including the WordPress-and-MySQL setups behind a startling fraction of live dental sites — get the isolation part wrong. Tenant data sits in shared tables, separated only by application- level WHERE clauses, with no defense against a query that forgets the org_id filter. One bug ships, and a clinic in Sherwood is reading data from a clinic in Conway.

Dream Create's database layer enforces tenant isolation at the database, not the application. Every tenant table — organizations, services, bookings, audit_events, all sixteen of them — has FORCE ROW LEVEL SECURITY enabled with a policy keyed off the app.current_org_id Postgres GUC. Application code uses a scopedQuery helper that sets the GUC at the start of every transaction; if the GUC isn't set, RLS fails closed and the query returns zero rows. The application's explicit eq(table.organizationId, orgId) filters are defense-in-depth, not the primary line.

PHI columns get a second layer: AES-256-GCM column-level encryption with a key (PHI_ENCRYPTION_KEY) separate from the database connection. Patient names, phone numbers, reasons-for-visit are stored as bytea blobs that are unreadable without the application-side key. The database could be exfiltrated and the PHI would still be encrypted at rest. We treat the encryption key with the same care as a Stripe webhook signing secret: rotated on key compromise, never logged, never in a git history.

The audit table is the third layer — every PHI reveal, every clinic data mutation, every operator action gets an audit_events row with the actor, the timestamp, the action name, and a GCM-encrypted changes payload. The audit log itself is also tenant-scoped + encrypted; we can decrypt a specific clinic's audit history during a support investigation, but the encrypted payloads are opaque to anyone without the key.

The general-family vertical doesn't need anything more exotic than this. Pediatrics adds parent_or_guardian as another encrypted column; cosmetic adds the consent_records lattice; orthodontic adds virtual_consults. Each vertical reuses the same RLS, encryption, audit posture — the database layer scales with the platform without rewriting the foundation.