PostgreSQL Database Design Best Practices¶

-- Create core entities with proper relationships
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    website VARCHAR(200),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_organizations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'member',
    is_active BOOLEAN DEFAULT TRUE,
    joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, organization_id)
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE project_members (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'contributor',
    permissions JSONB,
    joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(project_id, user_id)
);

-- Create audit trail table
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INTEGER NOT NULL,
    operation VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE
    old_values JSONB,
    new_values JSONB,
    changed_by INTEGER REFERENCES users(id),
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    client_ip INET,
    user_agent TEXT
);

-- Create audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(NEW), current_setting('app.current_user_id')::integer);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(OLD), to_jsonb(NEW), current_setting('app.current_user_id')::integer);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_by)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, to_jsonb(OLD), current_setting('app.current_user_id')::integer);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create audit triggers
CREATE TRIGGER users_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER projects_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON projects
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Create hierarchical categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    path LTREE,
    level INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create function to update path and level
CREATE OR REPLACE FUNCTION update_category_path()
RETURNS TRIGGER AS $$
DECLARE
    parent_path LTREE;
    parent_level INTEGER;
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path := NEW.id::text::ltree;
        NEW.level := 0;
    ELSE
        SELECT path, level INTO parent_path, parent_level
        FROM categories WHERE id = NEW.parent_id;

        NEW.path := parent_path || NEW.id::text;
        NEW.level := parent_level + 1;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to update path
CREATE TRIGGER update_categories_path
    BEFORE INSERT OR UPDATE ON categories
    FOR EACH ROW EXECUTE FUNCTION update_category_path();

-- Insert sample hierarchical data
INSERT INTO categories (name, parent_id) VALUES
    ('Technology', NULL),
    ('Programming', 1),
    ('Web Development', 2),
    ('Frontend', 3),
    ('Backend', 3),
    ('Database', 2),
    ('PostgreSQL', 6),
    ('MySQL', 6);

-- Query hierarchical data
SELECT 
    c.id,
    c.name,
    c.path,
    c.level,
    REPEAT('  ', c.level) || c.name as indented_name
FROM categories c
ORDER BY c.path;

-- Create polymorphic associations for comments
CREATE TABLE commentable_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

INSERT INTO commentable_types (name) VALUES ('post'), ('project'), ('user');

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    commentable_type VARCHAR(50) NOT NULL,
    commentable_id INTEGER NOT NULL,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(commentable_type, commentable_id, author_id, created_at)
);

-- Create function to validate polymorphic associations
CREATE OR REPLACE FUNCTION validate_commentable_reference()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate that the referenced record exists
    IF NEW.commentable_type = 'post' THEN
        IF NOT EXISTS (SELECT 1 FROM posts WHERE id = NEW.commentable_id) THEN
            RAISE EXCEPTION 'Referenced post does not exist';
        END IF;
    ELSIF NEW.commentable_type = 'project' THEN
        IF NOT EXISTS (SELECT 1 FROM projects WHERE id = NEW.commentable_id) THEN
            RAISE EXCEPTION 'Referenced project does not exist';
        END IF;
    ELSIF NEW.commentable_type = 'user' THEN
        IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.commentable_id) THEN
            RAISE EXCEPTION 'Referenced user does not exist';
        END IF;
    ELSE
        RAISE EXCEPTION 'Invalid commentable_type: %', NEW.commentable_type;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_commentable_reference_trigger
    BEFORE INSERT OR UPDATE ON comments
    FOR EACH ROW EXECUTE FUNCTION validate_commentable_reference();

-- Create denormalized views for performance
CREATE MATERIALIZED VIEW user_project_summary AS
SELECT 
    u.id as user_id,
    u.username,
    u.email,
    COUNT(p.id) as project_count,
    COUNT(pm.id) as member_count,
    MAX(p.created_at) as latest_project_created,
    ARRAY_AGG(DISTINCT o.name) as organization_names
FROM users u
LEFT JOIN projects p ON u.id = p.owner_id
LEFT JOIN project_members pm ON u.id = pm.user_id
LEFT JOIN organizations o ON p.organization_id = o.id
GROUP BY u.id, u.username, u.email;

-- Create index on materialized view
CREATE INDEX idx_user_project_summary_username ON user_project_summary (username);

-- Create function to refresh materialized view
CREATE OR REPLACE FUNCTION refresh_user_project_summary()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY user_project_summary;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to auto-refresh materialized view
CREATE OR REPLACE FUNCTION trigger_refresh_user_project_summary()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM refresh_user_project_summary();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER refresh_user_project_summary_trigger
    AFTER INSERT OR UPDATE OR DELETE ON projects
    FOR EACH STATEMENT EXECUTE FUNCTION trigger_refresh_user_project_summary();

-- Create partitioned table for events
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50) NOT NULL,
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Create indexes on partitions
CREATE INDEX idx_events_2024_01_user_id ON events_2024_01 (user_id);
CREATE INDEX idx_events_2024_01_event_type ON events_2024_01 (event_type);
CREATE INDEX idx_events_2024_01_created_at ON events_2024_01 (created_at);

-- Create function to auto-create partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(table_name TEXT, start_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    end_date DATE;
BEGIN
    partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');
    end_date := start_date + interval '1 month';

    EXECUTE format('CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                   partition_name, table_name, start_date, end_date);

    -- Create indexes on new partition
    EXECUTE format('CREATE INDEX %I ON %I (user_id)', 
                   'idx_' || partition_name || '_user_id', partition_name);
    EXECUTE format('CREATE INDEX %I ON %I (event_type)', 
                   'idx_' || partition_name || '_event_type', partition_name);
    EXECUTE format('CREATE INDEX %I ON %I (created_at)', 
                   'idx_' || partition_name || '_created_at', partition_name);
END;
$$ LANGUAGE plpgsql;

-- Create comprehensive constraints
ALTER TABLE users ADD CONSTRAINT users_email_format 
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

ALTER TABLE users ADD CONSTRAINT users_username_length 
    CHECK (LENGTH(username) >= 3 AND LENGTH(username) <= 50);

ALTER TABLE projects ADD CONSTRAINT projects_status_valid 
    CHECK (status IN ('active', 'inactive', 'archived', 'deleted'));

ALTER TABLE project_members ADD CONSTRAINT project_members_role_valid 
    CHECK (role IN ('owner', 'admin', 'contributor', 'viewer'));

-- Create custom constraint for business rules
CREATE OR REPLACE FUNCTION validate_project_owner()
RETURNS TRIGGER AS $$
BEGIN
    -- Ensure project owner is a member of the organization
    IF NOT EXISTS (
        SELECT 1 FROM user_organizations uo
        WHERE uo.user_id = NEW.owner_id 
        AND uo.organization_id = NEW.organization_id
        AND uo.is_active = TRUE
    ) THEN
        RAISE EXCEPTION 'Project owner must be a member of the organization';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_project_owner_trigger
    BEFORE INSERT OR UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION validate_project_owner();

-- Create soft delete pattern
CREATE TABLE soft_deletable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMPTZ,
    deleted_by INTEGER REFERENCES users(id),
    is_deleted BOOLEAN GENERATED ALWAYS AS (deleted_at IS NOT NULL) STORED
);

-- Create function for soft delete
CREATE OR REPLACE FUNCTION soft_delete_record(
    table_name TEXT,
    record_id INTEGER,
    deleted_by_user_id INTEGER
)
RETURNS BOOLEAN AS $$
BEGIN
    EXECUTE format('UPDATE %I SET deleted_at = CURRENT_TIMESTAMP, deleted_by = %s WHERE id = %s',
                   table_name, deleted_by_user_id, record_id);

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Create view to exclude soft-deleted records
CREATE VIEW active_projects AS
SELECT * FROM projects WHERE deleted_at IS NULL;

-- Create function to restore soft-deleted records
CREATE OR REPLACE FUNCTION restore_record(
    table_name TEXT,
    record_id INTEGER
)
RETURNS BOOLEAN AS $$
BEGIN
    EXECUTE format('UPDATE %I SET deleted_at = NULL, deleted_by = NULL WHERE id = %s',
                   table_name, record_id);

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Create schema versioning table
CREATE TABLE schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    applied_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    description TEXT
);

-- Create function to track schema changes
CREATE OR REPLACE FUNCTION track_schema_change(
    change_description TEXT
)
RETURNS VOID AS $$
DECLARE
    current_version VARCHAR(50);
BEGIN
    current_version := to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HH24MISS');

    INSERT INTO schema_migrations (version, description)
    VALUES (current_version, change_description);

    RAISE NOTICE 'Schema change tracked: % - %', current_version, change_description;
END;
$$ LANGUAGE plpgsql;

-- Example schema migration
DO $$
BEGIN
    -- Add new column
    ALTER TABLE users ADD COLUMN phone VARCHAR(20);

    -- Track the change
    PERFORM track_schema_change('Add phone column to users table');
END $$;

-- Create function to handle schema evolution gracefully
CREATE OR REPLACE FUNCTION migrate_user_data()
RETURNS VOID AS $$
BEGIN
    -- Add new columns with defaults
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'users' AND column_name = 'phone') THEN
        ALTER TABLE users ADD COLUMN phone VARCHAR(20);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'users' AND column_name = 'preferences') THEN
        ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';
    END IF;

    -- Migrate existing data
    UPDATE users 
    SET preferences = '{"theme": "light", "notifications": true}'::jsonb
    WHERE preferences IS NULL;

    RAISE NOTICE 'User data migration completed';
END;
$$ LANGUAGE plpgsql;

-- 1. Create core entities with proper relationships
CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL);
CREATE TABLE organizations (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE projects (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, organization_id INTEGER REFERENCES organizations(id));

-- 2. Add constraints and indexes
ALTER TABLE users ADD CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE INDEX idx_projects_organization_id ON projects (organization_id);

-- 3. Create audit trail
CREATE TABLE audit_log (id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100), operation VARCHAR(20), old_values JSONB, new_values JSONB, changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP);