Priority Key:
Critical
High
Medium
120+ items · 10 categories · PostgreSQL focusedBefore You Start
6 Database Mistakes That Cost You Later
These are the most common and expensive database design errors. Most are trivial to prevent upfront and catastrophic to fix in production.
Quick Reference
Data Type Selection Guide
Choose the right PostgreSQL data type for every common use case. The wrong type causes bugs that are invisible until they become production incidents.
🗂️
01Category
Schema Design & Naming Conventions
A well-named, consistently structured schema prevents confusion, reduces bugs, and makes onboarding new developers significantly faster. Establish these conventions before writing a single table.
14Items
All table names use snake_case plural nouns (e.g. users, project_briefs, invoice_line_items)Critical
All column names use snake_case (e.g. created_at, first_name, stripe_customer_id)Critical
Every table has a primary key named id — no exceptionsCritical
Every table has created_at (timestamptz, default now()) and updated_at (timestamptz) columnsCritical
Foreign key columns are named {referenced_table_singular}_id (e.g. user_id, project_id)Critical
Boolean columns use positive names (is_active, has_paid) — never double negatives (is_not_deleted)Critical
Enum columns use a consistent naming pattern — either a database enum type or a constrained text columnHigh
Tables for many-to-many relationships are named {table_a}_{table_b} in alphabetical order (e.g. project_tags)High
Column names are self-explanatory — avoid abbreviations (use description not desc, quantity not qty)High
Timestamps always use timestamptz (with timezone) not timestamp — prevents timezone bugsHigh
All tables and columns have inline SQL comments explaining their purpose if not obviousHigh
A schema README or ERD diagram exists and is kept up to dateMedium
Deleted records use soft delete via deleted_at timestamptz column — not permanent hard deletesMedium
Version or revision columns use integer type with default 1 and increment on updateMedium
6 critical · 5 high · 3 medium
🔢
02Category
Data Types & Constraints
Choosing the right data type for each column prevents entire classes of bugs. Constraints enforce business rules at the database level — the safest place to enforce them.
14Items
Primary keys use uuid (UUIDv4 or UUIDv7) for user-facing entities — not sequential integers that are enumerableCritical
Email columns use text with a CHECK constraint enforcing format — not varchar(255) with no validationCritical
Monetary values stored as integer (cents) or numeric(19,4) — never float or double precision (rounding errors)Critical
NOT NULL constraints applied to all columns that should never be nullCritical
CHECK constraints enforce valid ranges and business rules (e.g. quantity > 0, status IN ('active', 'inactive'))Critical
UNIQUE constraints applied to columns that must be unique (email, username, public_token)Critical
Text columns with fixed valid values use CHECK constraints or enum types — not free-form textHigh
URL columns validated with a CHECK constraint (starts with http:// or https://)High
Phone number columns stored as text — not integer (loses leading zeros and international formats)High
JSON/JSONB columns used only for genuinely flexible data — not as a substitute for proper relational designHigh
Array columns (PostgreSQL) used only for simple list data that does not need to be queried individuallyHigh
Default values defined for columns that should always have a value (e.g. status DEFAULT 'active', count DEFAULT 0)Medium
Binary data (files, images) stored in object storage (S3/Supabase Storage) with only the URL in the databaseMedium
Text columns have appropriate length limits via CHECK constraints where business rules applyMedium
6 critical · 5 high · 3 medium
🔗
03Category
Relationships & Referential Integrity
Foreign key constraints enforce relationships between tables at the database level. Without them, orphaned records accumulate silently and corrupt your data over time.
11Items
All foreign key columns have explicit FOREIGN KEY constraints — not just application-level enforcementCritical
ON DELETE behaviour explicitly defined for every foreign key (CASCADE, SET NULL, or RESTRICT)Critical
ON DELETE RESTRICT used for critical relationships where orphan records would be catastrophicCritical
ON DELETE CASCADE used only when child records are meaningless without the parent (e.g. deleting a user deletes their sessions)Critical
ON DELETE SET NULL used for optional relationships where the child record should survive (e.g. deleted author on a blog post)Critical
Many-to-many relationships use a dedicated junction table — not a JSON array in a columnHigh
Self-referential tables (e.g. category trees, comment threads) have parent_id nullable foreign key to same tableHigh
Circular foreign key dependencies explicitly documented and avoided where possibleHigh
All foreign key columns are indexed (PostgreSQL does NOT auto-index FK columns unlike MySQL)High
Referential integrity verified in staging environment by attempting to insert invalid foreign key valuesMedium
Relationship cardinality documented in ERD (one-to-one, one-to-many, many-to-many)Medium
5 critical · 4 high · 2 medium
⚡
04Category
Indexing Strategy
Indexes are the single biggest lever for database query performance. Missing indexes on frequently queried columns cause queries to do full table scans — unacceptable at scale.
14Items
Primary key indexes exist on all tables (automatic for most databases)Critical
All foreign key columns have a B-tree index (PostgreSQL requires manual creation — it does NOT auto-index FKs)Critical
Columns used in frequent WHERE clauses are indexedCritical
Columns used in ORDER BY on large tables are indexedCritical
UNIQUE indexes applied to all columns with uniqueness constraints (email, username, api_key)Critical
Composite indexes created for multi-column queries that always filter on the same combination of columnsHigh
Partial indexes used for filtered queries (e.g. CREATE INDEX ON proposals WHERE deleted_at IS NULL)High
GIN indexes used for full-text search columns (tsvector) or JSONB columnsHigh
Index names follow a consistent convention (idx_{table}_{column} or idx_{table}_{col1}_{col2})High
EXPLAIN ANALYZE run on all critical queries — no sequential scans on tables over 10k rowsHigh
Indexes reviewed for columns with very low cardinality (boolean columns rarely benefit from an index)Medium
Unused indexes identified and removed (they slow down INSERT/UPDATE operations for no gain)Medium
pgvector index (IVFFlat or HNSW) created on embedding columns if AI vector search is usedMedium
Index bloat monitored — REINDEX scheduled after high-volume batch operationsMedium
5 critical · 5 high · 4 medium
🧩
05Category
Normalisation & Data Integrity
Normalisation eliminates data duplication and update anomalies. Most production SaaS databases should aim for Third Normal Form (3NF) with pragmatic denormalisation only where performance requires it.
10Items
No repeating groups in a single column — data that could be multiple values lives in a related tableCritical
Each table represents exactly one entity — user data and order data are not in the same tableCritical
No transitive dependencies — columns depend only on the primary key, not on other non-key columnsCritical
Addresses stored in structured columns (street, city, state, country, postal_code) — not as a single text blobCritical
Calculated or derived values are computed at query time, not stored as columns (avoid stale cached values)High
Denormalisation is documented with a clear rationale and the update strategy that keeps denormalised copies freshHigh
Lookup/reference data (countries, currencies, categories) lives in its own table — not hardcoded in application codeHigh
User-generated content stored in dedicated content tables — not embedded in the users tableHigh
Reporting queries that require many joins are served by a materialised view or read replicaMedium
Audit/history tables duplicate key data intentionally for point-in-time accuracy — this denormalisation is acceptableMedium
4 critical · 4 high · 2 medium
🔒
06Category
Security & Access Control
Database security failures are catastrophic and irreversible. These controls must be in place before any user data enters the database.
14Items
Row-level security (RLS) enabled on all tables containing user data — enforced at database layer, not just applicationCritical
RLS policies tested by attempting to query another user's data using their JWT — access must be deniedCritical
Database connection strings stored in environment variables — never committed to version controlCritical
Application database user has minimum required privileges — not superuser or ownerCritical
Passwords never stored in plain text — use Supabase Auth, or bcrypt/argon2 with cost factor ≥ 12Critical
Sensitive data columns (PII, payment info) identified and documented in a data mapCritical
PII fields encrypted at rest where regulation requires (HIPAA, GDPR sensitive categories)Critical
Database connection uses SSL/TLS — unencrypted connections rejectedHigh
API keys and secrets stored as hashed values in the database — only the hash is stored, not the raw valueHigh
Audit log table records all data mutations to sensitive tables (who changed what and when)High
Database access limited to application server IPs via firewall rules — not publicly accessibleHigh
Separate read replica credentials used for analytics and reporting queriesHigh
Data retention policy defined — records older than X period archived or deleted per compliance requirementsMedium
Database user activity logging enabled for production — queries logged for security auditMedium
7 critical · 5 high · 2 medium
🚀
07Category
Performance & Query Optimisation
Performance problems that appear at 10,000 rows become critical at 1,000,000 rows. Design for scale from day one — it is far cheaper than a migration under load.
13Items
All queries touching tables over 10,000 rows reviewed with EXPLAIN ANALYZE — no Seq Scans without justificationCritical
N+1 query patterns eliminated — ORM batch loading (Supabase .select with joins) used where applicableCritical
Pagination implemented on all list endpoints — no endpoint returns unbounded result setsCritical
Connection pooling configured (PgBouncer or Supabase connection pooler) — not new connection per requestHigh
Database query response time P99 under 200ms for all critical queries at target loadHigh
Large batch operations use chunked processing — not a single query that locks the tableHigh
Slow query log enabled — queries over 100ms flagged for reviewHigh
Materialised views used for expensive aggregation queries that run frequentlyHigh
Database statistics updated regularly (ANALYZE) so query planner makes optimal decisionsHigh
Read-heavy tables on a read replica — analytical queries do not compete with write trafficMedium
Table partitioning applied to time-series tables over 100 million rows (e.g. events, logs)Medium
Vacuum settings tuned for high-update tables — autovacuum keeping dead tuple count lowMedium
Column statistics targets increased on high-cardinality columns used in complex query plansMedium
3 critical · 6 high · 4 medium
🔄
08Category
Migrations & Schema Changes
Database migrations are irreversible in production. Every schema change must be reviewed carefully, tested thoroughly, and deployed with a rollback strategy.
12Items
All schema changes are made via versioned migration files — never manual SQL in productionCritical
Migrations are idempotent — running them twice produces the same resultCritical
Every migration has a corresponding rollback/down migrationCritical
All migrations tested on a production data snapshot in staging before deploying to productionCritical
Column renames done in three stages: add new column → backfill data → drop old column (avoids downtime)Critical
Adding a new NOT NULL column done with a default value OR in two steps (add nullable → backfill → add NOT NULL constraint)High
Large table migrations (millions of rows) done with CONCURRENTLY where possible to avoid locksHigh
Migration run time estimated before deploying — operations over 30 seconds planned for maintenance windowHigh
Zero-downtime deployment strategy documented for schema changes that affect running application codeHigh
Migrations reviewed by a second developer before merging to main branchHigh
Migration history tracked in a migrations table — applied migrations recorded with timestampMedium
Database migration tool used consistently (Supabase CLI, Prisma Migrate, Flyway) — no ad hoc scriptsMedium
5 critical · 5 high · 2 medium
💾
09Category
Backups & Disaster Recovery
Backups are only useful if they work. Every item in this section must be verified through a real restore test — not just assumed to be working.
12Items
Automated daily full database backups configured with at minimum 7-day retentionCritical
Backup restore tested successfully in staging — a backup never tested is a backup you cannot trustCritical
Point-in-time recovery (PITR) enabled for production database — Supabase Pro and above provides thisCritical
Recovery Time Objective (RTO) defined — how long can the product be down? Maximum acceptable: 4 hoursCritical
Recovery Point Objective (RPO) defined — how much data can be lost? Maximum acceptable: 24 hoursCritical
Backups stored in a separate geographic region from the primary databaseHigh
Backup access is restricted — not accessible with the same credentials as the application databaseHigh
Disaster recovery runbook written and accessible to the team — step-by-step guide to restore from backupHigh
Backup success/failure monitored and alerting in place — failures reported within 1 hourHigh
Monthly restore drill conducted — team practices the recovery procedure to ensure it works and stays currentMedium
Database export in standard format (pg_dump) as an additional backup alongside managed backupsMedium
Backup encryption verified — backups encrypted at rest and in transitMedium
5 critical · 4 high · 3 medium
📈
10Category
Scalability & Future-Proofing
Design decisions made at 1,000 rows affect you at 100,000,000 rows. Build with growth in mind from the start — retrofitting scalability is expensive.
12Items
Schema designed to support 100× current data volume without structural changesCritical
Multi-tenant architecture uses a single database with RLS tenant isolation — not one database per tenant (until very large scale)Critical
Sharding strategy documented for tables expected to exceed 1 billion rowsHigh
Time-series data (events, logs, analytics) partitioned by date range from day oneHigh
Archive strategy defined for data older than a defined period — cold storage or archival tablesHigh
Database vertical scaling path documented — next instance size defined before it is urgently neededHigh
Read replicas provisioned or planned before read traffic exceeds primary database capacityHigh
Caching strategy documented for hot data — Redis or application-level cache for frequently read rowsHigh
Database connection limits understood — connection pooling required before 100 concurrent application processesMedium
Load test run against database with simulated peak traffic — bottlenecks identified before productionMedium
Database monitoring dashboards configured — CPU, memory, disk I/O, connection count, query performance all visibleMedium
Data growth projections calculated — estimated database size at 6 months, 12 months, and 24 monthsMedium
2 critical · 6 high · 4 medium
Built by 4Byte
How 4Byte designs production databases
This checklist is built from our experience designing and shipping databases for 45+ production SaaS products, web applications, and AI systems. Every item on this list comes from a real-world lesson — many of them learned the hard way on client projects.
We use PostgreSQL via Supabase as our default database for every project. Row-level security, proper indexing, UUID primary keys, and automated backups are set up from day one — not added later.
PostgreSQL via Supabase on every production build
RLS, indexes, and constraints configured before first user
pgvector for AI embeddings — no separate vector database needed
Migration-first workflow — no ad hoc SQL in production, ever
45+
Production Databases
Shipped and battle-tested
Zero
Data Breaches
RLS from day one, every time
99.9%
Uptime Target
With PITR backup on all builds
≤ 4hrs
Response Time
On all new enquiries
FAQ