Database Schema Design: ऐसे Patterns जो समय के साथ अच्छे बने रहें

Schema आपके भविष्य के self के साथ एक contract है। हर column जो आप add करते हैं, हर constraint जो skip करते हैं, हर "बाद में clean up करेंगे" — यह सब compound होता है। मैंने ऐसे systems पर काम किया है जहां तीन साल पहले का एक बुरा schema decision अब team को हर quarter एक पूरा sprint workarounds में खर्च करवाता है।

Database सब कुछ outlive करता है। आपका frontend framework बदलेगा। API layer rewrite होगी। Deployment strategy evolve होगी। लेकिन data? Data रहता है। और day one पर जो shape दी, वो हमेशा follow करती है, क्योंकि 200 million rows वाली table migrate करना React component refactor करने जैसा नहीं है।

यह मैंने schema decisions के बारे में सीखा है जो आपको haunt नहीं करते। PostgreSQL-focused, क्योंकि वही मैं इस्तेमाल करता हूं और trust करता हूं, लेकिन ज़्यादातर patterns किसी भी relational database पर apply होते हैं।

Naming Conventions: वो Boring चीज़ जो सबसे ज़्यादा Matter करती है#

मैंने naming conventions पर actual architecture decisions से ज़्यादा बहस देखी है। सालों तक projects के बीच context-switch करने के बाद यह मेरा तरीका है:

हर जगह snake_case। Tables, columns, indexes, constraints। कोई camelCase नहीं, कोई PascalCase नहीं। PostgreSQL unquoted identifiers को वैसे भी lowercase में fold करता है, तो createdAt बन जाता है createdat जब तक हर जगह double-quote नहीं करते। Database से मत लड़ो।

Plural table names। एक table बहुत सारी rows hold करती है। users न कि user। orders न कि order। order_items न कि order_item। Queries में naturally पढ़ा जाता है: SELECT * FROM users WHERE ... — आप एक collection से select कर रहे हैं।

Foreign keys के लिए _id suffix। user_id, order_id, tenant_id। Primary key बस id है। यह unambiguous है। जब tables join करते हैं, users.id = orders.user_id English जैसा पढ़ा जाता है।

Timestamps के लिए _at suffix। created_at, updated_at, deleted_at, published_at, expires_at। आपको हमेशा पता रहता है कि यह एक point in time है।

Booleans के लिए is_ prefix। is_active, is_verified, is_published। कुछ लोग ownership booleans के लिए has_ इस्तेमाल करते हैं (has_mfa_enabled), लेकिन मैं simple रखता हूं और is_ ही इस्तेमाल करता हूं।

Denormalized counters के लिए _count suffix। comment_count, follower_count। स्पष्ट करता है कि यह एक cached number है, live calculation नहीं।

बात यह है: consistency perfection से बेहतर है। मैंने teams को email_address या email या email_addr पर हफ्ते बिताते देखा है। बस एक pattern चुनो और हर जगह enforce करो। सबसे बुरी naming convention वो है जो inconsistently apply की गई हो।

sql

-- अच्छा: consistent, readable, कोई surprises नहीं
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    login_count     INTEGER NOT NULL DEFAULT 0,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- बुरा: inconsistent casing, mixed conventions, ambiguous names
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- क्या यह 0/1 है? verification level?
    lastLogin       TIMESTAMP,       -- snake_case दुनिया में camelCase
    created         TIMESTAMP        -- कब created? क्या created?
);

एक और बात: column names में कभी reserved words इस्तेमाल मत करो। type, order, user, group, table — ये सब quote करने पर काम करते हैं, लेकिन ORMs, query builders, और हर dynamic SQL generation tool में आपको जलाएंगे। type की जगह kind इस्तेमाल करो, order की जगह sort_order। आपका भविष्य का self शुक्रगुज़ार होगा।

Standard Columns: हर Table को क्या मिलता है#

मेरे schemas में हर table एक ही skeleton से शुरू होती है। कोई अपवाद नहीं।

sql

CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... domain columns ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

UUID vs BIGSERIAL बहस#

यह उन decisions में से एक है जो ज़रूरत से ज़्यादा गरमी पैदा करते हैं। असली trade-off यह है:

BIGSERIAL (auto-incrementing integer):

8 bytes। Compact। Index और join करने में fast।
Ordered — id से sort करके insertion order मिलता है।
Predictable — user अपनी ID increment करके दूसरी IDs guess कर सकता है।
Distributed systems में ठीक से काम नहीं करता (coordination चाहिए)।

UUID v4 (random):

16 bytes। बड़े indexes, धीमे joins (लेकिन शायद ही कभी bottleneck)।
Unpredictable — कोई information leakage नहीं।
Distributed systems में बिना coordination काम करता है।
B-tree indexes की खराब locality — random UUIDs B-tree indexes fragment करते हैं।

UUID v7 (time-sorted, RFC 9562):

16 bytes, लेकिन time-ordered तो B-tree locality बहुत अच्छी।
बाहरी उपयोग के लिए काफी unpredictable।
अपेक्षाकृत नया, लेकिन PostgreSQL 17+ में gen_random_uuid() है और extensions से uuid_generate_v7() इस्तेमाल कर सकते हैं।

मेरा वर्तमान रुख: Internal tables के लिए BIGSERIAL, बाहरी दुनिया को दिखने वाली किसी भी चीज़ के लिए UUID v7। अगर ID कभी URL, API response, या webhook payload में दिखती है, UUID इस्तेमाल करो। अगर यह pure join table है जो users कभी नहीं देखते, BIGSERIAL ठीक है।

sql

-- API-facing resource के लिए
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Internal mapping table के लिए
CREATE TABLE project_members (
    id          BIGSERIAL PRIMARY KEY,
    project_id  UUID NOT NULL REFERENCES projects(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL DEFAULT 'member',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, user_id)
);

Timestamps: हमेशा TIMESTAMPTZ#

TIMESTAMPTZ इस्तेमाल करो, TIMESTAMP नहीं। "TZ" version value को UTC में store करता है और session timezone के आधार पर read पर convert करता है। Non-TZ version जो भी दो वो store करता है बिना timezone context के — जिसका मतलब है कि अगर अलग-अलग timezones के दो servers एक ही table में लिखें, तो silent data corruption होती है।

sql

-- हमेशा यह
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- कभी यह नहीं
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

updated_at Trigger#

PostgreSQL में MySQL का ON UPDATE CURRENT_TIMESTAMP नहीं है। आपको trigger चाहिए:

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- हर table पर apply करो जिसमें updated_at है
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

हां, हर table के लिए एक trigger चाहिए। हां, tedious है। एक migration helper लिखो जो table add करते समय automatically trigger बनाए। यह उचित है, क्योंकि alternative है अपनी पूरी application में हर UPDATE query में updated_at = NOW() set करना याद रखना — और आप भूल जाएंगे।

Normalization: कब नियम तोड़ें#

हर CS course 3NF (Third Normal Form) तक normalization सिखाता है। नियम हैं:

1NF: हर column एक single atomic value hold करता है। कोई arrays नहीं, कोई comma-separated lists नहीं।
2NF: हर non-key column पूरी primary key पर depend करता है (composite keys के लिए relevant)।
3NF: कोई transitive dependencies नहीं। अगर column A column B determine करता है, और B C determine करता है, तो C उसी table में A के साथ नहीं होना चाहिए।

Practice में, transactional tables के लिए 3NF sweet spot है। वहां से शुरू करें और तभी deviate करें जब कोई specific, measurable कारण हो।

यहां एक properly normalized order system है:

sql

CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

order_items पर unit_price_cents ध्यान दें। यह जानबूझकर denormalization है। हम order के समय price का snapshot लेते हैं, क्योंकि product की price बाद में बदल सकती है। Denormalize करने का यह सबसे आम और सही कारण है।

कब Denormalization सही फैसला है#

Reporting tables। अगर आपके analytics dashboard को render करने के लिए 8 tables join करनी पड़ें, एक denormalized reporting table बनाओ और background job से populate करो। आपका transactional schema clean रहता है, और reporting queries fast रहती हैं।

sql

-- Denormalized reporting table, cron job से populate
CREATE TABLE order_reports (
    id                  BIGSERIAL PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    customer_email      TEXT NOT NULL,
    customer_name       TEXT NOT NULL,
    product_names       TEXT[] NOT NULL,        -- array यहां ठीक है
    item_count          INTEGER NOT NULL,
    total_cents         INTEGER NOT NULL,
    order_status        TEXT NOT NULL,
    ordered_at          TIMESTAMPTZ NOT NULL,
    report_generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Materialized views। PostgreSQL का MATERIALIZED VIEW underrated है। यह एक denormalized snapshot है जो demand पर refresh होता है। Dashboards के लिए perfect।

sql

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;
 
-- हर रात refresh करो
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Dynamic attributes के लिए JSON columns। जब अलग-अलग rows को अलग-अलग fields चाहिए — product variants, form submissions, user preferences — JSONB column अक्सर nightmare EAV (Entity-Attribute-Value) table से बेहतर है। इसके बारे में बाद में और।

एक नियम जो मैं कभी नहीं तोड़ता#

अपनी source-of-truth tables को कभी denormalize मत करो। Copies, snapshots, reports, और caches denormalize करो। Canonical data normalized रहता है। जब denormalized copy stale या corrupt हो जाए (और होगी), normalized source से rebuild करो।

Foreign Keys और Constraints: सबसे अच्छा Code जो आप कभी नहीं लिखेंगे#

मैंने foreign keys skip करने का हर बहाना सुना है। "ये writes slow करते हैं।" "हम application में enforce करते हैं।" "हमें flexibility चाहिए।"

ये सब गलत हैं।

Foreign keys सबसे impactful चीज़ हैं जो आप schema में add कर सकते हैं। ये bugs की पूरी categories रोकते हैं जिन्हें कोई application code नहीं पकड़ सकता — race conditions, partial failures, failed transactions से orphaned rows। Foreign key database engine की गारंटी है, storage level पर enforce। आपके ORM का beforeDelete hook एक suggestion है।

sql

-- हमेशा यह करो
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- "Code में handle करेंगे" alternative:
-- उम्मीद। उम्मीद strategy नहीं है।

ON DELETE Strategies#

यहां बारीकियां आती हैं। Parent row delete करने पर क्या होता है?

RESTRICT (default): अगर child rows exist करती हैं तो delete fail होता है। ज़्यादातर relationships के लिए यह इस्तेमाल करो। आप ऐसे customer को delete नहीं कर सकते जिसके orders हैं — यह business logic है जो schema में encoded है।

sql

-- Customer delete नहीं हो सकता जब तक orders हैं
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Child rows automatically delete हो जाती हैं। इसे कम और जानबूझकर इस्तेमाल करो। "Part-of" relationships के लिए अच्छा है जहां child का parent के बिना कोई मतलब नहीं।

sql

-- Order delete करने से line items delete — अकेले meaningless हैं
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Project delete करने से memberships delete
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: Foreign key column NULL set हो जाता है। जब relationship optional हो और child row अकेली भी meaningful हो तब इस्तेमाल करो।

sql

-- अगर manager छोड़ता है, reports अभी भी exist — बस unassigned
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

मेरा thumb rule: default RESTRICT रखो, composition relationships के लिए CASCADE, optional associations के लिए SET NULL इस्तेमाल करो। अगर unsure हो, RESTRICT हमेशा safe choice है — constraint ढीला करना आसान है बनिस्बत deleted data recover करने के।

Check Constraints: सस्ता बीमा#

Check constraints write time पर लगभग कुछ खर्च नहीं करते और हमेशा के लिए garbage data को system में आने से रोकते हैं:

sql

CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    discount_pct    NUMERIC(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    weight_kg       NUMERIC(10,3) CHECK (weight_kg > 0),
    status          TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

हर check constraint जो आप add करते हैं, एक कम validation bug है जो आपको कभी production में debug करनी पड़ेगी। Database आखिरी रक्षा पंक्ति है। इसे इस्तेमाल करो।

Unique Constraints और Partial Unique Indexes#

Unique constraints simple cases के लिए straightforward हैं:

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

लेकिन partial indexes के साथ दिलचस्प हो जाती हैं — unique constraints जो सिर्फ कुछ rows पर apply होती हैं:

sql

-- प्रति user सिर्फ एक active subscription (लेकिन cancelled कितनी भी हो सकती हैं)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- प्रति user सिर्फ एक primary address
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

यह PostgreSQL की killer features में से एक है। इस्तेमाल करो।

Soft Deletes: वो Pattern जिससे सब नफरत करते हैं#

Soft deletes concept में simple हैं: DELETE FROM users WHERE id = 42 की बजाय, UPDATE users SET deleted_at = NOW() WHERE id = 42 करो। Row database में रहती है लेकिन normal queries से filter हो जाती है।

sql

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Partial index: deleted rows को efficiently filter करो
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

अब आपकी application की हर query में WHERE deleted_at IS NULL चाहिए। हर। एक। Query। ORMs इसमें मदद करते हैं (Prisma में middleware है, Drizzle में .where(isNull(deletedAt)) है), लेकिन यह हर read operation पर tax है। एक बार चूकें और "deleted" data users को दिख रहा है।

Unique Constraint समस्या#

यहां soft deletes बदसूरत हो जाते हैं। अगर UNIQUE (email) है और user अपना account soft-delete करता है, email अभी भी table में है। वो same email से re-register नहीं कर सकते। नया user भी उस email से sign up नहीं कर सकता।

Fix partial unique index है:

sql

-- Email unique होना चाहिए, लेकिन सिर्फ non-deleted users में
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

काम करता है, लेकिन अब आपको हर soft-deletable table पर हर unique column के लिए यह pattern याद रखना है। Discipline से manageable है, लेकिन complexity है जो आपने चुनी है।

Foreign Key समस्या#

Soft deletes foreign keys के साथ बुरे तरीके से interact करते हैं। अगर orders.user_id users.id reference करता है ON DELETE RESTRICT के साथ, और आप user soft-delete करते हैं... कुछ नहीं होता। FK fire नहीं होता क्योंकि आपने actually row delete नहीं की। Application के नज़रिए से user "gone" है लेकिन database में बहुत मौजूद है।

इसका मतलब आपके application code को handle करना होगा जब referenced entity soft-deleted हो। हर join, हर lookup, हर बार foreign key follow करें — referenced table पर भी deleted_at IS NULL check करना होगा। या नहीं करो, और application दिखाता है "Order by [deleted user]" जो bug है या feature, निर्भर करता है किससे पूछते हो।

मेरा तरीका#

मैं soft deletes सिर्फ user-facing entities के लिए इस्तेमाल करता हूं जहां recovery business requirement है — user accounts, projects, documents। ऐसी चीज़ें जहां support agent को deletion restore करनी पड़ सकती है। बाकी सब के लिए, hard deletes with audit trail (इसके बारे में आगे)।

sql

-- Soft delete: user-facing, recoverable
CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_documents_active ON documents (owner_id) WHERE deleted_at IS NULL;
 
-- Hard delete: internal, UI से recoverable नहीं (लेकिन audited)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash    TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Delete होने पर, gone है। audit_log record करता है कि exist करती थी।

Audit Trails: जानो क्या बदला और किसने किया#

हर non-trivial application को किसी न किसी बिंदु पर "इस record को क्या हुआ?" का जवाब चाहिए। Audit trails इस तरह जवाब देते हैं बिना application logs खोजे।

Separate Audit Table Pattern#

सबसे simple approach: एक audit_log table जो हर change record करती है।

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- UUID और BIGINT दोनों PKs handle करने के लिए text
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- INSERT के लिए NULL
    new_values      JSONB,                  -- DELETE के लिए NULL
    changed_fields  TEXT[],                 -- कौन से columns बदले (UPDATE के लिए)
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      INET,
    user_agent      TEXT
);
 
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_by ON audit_log (changed_by);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Challenge इसे populate करना है। Application code में कर सकते हैं (explicit, लेकिन भूलना आसान) या triggers के साथ (automatic, लेकिन changed_by जैसा context pass करना मुश्किल)।

Trigger-Based Approach#

Triggers हर change automatically capture करते हैं, raw SQL या database admin operations से भी:

sql

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    changed  TEXT[];
    col      TEXT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_at)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', old_data, NOW());
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
        -- बदले हुए fields ढूंढो
        FOR col IN SELECT key FROM jsonb_each(new_data)
        LOOP
            IF old_data->col IS DISTINCT FROM new_data->col THEN
                changed := array_append(changed, col);
            END IF;
        END LOOP;
        -- सिर्फ तभी log करो जब कुछ actually बदला हो
        IF array_length(changed, 1) > 0 THEN
            INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, changed_at)
            VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE', old_data, new_data, changed, NOW());
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_at)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', new_data, NOW());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- जिन tables को audit करना है उन पर apply करो
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
 
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

कमी: triggers नहीं जानते कि कौन से application user ने change किया। Session variables से workaround कर सकते हैं:

sql

-- Application में, query से पहले:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- Trigger function में:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

काम करता है लेकिन fragile लगता है। Practice में, मैं hybrid approach इस्तेमाल करता हूं: data capture के लिए triggers, और session context set करने के लिए application code।

History Table Pattern#

जिन tables का पूरा version history चाहिए (सिर्फ "क्या बदला" नहीं बल्कि "time T पर state क्या थी"), dedicated history table ज़्यादा clean है:

sql

CREATE TABLE documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL DEFAULT 1,
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE document_history (
    id              BIGSERIAL PRIMARY KEY,
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL,
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, version)
);

documents पर हर update से पहले, current state document_history में copy करो और version increment करो। अब किसी भी point in time पर document reconstruct कर सकते हो, versions के बीच diffs दिखा सकते हो, और पुराने versions restore भी कर सकते हो।

Trade-off storage है। अगर content column बड़ा है और बार-बार बदलता है, history table तेज़ी से बढ़ सकती है। ज़्यादातर applications के लिए यह ठीक है — storage सस्ता है और ज़रूरत हो तो पुराने versions cold storage में archive कर सकते हो।

Multi-Tenancy: तीन Approaches, अपना दर्द चुनो#

Multi-tenancy उन चीज़ों में से है जो शुरू में add करना आसान है और बाद में लगभग असंभव। अगर ज़रा सी भी संभावना है कि application multiple organizations serve करेगी, day one से build करो।

Row-Level: हर Table पर tenant_id#

सबसे आम approach। हर table में tenant_id column है, और हर query उससे filter करती है।

sql

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- हर index में tenant_id होना चाहिए query performance के लिए
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

खतरा data leakage है — एक छूटा हुआ WHERE tenant_id = ... और Tenant A का data Tenant B को दिख रहा है। PostgreSQL का Row-Level Security (RLS) इस पूरी bug category को खत्म करता है:

sql

-- Table पर RLS enable करो
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Session variable पर based policy बनाओ
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Table owners के लिए भी RLS force करो
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

अब, भले ही application code WHERE tenant_id = ... clause भूल जाए, PostgreSQL automatically add करता है। यह defense in depth है, और multi-tenant systems में PostgreSQL के लिए सबसे मज़बूत arguments में से एक।

sql

-- Application के connection middleware में:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- अब RLS-enabled tables पर सभी queries automatically filter होती हैं
SELECT * FROM projects;
-- PostgreSQL internally add करता है: WHERE tenant_id = 'tenant-uuid-here'

फायदे: Single database, simple ops, efficient resource usage, admin के लिए आसान cross-tenant queries। नुकसान: Discipline चाहिए (या RLS), हर query tenant_id touch करती है, tenants को अपना backup/restore देना मुश्किल।

Schema-Per-Tenant#

हर tenant को अपना PostgreSQL schema मिलता है। सभी schemas एक ही database share करते हैं, लेकिन tables namespace से isolated हैं।

sql

-- हर tenant के लिए schema बनाओ
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tables tenant के schema में रहती हैं
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Current tenant के लिए search_path set करो
SET search_path TO tenant_acme, public;
 
-- अब unqualified queries tenant का schema hit करती हैं
SELECT * FROM projects;  -- tenant_acme.projects query करता है

फायदे: मज़बूत isolation, cross-tenant data leakage का कोई risk नहीं, आसान per-tenant backup/restore, tenants में schema variations हो सकती हैं। नुकसान: Schema migration complexity (N schemas migrate करने पड़ते हैं), connection pool management, PostgreSQL की ~10,000 schemas की practical limit।

Database-Per-Tenant#

हर tenant को अपना database मिलता है। Maximum isolation।

फायदे: Complete isolation, independent scaling, आसान backup/restore, बड़े tenants को dedicated hardware पर रख सकते हैं। नुकसान: Connection management nightmare, cross-tenant queries impossible, migration N बार run करनी पड़ती है, significant operational overhead।

कौन सा चुनें?#

ज़्यादातर SaaS applications के लिए: row-level + RLS से शुरू करो। Operate करना सबसे simple है, और RLS अधिकांश use cases के लिए काफी मज़बूत isolation देता है। Schema-per-tenant तभी जाओ जब contractual isolation requirements हों (enterprise customers, regulated industries)। Database-per-tenant तब जब absolutely physical isolation guarantee करना हो — और तब भी, managed databases consider करो जहां operational burden handle होता है।

JSON/JSONB Columns: Escape Hatch#

PostgreSQL का JSONB remarkable है। Relational system के अंदर document-database flexibility देता है। लेकिन किसी भी powerful tool की तरह, abuse करना आसान है।

कब JSONB सही Choice है#

Dynamic user-defined attributes। E-commerce platform जहां हर product category के अलग attributes हैं — shoes में size और color है, electronics में voltage और wattage। EAV table या हर possible attribute के लिए column की बजाय:

sql

CREATE TABLE products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES categories(id),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Example data:
-- Shoe: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Application settings और configuration। User preferences, feature flags, notification settings — ऐसी चीज़ें जो shape frequently बदलती हैं और relational integrity नहीं चाहिए।

sql

CREATE TABLE user_settings (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{
        "theme": "system",
        "notifications": {"email": true, "push": false},
        "locale": "en",
        "timezone": "UTC"
    }',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Webhook payloads, API responses, event data। कुछ भी जहां schema external system control करता है और बिना notice बदल सकता है।

JSONB पर GIN Indexes#

बिना indexes के, JSONB के अंदर query करने के लिए full table scan चाहिए। GIN indexes इसे fast बनाते हैं:

sql

-- पूरे JSONB column पर index (@> containment queries के लिए अच्छा)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: सभी black color products ढूंढो
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: इनमें से कोई attribute वाले products ढूंढो
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Specific paths जो बार-बार query करते हो, उनके लिए targeted index ज़्यादा efficient है:

sql

-- Specific path index करो
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- अब यह regular B-tree lookup है
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Migration Trap#

यहां JSONB काटता है: JSON के अंदर fields पर NOT NULL constraints या default values आसानी से add नहीं कर सकते। अगर product attributes में नया required field add करो, हर existing row backfill करनी पड़ती है। Regular column के साथ, migration atomically handle करती है। JSONB के साथ, UPDATE लिख रहे हो जो हर row touch करता है और उम्मीद कर रहे हो कि backfill complete होने तक application code missing fields gracefully handle करे।

sql

-- नया column add करना: clean, atomic, एक statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- नया JSONB field add करना: messy, backfill चाहिए
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus application code update करो missing weight_kg handle करने के लिए
-- Plus validation logic update करो
-- Plus हर API response update करो जो attributes include करता है

मेरा नियम: अगर JSONB field को WHERE clause में हफ्ते में दो बार से ज़्यादा query कर रहे हो, तो शायद column होना चाहिए। JSONB बढ़िया escape hatch है। भयानक default है।

Schema Migrations: Production में रात 3 बजे कुछ मत तोड़ो#

Schema migrations वहां हैं जहां theory reality से मिलती है। Schema कागज़ पर अच्छा दिखता है, लेकिन अब 50 million rows वाली table को business hours में बिना downtime alter करना है।

Migration Tools#

मैंने ज़्यादातर इस्तेमाल किए हैं। संक्षिप्त राय:

Drizzle (TypeScript): मेरा current favorite। Schema-as-code, schema से generated type-safe queries, clean migration SQL। Development के लिए push command fast है।

Prisma (TypeScript): Simple schemas के लिए great DX। Advanced PostgreSQL features (partial indexes, custom types, RLS) के साथ struggle करता है। Migration engine surprising decisions ले सकता है।

Flyway (Java/CLI): Rock-solid, battle-tested, SQL-first। अगर raw SQL migrations लिख रहे हो, Flyway reliably track करता है। कोई magic नहीं, कोई surprises नहीं।

golang-migrate (Go/CLI): Flyway जैसा लेकिन lighter। Go projects या जब बस simple up/down migration runner चाहिए।

Zero-Downtime समस्या#

सबसे खतरनाक schema changes वो हैं जो table lock करते हैं। PostgreSQL में, ALTER TABLE ... ADD COLUMN default value के साथ पहले पूरी table lock करता था rewrite के दौरान। PostgreSQL 11 से, simple defaults (constants) metadata-only और instant हैं। लेकिन दूसरी operations अभी भी lock करती हैं:

sql

-- SAFE: metadata-only, instant (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- खतरनाक: पूरी table rewrite करता है, full table lock
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- खतरनाक: validate करने के लिए पूरी table scan करता है
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

NOT NULL Columns सुरक्षित तरीके से Add करना#

Data वाली existing table पर बस NOT NULL add नहीं कर सकते — fail होता है क्योंकि existing rows के पास value नहीं है। Naive approach:

sql

-- यह table lock करता है और rewrite करता है। बड़ी table पर मत करो।
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

PostgreSQL 11 से, constant defaults के लिए यह actually safe है — metadata-only है। लेकिन अगर default function है या computed values से backfill करना है, expand-contract pattern इस्तेमाल करो।

Expand-Contract Pattern#

Zero-downtime schema changes का gold standard। तीन phases:

Phase 1: Expand — नया column nullable add करो। Application code deploy करो जो old और new दोनों columns में लिखे।

sql

-- Migration 1: nullable column add करो
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Phase 2: Migrate — Existing rows batches में backfill करो। Application पहले से नए data के लिए new column में लिख रही है।

sql

-- Migration 2: batches में backfill करो (बड़ी tables के लिए एक statement में मत करो)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- सब rows backfill होने तक repeat करो

Phase 3: Contract — सब rows backfill होने के बाद, NOT NULL constraint add करो और old column remove करो (अगर applicable हो)।

sql

-- Migration 3: Constraint add करो (full table scan बचाने के लिए NOT VALID इस्तेमाल करो, फिर अलग से validate करो)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migration 4: Constraint validate करो (ShareUpdateExclusiveLock लेता है, AccessExclusiveLock नहीं)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

NOT VALID + VALIDATE CONSTRAINT trick crucial है। Check constraint normally add करने से heavy lock hold करके पूरी table scan होती है। NOT VALID बिना scan constraint add करता है (सिर्फ new writes पर apply होता है), और VALIDATE CONSTRAINT lighter lock से scan करता है जो reads या writes block नहीं करता।

Index Creation#

बड़ी tables पर indexes बनाना default से writes block करता है। हमेशा CONCURRENTLY इस्तेमाल करो:

sql

-- WRITES BLOCK करता है: live table पर मत करो
CREATE INDEX idx_users_email ON users (email);
 
-- NON-BLOCKING: इसकी जगह यह इस्तेमाल करो
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY ज़्यादा समय लेता है और transaction के अंदर run नहीं हो सकता, लेकिन table lock नहीं करता। Production में trade-off हमेशा उचित है।

वो Decisions जिन पर मुझे पछतावा हुआ#

हर schema में पछतावे होते हैं। यह मेरे हैं।

External IDs के लिए UUID की जगह SERIAL इस्तेमाल करना#

Project की शुरुआत में, मैंने SERIAL primary keys इस्तेमाल किए और URLs में directly expose किए: /users/42, /orders/1337। इससे information leak हुई (competitors हमारे user count guess कर सकते थे), pagination guessable था, और जब दो regions के databases merge करने पड़े तो टूट गया। External-facing IDs के लिए UUIDs पर switch करने में multi-month migration लगी।

सबक: database के बाहर दिखने वाली किसी भी चीज़ के लिए UUIDs इस्तेमाल करो। Internal join tables के लिए SERIAL/BIGSERIAL चाहो तो रखो, लेकिन auto-incrementing integer कभी URL में मत दिखाओ।

कोई Constraints नहीं ("बाद में Add करेंगे")#

हमने एक table बिना check constraints के launch की क्योंकि "तेज़ चल रहे हैं और बाद में add करेंगे।" दो हफ्ते के अंदर, data में negative prices, खाली names, और order_status value "oof" थी जो testing के दौरान किसी ने type की और कभी clean up नहीं किया।

बाद में constraints add करने के लिए चाहिए था:

सारा invalid data ढूंढना
उसके साथ क्या करना है decide करना (fix, delete, या grandfather)
Migration लिखना जो data backfill/fix करे और constraint add करे

इसमें day one पर constraint add करने से ज़्यादा समय लगा। Constraints से शुरू करो। ज़रूरत हो तो ढीला करो। उल्टा कभी नहीं।

Arrays या Join Tables की जगह Comma-Separated Strings#

मैंने एक बार tags comma-separated string में store किए: "javascript,react,nextjs"। Query करना nightmare था:

sql

-- Comma-separated values इस तरह query करते हैं। यह मत करो।
SELECT * FROM posts WHERE tags LIKE '%react%';
-- यह "react-native" और "preact" को भी match करता है
 
-- मुझे यह करना चाहिए था:
CREATE TABLE post_tags (
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

या कम से कम, PostgreSQL का native array type इस्तेमाल करो:

sql

CREATE TABLE posts (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL DEFAULT '{}',
    -- ...
);
 
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
 
-- Clean queries
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Arrays simple lists के लिए acceptable हैं जिन्हें अपने attributes नहीं चाहिए। जिस moment relationship पर metadata चाहिए (जैसे "यह tag किसने add किया" या "कब add किया"), join table चाहिए।

Column Name "type" इस्तेमाल करना#

sql

-- उस समय ठीक लगा
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- फिर किसी भी ORM में query करो:
-- notification.type  ← हर language के type system से conflict
-- "type" ज़्यादातर SQL dialects में reserved word है
-- हर जगह quote करना पड़ता है: SELECT "type" FROM notifications

kind, category, या notification_type इस्तेमाल करो। type के अलावा कुछ भी। इसी तरह, order से बचो (sort_order या position इस्तेमाल करो), user से बचो (account या prefix करो), और group से बचो (team या group_name)।

शुरू से created_at नहीं Add करना#

एक table को created_at नहीं मिला क्योंकि "हमें ज़रूरत नहीं।" तीन महीने बाद, issue debug करना था और पता नहीं था records कब बने। बाद में add करने का मतलब था सभी existing rows को same timestamp (migration timestamp) मिला, जिससे historical data बेकार हो गया।

हर table को created_at मिलता है। कोई अपवाद नहीं। Cost एक column है। नहीं होने की cost अज्ञात है जब तक ज़रूरत न पड़े।

Business Logic Database Views में रखना#

मैंने एक बार views की chain बनाई — active_users ने users filter किया, premium_active_users ने active_users filter किया, और reporting view ने सबको join किया। बढ़िया काम किया जब तक किसी ने users table बदली और तीनों views silently टूट गए। Query planner भी multiple view layers optimize करने में struggle करता था।

Views convenience और read-only access patterns के लिए great हैं। Business logic encode करने के लिए terrible हैं जो बदलती है। Business rules application code में रखो जहां versioned, tested, और deployable हैं। Views reporting shortcuts के लिए इस्तेमाल करो, architectural building blocks के रूप में नहीं।

सब को एक साथ रखना#

यह एक well-designed schema दिखता है जब इन patterns को combine करें। एक simplified project management system:

sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- gen_random_uuid() के लिए
 
-- Updated_at trigger function (reusable)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenants
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    slug            TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    plan            TEXT NOT NULL DEFAULT 'free'
                    CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Users
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL CHECK (LENGTH(display_name) > 0),
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    last_login_at   TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE UNIQUE INDEX uq_users_email_per_tenant
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_tenant ON users (tenant_id) WHERE deleted_at IS NULL;
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- RLS Enable करो
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projects
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'archived', 'deleted')),
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_projects_owner ON projects (owner_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Tasks
CREATE TABLE tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title           TEXT NOT NULL CHECK (LENGTH(title) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'todo'
                    CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority        INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4),
    assignee_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_status ON tasks (tenant_id, status) WHERE status != 'done';
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Task comments
CREATE TABLE task_comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL CHECK (LENGTH(body) > 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_task_comments_task ON task_comments (task_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON task_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON task_comments
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Audit log (कोई RLS नहीं — admin-only table)
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_log_tenant_table ON audit_log (tenant_id, table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Patterns ध्यान दें:

हर table में tenant_id है और RLS enabled है (tenants और audit_log को छोड़कर)।
हर table में created_at और updated_at triggers के साथ हैं।
सभी primary keys UUIDs हैं (external-facing entities)।
Status enums, lengths, और ranges पर check constraints।
Appropriate ON DELETE behavior के साथ foreign keys।
Access patterns के लिए designed indexes (tenant + project, tenant + status)।
Soft deletes सिर्फ users पर (जहां account recovery ज़रूरी है), projects पर status-based archival।

अंतिम विचार#

Schema design glamorous नहीं है। किसी ने कभी conference talk नहीं दी "मैंने हर Table पर Check Constraints Add किए और छह महीने की Debugging बचाई।" लेकिन अच्छा schema design exactly यही करता है — समस्याएं इतनी चुपचाप रोकता है कि आपको पता भी नहीं चलता कि exist करतीं।

इस post में patterns नए नहीं हैं। ये सालों तक migrations लिखने, data corruption debug करने, और production load में schemas refactor करने का नतीजा हैं। हर एक इसलिए exist करता है क्योंकि मैंने, या किसी colleague ने, पहले दूसरे तरीके से किया और कीमत चुकाई।

Constraints से शुरू करो। Foreign keys इस्तेमाल करो। हर जगह created_at add करो। एक naming convention चुनो और बेरहमी से enforce करो। Multi-tenancy के लिए RLS इस्तेमाल करो। JSONB से सावधान रहो। Migrations को production-sized data पर test करो deploy करने से पहले।

Database foundation है। सही करो, और ऊपर बनी हर चीज़ simpler हो जाती है। गलत करो, और कोई clever application code नहीं बचा सकता।