I refactored my database schema 4 times before getting it right. Each time cost me a week.
The first version used auto-increment IDs everywhere. The second didn't account for soft deletes. The third had audit logging bolted on as an afterthought. The fourth tried to retrofit consent versioning into a schema that assumed a single boolean for "agreed to terms."
Database schema is the one foundation you can't easily refactor once you have real data. Migrations exist, but migrating a million-row table with zero downtime while restructuring relationships is a different beast than prisma migrate dev on localhost.
Here are the six decisions I'd get right from Day 1.
1. UUIDs for Public IDs
Auto-increment IDs leak information. When a competitor signs up and sees /users/47, they know you have 47 users. When they see /api/payments/12, they know your payment volume. Worse, sequential IDs are trivially enumerable — an attacker can crawl /api/users/1 through /api/users/10000 in seconds.
UUIDs fix this entirely:
model User {
id String @id @default(uuid())
email String @unique
name String?
role Role @default(USER)
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Every model uses @default(uuid()). No sequential integer, no information leakage, no enumeration attacks. The performance cost on PostgreSQL is negligible with proper indexing — and the security benefit is immediate.
2. RBAC as a Role Hierarchy, Not Flat Strings
A role: string field with values like "editor", "manager", "admin" scattered across your codebase becomes unmaintainable fast. Every permission check turns into if (role === 'admin' || role === 'manager' || role === 'editor').
Use an enum with a clear hierarchy instead:
enum Role {
USER
ADMIN
SUPER_ADMIN
}
model User {
id String @id @default(uuid())
role Role @default(USER)
// ...
}
The middleware that enforces this is one function:
export const requireRole = (...roles: string[]) => {
return (req, res, next) => {
if (!req.user) return next(new UnauthorizedError('Authentication required'));
if (!roles.includes(req.user.role)) return next(new ForbiddenError('Insufficient permissions'));
next();
};
};
// ADMIN and SUPER_ADMIN can list users; only SUPER_ADMIN can delete
router.get('/admin/users', authenticate, requireRole('ADMIN', 'SUPER_ADMIN'), handler);
router.delete('/admin/users/:id', authenticate, requireRole('SUPER_ADMIN'), handler);
Because the enum is defined in Prisma, the database enforces it. No rogue "superadmin" or "Admin" strings sneaking in. Every role change gets audit-logged (more on that in a moment).
3. Soft Deletes With deletedAt
Hard-deleting a user row cascades through sessions, payments, notifications, audit logs — and if anything goes wrong mid-cascade, you're left with orphaned records and no way to recover.
Worse: GDPR requires you to prove deletion happened. If you DELETE FROM users WHERE id = ? and it's gone, you have no proof.
The deletedAt pattern gives you both safety and compliance:
model User {
id String @id @default(uuid())
email String @unique
isActive Boolean @default(true)
deletedAt DateTime?
anonymizedAt DateTime?
// ...
}
Soft delete sets deletedAt and isActive = false. For GDPR "right to erasure," you anonymize the PII (replace email with a hash, wipe name and phone) and set anonymizedAt. The record still exists for accounting and audit purposes, but the personal data is gone.
Hard delete is available when legally required — but it's a separate, deliberate action (DeletionType.HARD) with email confirmation, not the default.
4. Audit Logs as a Separate, Append-Only Table
Audit logging isn't a "nice to have." It's the first thing auditors ask for, the first thing you'll need when debugging a production incident, and the first thing enterprise procurement teams check.
It needs to be a dedicated table, not a column on other tables:
model AuditLog {
id String @id @default(uuid())
userId String?
action String // "USER_LOGIN", "PAYMENT_CREATED", "ROLE_CHANGED"
resource String? // "users", "payments", "mfa_methods"
resourceId String? // ID of affected resource
details Json? // Before/after state, additional context
ipAddress String?
userAgent String?
createdAt DateTime @default(now())
archived Boolean @default(false)
archivedAt DateTime?
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
@@index([userId])
@@index([action])
@@index([createdAt])
}
A few critical design decisions here:
-
onDelete: SetNull— if a user is deleted, their audit logs remain. You never cascade-delete audit records. -
details: Json?— stores before/after snapshots for state changes. When someone asks "what did this user's profile look like before the admin edited it?", you have the answer. -
archived+archivedAt— financial data needs 7-year retention in many jurisdictions. Archiving lets you move old logs to cold storage without deleting them. - Append-only by convention — the service only creates log entries, never updates or deletes them. Immutable audit trails are a compliance requirement.
Every meaningful action in the system — login, password change, MFA setup, role change, data export, OAuth linking — writes to this table with the user's IP and user agent.
5. Session Tracking With Device Context
Most tutorials store sessions as a token string and an expiry. That's not enough. When a user looks at "Active Sessions" in their security settings, they expect to see where they're logged in. And when you need to revoke sessions on password change, you need to know which ones exist.
model Session {
id String @id @default(uuid())
userId String
token String @unique
expiresAt DateTime
userAgent String?
ipAddress String?
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId])
@@index([token])
@@index([expiresAt])
}
userAgent and ipAddress are captured at login and stored alongside the session. This enables:
- "Active sessions" UI — show device/browser and location per session.
- Password change → revoke all — delete all sessions for this user, forcing re-authentication everywhere.
- Suspicious activity detection — flag when the same user logs in from two countries within an hour.
The @@index([expiresAt]) matters for cleanup: a scheduled job can efficiently delete expired sessions without a full table scan.
6. Consent Versioning
This is the one most SaaS templates get wrong — or skip entirely.
A single agreedToTerms: Boolean on the User model is legally meaningless. GDPR requires you to prove what the user consented to, which version of the policy, when, and from where.
That means two models: one for consent records, one for consent versions.
model ConsentRecord {
id String @id @default(uuid())
userId String
consentType ConsentType
granted Boolean @default(false)
grantedAt DateTime?
revokedAt DateTime?
ipAddress String?
userAgent String?
version String? // e.g. "2.1.0"
versionId String? // FK to ConsentVersion
expiresAt DateTime?
metadata Json?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
consentVersion ConsentVersion? @relation(fields: [versionId], references: [id])
@@unique([userId, consentType])
@@index([consentType])
}
model ConsentVersion {
id String @id @default(uuid())
consentType ConsentType
version String // Semantic version "1.0.0"
title String
content String @db.Text
summary String? @db.Text // Summary of changes
effectiveDate DateTime
expiryPeriod Int? // Days until consent expires
requiresReConsent Boolean @default(true)
changes String? @db.Text
isActive Boolean @default(false)
createdAt DateTime @default(now())
consentRecords ConsentRecord[]
@@unique([consentType, version])
}
enum ConsentType {
MARKETING_EMAILS
ANALYTICS
THIRD_PARTY_SHARING
COOKIES
TERMS_OF_SERVICE
PRIVACY_POLICY
}
Six consent types, each independently versioned. When you update your privacy policy (version 1.0 → 2.0), requiresReConsent: true triggers a prompt for every user who consented to v1. The ConsentRecord stores the exact version they agreed to, with IP and timestamp.
When a regulator asks "did this user consent to third-party data sharing?", you don't check a boolean. You pull the record: they consented to version 1.2.0 of the THIRD_PARTY_SHARING policy on January 15th at 14:32 UTC from IP 203.0.113.42. That's the difference between "we think so" and "here's the evidence."
The Controversial Opinion
Prisma migrations > raw SQL. I know, I know.
Raw SQL gives you fine-grained control. But when you're iterating fast, the DX difference is massive. prisma migrate dev generates the SQL, tracks the migration history, and handles rollbacks. You can always eject to raw SQL for complex migrations. But for the 90% case — adding a field, creating a table, adjusting an index — the productivity gain is real.
That said: always review the generated SQL before running it in production. prisma migrate deploy, not prisma migrate dev, in prod. And keep backups before every migration. Trust the tool, but verify the output.
What database decisions have burned you?
Top comments (0)