Database PostgreSQL Schema Design Backend

Database Schema Design: Lessons from Production Systems

Practical patterns for designing maintainable, scalable PostgreSQL schemas for SaaS applications with real-world examples.

January 5, 202510 min read·Talha Bilal

Database Schema Design: Lessons from Production Systems

Introduction

Your database schema is the foundation of your application. Get it wrong and you'll spend months refactoring. Get it right and your codebase stays clean and your queries stay fast as you scale.

Over the years, I've designed schemas for various SaaS applications—ticketing systems, HR platforms, learning management systems. In this post, I'll share the patterns, anti-patterns, and pragmatic decisions that have served me well.

Core Principles

Before diving into specific patterns, here are the principles I follow:

Normalize first, denormalize for performance - Start with proper normalization, denormalize only when needed
Use meaningful naming conventions - Clear names prevent bugs
Think in terms of domains - Group related tables logically
Plan for multi-tenancy from day one - Retrofitting is painful
Use constraints liberally - The database should enforce data integrity

Naming Conventions

Consistency matters more than the specific style you choose. Here's what I use:

Table Names

sql

1-- Use plural, lowercase with underscores
2users
3organizations
4ticket_comments
5payment_methods
6
7-- NOT: User, TicketComment, paymentMethod

Column Names

sql

1-- Descriptive, lowercase with underscores
2id              -- Primary key
3user_id         -- Foreign key (table_name + _id)
4created_at      -- Timestamp columns end with _at
5is_active       -- Boolean columns start with is_/has_
6email_address   -- Be specific, not just "email"

main-content

sql

1-- Pattern: fk_childtable_parenttable
2-- Example:
3CONSTRAINT fk_tickets_users FOREIGN KEY (assignee_id) REFERENCES users(id)

Primary Keys: UUID vs. Auto-Increment

This is a hot debate. Here's my take:

Use UUIDs when:

Building a distributed system
Exposing IDs in URLs (don't leak sequence information)
Merging data from multiple sources
Multi-tenant system where ID collisions are a concern

Use Auto-Increment (Serial/BigSerial) when:

Single database system
Internal IDs only (never exposed to users)
You need the slight performance benefit
Debugging with sequential IDs is valuable

My default: UUIDs

sql

1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
3CREATE TABLE users (
4  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
5  email VARCHAR(255) NOT NULL UNIQUE,
6  name VARCHAR(255) NOT NULL,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
9);

Why? Better for distributed systems, no information leakage, easier to merge datasets.

Timestamps: The Essential Pattern

Every table should track creation and modification:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  
4  -- ... other columns ...
5  
6  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
7  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8  deleted_at TIMESTAMPTZ -- For soft deletes
9);
10
11-- Trigger to auto-update updated_at
12CREATE OR REPLACE FUNCTION update_updated_at_column()
13RETURNS TRIGGER AS $$
14BEGIN
15  NEW.updated_at = NOW();
16  RETURN NEW;
17END;
18$$ LANGUAGE plpgsql;
19
20CREATE TRIGGER update_tickets_updated_at
21  BEFORE UPDATE ON tickets
22  FOR EACH ROW
23  EXECUTE FUNCTION update_updated_at_column();

Pro tip: Use TIMESTAMPTZ (timestamp with timezone), not TIMESTAMP. Always store in UTC.

Multi-Tenancy: The Shared Schema Approach

For B2B SaaS, add tenant_id to every table:

sql

1CREATE TABLE organizations (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  name VARCHAR(255) NOT NULL,
4  slug VARCHAR(100) NOT NULL UNIQUE,
5  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
6);
7
8CREATE TABLE users (
9  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
10  organization_id UUID NOT NULL REFERENCES organizations(id),
11  email VARCHAR(255) NOT NULL,
12  name VARCHAR(255) NOT NULL,
13  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
14  
15  -- Email unique per organization, not globally
16  UNIQUE(organization_id, email)
17);
18
19CREATE TABLE tickets (
20  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
21  organization_id UUID NOT NULL REFERENCES organizations(id),
22  title VARCHAR(500) NOT NULL,
23  description TEXT,
24  status VARCHAR(50) NOT NULL DEFAULT 'open',
25  assignee_id UUID REFERENCES users(id),
26  created_by_id UUID NOT NULL REFERENCES users(id),
27  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
28  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
29);
30
31-- Critical: Index for tenant queries
32CREATE INDEX idx_tickets_organization_id ON tickets(organization_id);
33CREATE INDEX idx_users_organization_id ON users(organization_id);

Enforce tenant isolation with Row-Level Security:

sql

1ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
2
3CREATE POLICY tenant_isolation ON tickets
4  USING (organization_id = current_setting('app.current_tenant_id')::UUID);
5
6-- Set in application code before queries:
7-- SET LOCAL app.current_tenant_id = 'tenant-uuid-here';

Enum vs. Reference Tables

When should you use an enum vs. a lookup table?

Use ENUMs for:

Small, fixed sets of values that rarely change
Application-defined states (status, priority)

sql

1CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');
2
3CREATE TABLE tickets (
4  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
5  status ticket_status NOT NULL DEFAULT 'open',
6  -- ...
7);

Use Reference Tables for:

User-defined categories
Values that change over time
Values that need metadata (display names, colors, order)

sql

1CREATE TABLE ticket_categories (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  organization_id UUID NOT NULL REFERENCES organizations(id),
4  name VARCHAR(100) NOT NULL,
5  color VARCHAR(7), -- Hex color
6  display_order INT,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8);
9
10CREATE TABLE tickets (
11  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
12  category_id UUID REFERENCES ticket_categories(id),
13  -- ...
14);

JSONB: When and How to Use It

PostgreSQL's JSONB is powerful but often misused.

Use JSONB for:

Flexible metadata that varies by record
User-defined custom fields
Audit logs
API response caching

Don't use JSONB for:

Data you'll frequently query or join on
Structured data with known fields
Values that need foreign key constraints

Example: Custom Fields

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL,
4  
5  -- Standard fields as columns
6  priority VARCHAR(50) NOT NULL,
7  status VARCHAR(50) NOT NULL,
8  
9  -- Custom fields as JSONB
10  custom_fields JSONB DEFAULT '{}',
11  
12  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
13);
14
15-- Index for JSONB queries
16CREATE INDEX idx_tickets_custom_fields ON tickets USING GIN (custom_fields);
17
18-- Query custom fields
19SELECT * FROM tickets
20WHERE custom_fields->>'department' = 'Engineering';
21
22-- Update custom field
23UPDATE tickets
24SET custom_fields = jsonb_set(custom_fields, '{department}', '"Engineering"')
25WHERE id = 'some-uuid';

Soft Deletes vs. Hard Deletes

Soft deletes keep deleted records for audit trails:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL,
4  status VARCHAR(50) NOT NULL,
5  
6  deleted_at TIMESTAMPTZ, -- NULL = not deleted
7  deleted_by_id UUID REFERENCES users(id),
8  
9  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
10);
11
12-- Index for active records queries
13CREATE INDEX idx_tickets_active ON tickets(id) WHERE deleted_at IS NULL;

Query patterns:

sql

1-- Get active tickets
2SELECT * FROM tickets WHERE deleted_at IS NULL;
3
4-- Soft delete
5UPDATE tickets
6SET deleted_at = NOW(), deleted_by_id = 'user-uuid'
7WHERE id = 'ticket-uuid';
8
9-- Restore
10UPDATE tickets
11SET deleted_at = NULL, deleted_by_id = NULL
12WHERE id = 'ticket-uuid';
13
14-- Hard delete (rare)
15DELETE FROM tickets WHERE deleted_at < NOW() - INTERVAL '90 days';

Relationship Patterns

One-to-Many

Most common relationship:

sql

1CREATE TABLE users (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  name VARCHAR(255) NOT NULL
4);
5
6CREATE TABLE tickets (
7  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
8  assigned_to_id UUID REFERENCES users(id),
9  title VARCHAR(500) NOT NULL
10);
11
12-- Index for reverse lookup
13CREATE INDEX idx_tickets_assigned_to ON tickets(assigned_to_id);

Many-to-Many

Use a junction table:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL
4);
5
6CREATE TABLE tags (
7  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
8  name VARCHAR(100) NOT NULL
9);
10
11CREATE TABLE ticket_tags (
12  ticket_id UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
13  tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
14  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
15  
16  PRIMARY KEY (ticket_id, tag_id)
17);
18
19-- Indexes for both directions
20CREATE INDEX idx_ticket_tags_ticket ON ticket_tags(ticket_id);
21CREATE INDEX idx_ticket_tags_tag ON ticket_tags(tag_id);

Self-Referential (Comments, Hierarchy)

sql

1CREATE TABLE comments (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  ticket_id UUID NOT NULL REFERENCES tickets(id),
4  parent_id UUID REFERENCES comments(id), -- NULL for top-level
5  author_id UUID NOT NULL REFERENCES users(id),
6  content TEXT NOT NULL,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8);
9
10-- Index for fetching replies
11CREATE INDEX idx_comments_parent ON comments(parent_id);
12
13-- Query: Get all replies to a comment
14SELECT * FROM comments WHERE parent_id = 'comment-uuid';
15
16-- Query: Get top-level comments
17SELECT * FROM comments WHERE ticket_id = 'ticket-uuid' AND parent_id IS NULL;

Indexing Strategy

Indexes speed up reads but slow down writes. Be strategic:

When to Add Indexes

sql

1-- 1. Foreign keys (always)
2CREATE INDEX idx_tickets_assignee ON tickets(assignee_id);
3
4-- 2. Columns in WHERE clauses
5CREATE INDEX idx_tickets_status ON tickets(status);
6
7-- 3. Columns in ORDER BY
8CREATE INDEX idx_tickets_created ON tickets(created_at DESC);
9
10-- 4. Composite indexes for common queries
11CREATE INDEX idx_tickets_status_created ON tickets(status, created_at DESC);
12
13-- 5. Unique constraints (prevent duplicates)
14CREATE UNIQUE INDEX idx_users_email ON users(organization_id, email);
15
16-- 6. Partial indexes (for filtered queries)
17CREATE INDEX idx_tickets_open ON tickets(created_at)
18WHERE status = 'open' AND deleted_at IS NULL;

Check Index Usage

sql

1-- See which indexes are used
2SELECT
3  schemaname,
4  tablename,
5  indexname,
6  idx_scan,
7  idx_tup_read,
8  idx_tup_fetch
9FROM pg_stat_user_indexes
10WHERE schemaname = 'public'
11ORDER BY idx_scan ASC;
12
13-- Find unused indexes (candidates for removal)
14SELECT
15  schemaname,
16  tablename,
17  indexname
18FROM pg_stat_user_indexes
19WHERE idx_scan = 0
20  AND indexname NOT LIKE 'pg_toast%'
21ORDER BY tablename;

Constraints for Data Integrity

Let the database enforce rules:

sql

1CREATE TABLE users (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  email VARCHAR(255) NOT NULL,
4  age INT,
5  status VARCHAR(50) NOT NULL DEFAULT 'active',
6  
7  -- Check constraints
8  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
9  CONSTRAINT age_range CHECK (age >= 13 AND age <= 120),
10  CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'suspended'))
11);

Audit Logging Pattern

Track who changed what and when:

sql

1CREATE TABLE audit_logs (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  organization_id UUID NOT NULL REFERENCES organizations(id),
4  
5  -- What was changed
6  table_name VARCHAR(100) NOT NULL,
7  record_id UUID NOT NULL,
8  action VARCHAR(50) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
9  
10  -- Who made the change
11  user_id UUID NOT NULL REFERENCES users(id),
12  
13  -- What changed
14  old_values JSONB,
15  new_values JSONB,
16  
17  -- When
18  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
19  
20  -- Context (IP, user agent, etc.)
21  metadata JSONB DEFAULT '{}'
22);
23
24CREATE INDEX idx_audit_logs_record ON audit_logs(table_name, record_id);
25CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);

Migration Strategy

Schema changes should be:

Reversible - Always write a down migration
Non-breaking - Don't break existing code
Incremental - Small, tested changes

Example: Adding a NOT NULL column safely

sql

1-- ❌ Bad: Breaks if table has existing rows
2ALTER TABLE users ADD COLUMN department VARCHAR(100) NOT NULL;
3
4-- ✅ Good: Add as nullable first
5ALTER TABLE users ADD COLUMN department VARCHAR(100);
6
7-- Backfill existing rows
8UPDATE users SET department = 'Engineering' WHERE department IS NULL;
9
10-- Then add constraint
11ALTER TABLE users ALTER COLUMN department SET NOT NULL;

Key Takeaways

Use consistent naming conventions - prevents confusion and bugs
Add tenant_id everywhere for multi-tenant SaaS
Use TIMESTAMPTZ for all timestamps, store in UTC
Index foreign keys and frequently queried columns
Use constraints to enforce data integrity at the database level
Soft delete when audit trails matter
JSONB for flexible metadata, not structured data
Plan migrations carefully - always reversible and non-breaking

Good schema design is like a good foundation—it's invisible when done right but catastrophic when done wrong. Take the time to design it properly from the start.

Need help designing your database schema? Get in touch.

Continue reading

PreviousBuilding Real-Time Features with WebSockets in Next.js NextTypeScript Patterns for Scalable Frontend Applications

Backend Architecture SaaS6 min read

Backend Architecture for Modern SaaS Applications

A deep dive into scalable backend patterns, database design, and API architecture that power production SaaS platforms.

January 15, 2025Read more

WebSockets Real-Time Next.js8 min read

Building Real-Time Features with WebSockets in Next.js

How to implement WebSocket communication for live notifications, collaborative editing, and real-time dashboards in modern web applications.

January 10, 2025Read more

TypeScript React Frontend9 min read

TypeScript Patterns for Scalable Frontend Applications

Advanced TypeScript techniques for building type-safe, maintainable React applications with real-world examples.

December 28, 2024Read more

Back to blog

Database PostgreSQL Schema Design Backend

Database Schema Design: Lessons from Production Systems

Practical patterns for designing maintainable, scalable PostgreSQL schemas for SaaS applications with real-world examples.

January 5, 202510 min read·Talha Bilal

Introduction

Your database schema is the foundation of your application. Get it wrong and you'll spend months refactoring. Get it right and your codebase stays clean and your queries stay fast as you scale.

Core Principles

Before diving into specific patterns, here are the principles I follow:

Normalize first, denormalize for performance - Start with proper normalization, denormalize only when needed
Use meaningful naming conventions - Clear names prevent bugs
Think in terms of domains - Group related tables logically
Plan for multi-tenancy from day one - Retrofitting is painful
Use constraints liberally - The database should enforce data integrity

Naming Conventions

Consistency matters more than the specific style you choose. Here's what I use:

Table Names

sql

1-- Use plural, lowercase with underscores
2users
3organizations
4ticket_comments
5payment_methods
6
7-- NOT: User, TicketComment, paymentMethod

Column Names

sql

1-- Descriptive, lowercase with underscores
2id              -- Primary key
3user_id         -- Foreign key (table_name + _id)
4created_at      -- Timestamp columns end with _at
5is_active       -- Boolean columns start with is_/has_
6email_address   -- Be specific, not just "email"

main-content

sql

1-- Pattern: fk_childtable_parenttable
2-- Example:
3CONSTRAINT fk_tickets_users FOREIGN KEY (assignee_id) REFERENCES users(id)

Primary Keys: UUID vs. Auto-Increment

This is a hot debate. Here's my take:

Use UUIDs when:

Building a distributed system
Exposing IDs in URLs (don't leak sequence information)
Merging data from multiple sources
Multi-tenant system where ID collisions are a concern

Use Auto-Increment (Serial/BigSerial) when:

Single database system
Internal IDs only (never exposed to users)
You need the slight performance benefit
Debugging with sequential IDs is valuable

My default: UUIDs

sql

1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
3CREATE TABLE users (
4  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
5  email VARCHAR(255) NOT NULL UNIQUE,
6  name VARCHAR(255) NOT NULL,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
9);

Why? Better for distributed systems, no information leakage, easier to merge datasets.

Timestamps: The Essential Pattern

Every table should track creation and modification:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  
4  -- ... other columns ...
5  
6  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
7  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8  deleted_at TIMESTAMPTZ -- For soft deletes
9);
10
11-- Trigger to auto-update updated_at
12CREATE OR REPLACE FUNCTION update_updated_at_column()
13RETURNS TRIGGER AS $$
14BEGIN
15  NEW.updated_at = NOW();
16  RETURN NEW;
17END;
18$$ LANGUAGE plpgsql;
19
20CREATE TRIGGER update_tickets_updated_at
21  BEFORE UPDATE ON tickets
22  FOR EACH ROW
23  EXECUTE FUNCTION update_updated_at_column();

Pro tip: Use TIMESTAMPTZ (timestamp with timezone), not TIMESTAMP. Always store in UTC.

Multi-Tenancy: The Shared Schema Approach

For B2B SaaS, add tenant_id to every table:

sql

1CREATE TABLE organizations (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  name VARCHAR(255) NOT NULL,
4  slug VARCHAR(100) NOT NULL UNIQUE,
5  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
6);
7
8CREATE TABLE users (
9  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
10  organization_id UUID NOT NULL REFERENCES organizations(id),
11  email VARCHAR(255) NOT NULL,
12  name VARCHAR(255) NOT NULL,
13  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
14  
15  -- Email unique per organization, not globally
16  UNIQUE(organization_id, email)
17);
18
19CREATE TABLE tickets (
20  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
21  organization_id UUID NOT NULL REFERENCES organizations(id),
22  title VARCHAR(500) NOT NULL,
23  description TEXT,
24  status VARCHAR(50) NOT NULL DEFAULT 'open',
25  assignee_id UUID REFERENCES users(id),
26  created_by_id UUID NOT NULL REFERENCES users(id),
27  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
28  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
29);
30
31-- Critical: Index for tenant queries
32CREATE INDEX idx_tickets_organization_id ON tickets(organization_id);
33CREATE INDEX idx_users_organization_id ON users(organization_id);

Enforce tenant isolation with Row-Level Security:

sql

1ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
2
3CREATE POLICY tenant_isolation ON tickets
4  USING (organization_id = current_setting('app.current_tenant_id')::UUID);
5
6-- Set in application code before queries:
7-- SET LOCAL app.current_tenant_id = 'tenant-uuid-here';

Enum vs. Reference Tables

When should you use an enum vs. a lookup table?

Use ENUMs for:

Small, fixed sets of values that rarely change
Application-defined states (status, priority)

sql

1CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');
2
3CREATE TABLE tickets (
4  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
5  status ticket_status NOT NULL DEFAULT 'open',
6  -- ...
7);

Use Reference Tables for:

User-defined categories
Values that change over time
Values that need metadata (display names, colors, order)

sql

1CREATE TABLE ticket_categories (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  organization_id UUID NOT NULL REFERENCES organizations(id),
4  name VARCHAR(100) NOT NULL,
5  color VARCHAR(7), -- Hex color
6  display_order INT,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8);
9
10CREATE TABLE tickets (
11  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
12  category_id UUID REFERENCES ticket_categories(id),
13  -- ...
14);

JSONB: When and How to Use It

PostgreSQL's JSONB is powerful but often misused.

Use JSONB for:

Flexible metadata that varies by record
User-defined custom fields
Audit logs
API response caching

Don't use JSONB for:

Data you'll frequently query or join on
Structured data with known fields
Values that need foreign key constraints

Example: Custom Fields

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL,
4  
5  -- Standard fields as columns
6  priority VARCHAR(50) NOT NULL,
7  status VARCHAR(50) NOT NULL,
8  
9  -- Custom fields as JSONB
10  custom_fields JSONB DEFAULT '{}',
11  
12  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
13);
14
15-- Index for JSONB queries
16CREATE INDEX idx_tickets_custom_fields ON tickets USING GIN (custom_fields);
17
18-- Query custom fields
19SELECT * FROM tickets
20WHERE custom_fields->>'department' = 'Engineering';
21
22-- Update custom field
23UPDATE tickets
24SET custom_fields = jsonb_set(custom_fields, '{department}', '"Engineering"')
25WHERE id = 'some-uuid';

Soft Deletes vs. Hard Deletes

Soft deletes keep deleted records for audit trails:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL,
4  status VARCHAR(50) NOT NULL,
5  
6  deleted_at TIMESTAMPTZ, -- NULL = not deleted
7  deleted_by_id UUID REFERENCES users(id),
8  
9  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
10);
11
12-- Index for active records queries
13CREATE INDEX idx_tickets_active ON tickets(id) WHERE deleted_at IS NULL;

Query patterns:

sql

1-- Get active tickets
2SELECT * FROM tickets WHERE deleted_at IS NULL;
3
4-- Soft delete
5UPDATE tickets
6SET deleted_at = NOW(), deleted_by_id = 'user-uuid'
7WHERE id = 'ticket-uuid';
8
9-- Restore
10UPDATE tickets
11SET deleted_at = NULL, deleted_by_id = NULL
12WHERE id = 'ticket-uuid';
13
14-- Hard delete (rare)
15DELETE FROM tickets WHERE deleted_at < NOW() - INTERVAL '90 days';

Relationship Patterns

One-to-Many

Most common relationship:

sql

1CREATE TABLE users (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  name VARCHAR(255) NOT NULL
4);
5
6CREATE TABLE tickets (
7  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
8  assigned_to_id UUID REFERENCES users(id),
9  title VARCHAR(500) NOT NULL
10);
11
12-- Index for reverse lookup
13CREATE INDEX idx_tickets_assigned_to ON tickets(assigned_to_id);

Many-to-Many

Use a junction table:

sql

1CREATE TABLE tickets (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  title VARCHAR(500) NOT NULL
4);
5
6CREATE TABLE tags (
7  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
8  name VARCHAR(100) NOT NULL
9);
10
11CREATE TABLE ticket_tags (
12  ticket_id UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
13  tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
14  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
15  
16  PRIMARY KEY (ticket_id, tag_id)
17);
18
19-- Indexes for both directions
20CREATE INDEX idx_ticket_tags_ticket ON ticket_tags(ticket_id);
21CREATE INDEX idx_ticket_tags_tag ON ticket_tags(tag_id);

Self-Referential (Comments, Hierarchy)

sql

1CREATE TABLE comments (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  ticket_id UUID NOT NULL REFERENCES tickets(id),
4  parent_id UUID REFERENCES comments(id), -- NULL for top-level
5  author_id UUID NOT NULL REFERENCES users(id),
6  content TEXT NOT NULL,
7  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8);
9
10-- Index for fetching replies
11CREATE INDEX idx_comments_parent ON comments(parent_id);
12
13-- Query: Get all replies to a comment
14SELECT * FROM comments WHERE parent_id = 'comment-uuid';
15
16-- Query: Get top-level comments
17SELECT * FROM comments WHERE ticket_id = 'ticket-uuid' AND parent_id IS NULL;

Indexing Strategy

Indexes speed up reads but slow down writes. Be strategic:

When to Add Indexes

sql

1-- 1. Foreign keys (always)
2CREATE INDEX idx_tickets_assignee ON tickets(assignee_id);
3
4-- 2. Columns in WHERE clauses
5CREATE INDEX idx_tickets_status ON tickets(status);
6
7-- 3. Columns in ORDER BY
8CREATE INDEX idx_tickets_created ON tickets(created_at DESC);
9
10-- 4. Composite indexes for common queries
11CREATE INDEX idx_tickets_status_created ON tickets(status, created_at DESC);
12
13-- 5. Unique constraints (prevent duplicates)
14CREATE UNIQUE INDEX idx_users_email ON users(organization_id, email);
15
16-- 6. Partial indexes (for filtered queries)
17CREATE INDEX idx_tickets_open ON tickets(created_at)
18WHERE status = 'open' AND deleted_at IS NULL;

Check Index Usage

sql

1-- See which indexes are used
2SELECT
3  schemaname,
4  tablename,
5  indexname,
6  idx_scan,
7  idx_tup_read,
8  idx_tup_fetch
9FROM pg_stat_user_indexes
10WHERE schemaname = 'public'
11ORDER BY idx_scan ASC;
12
13-- Find unused indexes (candidates for removal)
14SELECT
15  schemaname,
16  tablename,
17  indexname
18FROM pg_stat_user_indexes
19WHERE idx_scan = 0
20  AND indexname NOT LIKE 'pg_toast%'
21ORDER BY tablename;

Constraints for Data Integrity

Let the database enforce rules:

sql

1CREATE TABLE users (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  email VARCHAR(255) NOT NULL,
4  age INT,
5  status VARCHAR(50) NOT NULL DEFAULT 'active',
6  
7  -- Check constraints
8  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
9  CONSTRAINT age_range CHECK (age >= 13 AND age <= 120),
10  CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'suspended'))
11);

Audit Logging Pattern

Track who changed what and when:

sql

1CREATE TABLE audit_logs (
2  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
3  organization_id UUID NOT NULL REFERENCES organizations(id),
4  
5  -- What was changed
6  table_name VARCHAR(100) NOT NULL,
7  record_id UUID NOT NULL,
8  action VARCHAR(50) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
9  
10  -- Who made the change
11  user_id UUID NOT NULL REFERENCES users(id),
12  
13  -- What changed
14  old_values JSONB,
15  new_values JSONB,
16  
17  -- When
18  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
19  
20  -- Context (IP, user agent, etc.)
21  metadata JSONB DEFAULT '{}'
22);
23
24CREATE INDEX idx_audit_logs_record ON audit_logs(table_name, record_id);
25CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);

Migration Strategy

Schema changes should be:

Reversible - Always write a down migration
Non-breaking - Don't break existing code
Incremental - Small, tested changes

Example: Adding a NOT NULL column safely

sql

1-- ❌ Bad: Breaks if table has existing rows
2ALTER TABLE users ADD COLUMN department VARCHAR(100) NOT NULL;
3
4-- ✅ Good: Add as nullable first
5ALTER TABLE users ADD COLUMN department VARCHAR(100);
6
7-- Backfill existing rows
8UPDATE users SET department = 'Engineering' WHERE department IS NULL;
9
10-- Then add constraint
11ALTER TABLE users ALTER COLUMN department SET NOT NULL;

Key Takeaways

Use consistent naming conventions - prevents confusion and bugs
Add tenant_id everywhere for multi-tenant SaaS
Use TIMESTAMPTZ for all timestamps, store in UTC
Index foreign keys and frequently queried columns
Use constraints to enforce data integrity at the database level
Soft delete when audit trails matter
JSONB for flexible metadata, not structured data
Plan migrations carefully - always reversible and non-breaking

Good schema design is like a good foundation—it's invisible when done right but catastrophic when done wrong. Take the time to design it properly from the start.

Need help designing your database schema? Get in touch.

Continue reading

PreviousBuilding Real-Time Features with WebSockets in Next.js NextTypeScript Patterns for Scalable Frontend Applications

Backend Architecture SaaS6 min read

Backend Architecture for Modern SaaS Applications

A deep dive into scalable backend patterns, database design, and API architecture that power production SaaS platforms.

January 15, 2025Read more

WebSockets Real-Time Next.js8 min read

Building Real-Time Features with WebSockets in Next.js

How to implement WebSocket communication for live notifications, collaborative editing, and real-time dashboards in modern web applications.

January 10, 2025Read more

TypeScript React Frontend9 min read

TypeScript Patterns for Scalable Frontend Applications

Advanced TypeScript techniques for building type-safe, maintainable React applications with real-world examples.

December 28, 2024Read more

Database Schema Design: Lessons from Production Systems

Continue reading

Related Articles

Backend Architecture for Modern SaaS Applications

Building Real-Time Features with WebSockets in Next.js

TypeScript Patterns for Scalable Frontend Applications

Database Schema Design: Lessons from Production Systems

Continue reading

Related Articles

Backend Architecture for Modern SaaS Applications

Building Real-Time Features with WebSockets in Next.js

TypeScript Patterns for Scalable Frontend Applications