データベーススキーマ設計：時を経ても色あせないパターン

スキーマとは、未来の自分との契約だ。追加するカラムの一つひとつ、スキップする制約の一つひとつ、「後で直そう」と言ったことのすべてが積み重なっていく。3年前のたった一つの悪いスキーマ決定が、今やチームに四半期ごとに丸1スプリント分の回避策を強いているシステムで働いたことがある。

データベースはすべてより長生きする。フロントエンドフレームワークは変わる。APIレイヤーは書き直される。デプロイ戦略も進化する。でもデータは？データはそこに残り続ける。初日に与えた形は永遠についてまわる。なぜなら、2億行のテーブルをマイグレーションすることは、Reactコンポーネントをリファクタリングすることとはわけが違うからだ。

これは、後々自分を苦しめないスキーマ決定をするために学んだことだ。PostgreSQL中心の内容だが、ほとんどのパターンはどのリレーショナルデータベースにも適用できる。

命名規則：最も退屈で最も重要なこと#

命名規則については、実際のアーキテクチャの議論よりも多くの論争を見てきた。プロジェクト間をコンテキストスイッチする年月を経て、最終的に落ち着いたのがこれだ：

すべてにsnake_caseを使う。 テーブル、カラム、インデックス、制約。camelCaseもPascalCaseも使わない。PostgreSQLはクオートなしの識別子を小文字に変換するので、createdAtはダブルクオートで囲まない限りcreatedatになる。データベースに逆らうな。

テーブル名は複数形。 テーブルは多数の行を保持する。userではなくusers。orderではなくorders。order_itemではなくorder_items。クエリで自然に読める：SELECT * FROM users WHERE ... — コレクションから選択しているのだ。

外部キーには_idサフィックス。 user_id、order_id、tenant_id。主キーは単にid。これは曖昧さがない。テーブルを結合するとき、users.id = orders.user_idは英語のように読める。

タイムスタンプには_atサフィックス。 created_at、updated_at、deleted_at、published_at、expires_at。常にある時点であることがわかる。

ブーリアンにはis_プレフィックス。 is_active、is_verified、is_published。所有を表すブーリアンにhas_を使う人もいる（has_mfa_enabled）が、私はシンプルにすべてis_を使っている。

非正規化カウンターには_countサフィックス。 comment_count、follower_count。これがキャッシュされた数値であり、リアルタイム計算ではないことが明確になる。

ポイントは：一貫性が完璧さに勝る。 email_addressにすべきかemailにすべきかemail_addrにすべきかを何週間も議論するチームを見てきた。一つのパターンを選んで、どこでも徹底すればいい。最悪の命名規則とは、一貫性なく適用されているものだ。

sql

-- 良い例：一貫性があり、読みやすく、驚きがない
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()
);
 
-- 悪い例：一貫性のないケース、混在した規則、曖昧な名前
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- これは0/1？検証レベル？
    lastLogin       TIMESTAMP,       -- snake_caseの世界でcamelCase
    created         TIMESTAMP        -- 何がcreated？いつ？
);

もう一つ：予約語をカラム名に絶対使うな。 type、order、user、group、table — クオートすればすべて動くが、ORM、クエリビルダー、あらゆる動的SQL生成ツールで痛い目に遭う。typeの代わりにkind、orderの代わりにsort_orderを使え。未来の自分が感謝するだろう。

標準カラム：すべてのテーブルが持つもの#

私のスキーマのすべてのテーブルは同じ骨格から始まる。例外なし。

sql

CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... ドメインカラム ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

UUID vs BIGSERIAL論争#

これは実際のトレードオフよりも遥かに多くの議論を生むテーマの一つだ。実際のトレードオフはこうだ：

BIGSERIAL（自動インクリメント整数）：

8バイト。コンパクト。インデックスとジョインが高速。
順序あり — idでソートすれば挿入順が得られる。
予測可能 — ユーザーは自分のIDをインクリメントして他のIDを推測できる。
分散システムではうまく機能しない（協調が必要）。

UUID v4（ランダム）：

16バイト。インデックスが大きく、ジョインが遅い（ただしボトルネックになることは稀）。
予測不能 — 情報漏洩がない。
協調なしで分散システムで動作する。
インデックスの局所性が最悪 — ランダムなUUIDはBツリーインデックスを断片化させる。

UUID v7（時刻ソート済み、RFC 9562）：

16バイトだが時間順なので、Bツリーの局所性が優秀。
外部利用には十分に予測不能。
比較的新しいが、PostgreSQL 17以降ではgen_random_uuid()があり、拡張でuuid_generate_v7()も使える。

現在の私のスタンス：内部テーブルにはBIGSERIAL、外部に公開するものにはUUID v7。 IDがURL、APIレスポンス、Webhookペイロードに現れるなら、UUIDを使え。ユーザーが決して目にしない純粋なジョインテーブルなら、BIGSERIALで十分だ。

sql

-- API向けリソースの場合
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()
);
 
-- 内部マッピングテーブルの場合
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)
);

タイムスタンプ：常にTIMESTAMPTZ#

TIMESTAMPではなくTIMESTAMPTZを使え。「TZ」版は値をUTCで格納し、セッションのタイムゾーンに基づいて読み取り時に変換する。非TZ版は与えられたものをタイムゾーンのコンテキストなしでそのまま格納する — つまり、異なるタイムゾーンの2つのサーバーが同じテーブルに書き込むと、サイレントなデータ破損が発生する。

sql

-- 常にこちら
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- 絶対にこちらは使わない
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

updated_atトリガー#

PostgreSQLにはMySQLのON UPDATE CURRENT_TIMESTAMPがない。トリガーが必要だ：

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 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();

そう、テーブルごとに1つのトリガーが必要だ。面倒だ。テーブル追加時に自動でトリガーを作成するマイグレーションヘルパーを書け。その価値はある。なぜなら、代替手段はアプリケーション全体のすべてのUPDATEクエリでupdated_at = NOW()を設定することを覚えておくことだが — 絶対に忘れるからだ。

正規化：ルールを破るべきとき#

すべてのCSコースは3NF（第三正規形）まで正規化を教える。ルールはこうだ：

1NF：すべてのカラムが単一のアトミックな値を保持する。配列もカンマ区切りリストもなし。
2NF：すべての非キーカラムが主キー全体に依存する（複合キーの場合に関連）。
3NF：推移的依存がない。カラムAがカラムBを決定し、BがCを決定するなら、CはAと同じテーブルにあるべきではない。

実務では、3NFがトランザクショナルテーブルのスイートスポットだ。 そこから始めて、具体的で測定可能な理由がある場合のみ逸脱すべきだ。

適切に正規化された注文システムはこうだ：

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に注目。これは意図的な非正規化だ。注文時点の価格をスナップショットしている。商品の価格は後で変わるかもしれないからだ。これは最もよくある、そして正しい非正規化の理由の一つだ。

非正規化が正解のとき#

レポーティングテーブル。 分析ダッシュボードが8テーブルをジョインしないとレンダリングできないなら、非正規化したレポーティングテーブルを作成し、バックグラウンドジョブで populate する。トランザクショナルスキーマはクリーンなまま、レポーティングクエリは高速なままだ。

sql

-- 非正規化レポーティングテーブル、cronジョブで populated
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,        -- 配列はここでは問題なし
    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()
);

マテリアライズドビュー。 PostgreSQLのMATERIALIZED VIEWは過小評価されている。オンデマンドでリフレッシュする非正規化スナップショットだ。ダッシュボードに最適。

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 MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

動的属性のJSONカラム。 行ごとに異なるフィールドが必要な場合 — 商品バリエーション、フォーム送信、ユーザー設定 — JSONBカラムは悪夢のようなEAV（Entity-Attribute-Value）テーブルよりも良い選択であることが多い。これについては後述する。

私が絶対に破らないルール#

ソース・オブ・トゥルースのテーブルを非正規化するな。コピー、スナップショット、レポート、キャッシュを非正規化しろ。正規のデータは正規化されたままにする。非正規化コピーが古くなったり破損したりしたら（必ずそうなる）、正規化されたソースから再構築すればいい。

外部キーと制約：書くことのない最高のコード#

外部キーをスキップするあらゆる言い訳を聞いてきた。「書き込みが遅くなる。」「アプリケーションで制御する。」「柔軟性が必要だ。」

すべて間違いだ。

外部キーはスキーマに追加できる最もインパクトのある要素だ。どれだけアプリケーションコードを書いても防げないカテゴリのバグを丸ごと防いでくれる — レースコンディション、部分的な失敗、失敗したトランザクションによる孤立行。外部キーはデータベースエンジン自体からの保証であり、ストレージレベルで強制される。ORMのbeforeDeleteフックは提案にすぎない。

sql

-- 常にこうする
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- 「コードで処理する」代替案：
-- 希望。希望は戦略ではない。

ON DELETE戦略#

ここからがニュアンスの出るところだ。親行を削除したとき何が起こるか？

RESTRICT（デフォルト）：子行が存在する場合、削除は失敗する。ほとんどのリレーションシップにはこれを使え。注文がある顧客は削除できない — それはスキーマに埋め込まれたビジネスロジックだ。

sql

-- 注文がある限り顧客は削除できない
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE：子行は自動的に削除される。控えめかつ意図的に使え。親なしでは子が意味を持たない「一部である」関係に良い。

sql

-- 注文を削除するとその明細も削除 — 単独では無意味
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- プロジェクトを削除するとメンバーシップも削除
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL：外部キーカラムがNULLに設定される。関係がオプショナルで、子行が単独でも意味を持つ場合に使え。

sql

-- マネージャーが退職しても、部下はまだ存在する — ただ未アサイン
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

私の経験則：デフォルトはRESTRICT、コンポジション関係にはCASCADE、オプショナルな関連にはSET NULL。 迷ったら、RESTRICTが常に安全な選択だ — 制約を緩めるのは、削除されたデータを復旧するよりずっと簡単だ。

チェック制約：安価な保険#

チェック制約は書き込み時のコストがほぼゼロで、ゴミデータがシステムに入るのを永遠に防ぐ：

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()
);

追加するチェック制約の一つひとつが、本番環境でデバッグする必要のないバリデーションバグを一つ減らす。データベースは最後の防衛線だ。活用しろ。

ユニーク制約と部分ユニークインデックス#

ユニーク制約は単純なケースでは簡単だ：

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

しかし、部分インデックス — 特定の行にのみ適用されるユニーク制約 — と組み合わせると面白くなる：

sql

-- ユーザーごとにアクティブなサブスクリプションは1つだけ（キャンセルされたものは複数可）
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- ユーザーごとにプライマリアドレスは1つだけ
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

これはPostgreSQLのキラー機能の一つだ。使え。

ソフトデリート：みんなが嫌いだけど愛するパターン#

ソフトデリートはコンセプトとしてはシンプルだ：DELETE FROM users WHERE id = 42の代わりに、UPDATE users SET deleted_at = NOW() WHERE id = 42を行う。行はデータベースに残るが、通常のクエリからはフィルタリングされる。

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()
);
 
-- 部分インデックス：削除された行を効率的にフィルタアウト
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

アプリケーションのすべてのクエリにWHERE deleted_at IS NULLが必要になる。すべての。単一の。クエリに。ORMは助けてくれる（Prismaにはミドルウェアがあり、Drizzleには.where(isNull(deletedAt))がある）が、すべての読み取り操作に対する税金だ。一度でも忘れれば、「削除された」データをユーザーに表示してしまう。

ユニーク制約の問題#

ソフトデリートが醜くなるのはここだ。UNIQUE (email)があって、ユーザーがアカウントをソフトデリートした場合、そのメールはまだテーブルにある。同じメールでは再登録できない。そのメールを持つ新しいユーザーもサインアップできない。

修正は部分ユニークインデックスだ：

sql

-- メールはユニークでなければならないが、削除されていないユーザー間でのみ
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

これは機能するが、ソフトデリート可能なすべてのテーブルのすべてのユニークカラムにこのパターンを覚えておく必要がある。規律があれば管理可能だが、自ら選んだ複雑さだ。

外部キーの問題#

ソフトデリートは外部キーとの相性が悪い。orders.user_idがON DELETE RESTRICTでusers.idを参照していて、ユーザーをソフトデリートすると…何も起こらない。実際に行を削除していないのでFKは発火しない。ユーザーはアプリケーションの観点からは「消えた」が、データベースではまだ健在だ。

これは、参照先エンティティがソフトデリートされたケースをアプリケーションコードが処理しなければならないことを意味する。すべてのジョイン、すべてのルックアップ、外部キーをたどるたびに — 参照先テーブルでもdeleted_at IS NULLをチェックする必要がある。あるいはチェックしないで、アプリケーションが「[削除済みユーザー]による注文」と表示する。それがバグか仕様かは聞く相手による。

私のアプローチ#

ソフトデリートは、リカバリーがビジネス要件であるユーザー向けエンティティにのみ使っている — ユーザーアカウント、プロジェクト、ドキュメント。サポート担当者が削除を復元する必要があるかもしれないもの。それ以外すべてには、監査証跡付きのハードデリートを使う（次のセクションで詳述）。

sql

-- ソフトデリート：ユーザー向け、復元可能
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;
 
-- ハードデリート：内部用、UIからは復元不可（ただし監査済み）
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()
);
-- 削除されたら消える。audit_logがそれが存在していたことを記録する。

監査証跡：何が変わったか、誰がやったかを知る#

すべての非自明なアプリケーションは、いずれ「このレコードに何が起こった？」という質問に答える必要がある。監査証跡は、アプリケーションログを掘り起こさずにその答えを提供する方法だ。

分離監査テーブルパターン#

最もシンプルなアプローチ：すべての変更を記録する単一のaudit_logテーブル。

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- UUIDとBIGINTの両方のPKを扱うためにtext
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- INSERTではNULL
    new_values      JSONB,                  -- DELETEではNULL
    changed_fields  TEXT[],                 -- どのカラムが変更されたか（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);

課題はそれを populate することだ。アプリケーションコードで行う方法（明示的だが忘れやすい）か、トリガーで行う方法（自動だがchanged_byのようなコンテキストを渡すのが難しい）がある。

トリガーベースのアプローチ#

トリガーは、生のSQLやデータベース管理操作からのものも含め、すべての変更を自動的にキャプチャする：

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);
        -- 変更されたフィールドを見つける
        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;
        -- 実際に何か変更された場合のみログ
        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;
 
-- 監査したいテーブルに適用
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();

欠点：トリガーはどのアプリケーションユーザーが変更を行ったかを知らない。セッション変数で回避できる：

sql

-- アプリケーションで、クエリの前に：
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- トリガー関数内で：
changed_by_id := current_setting('app.current_user_id', true)::UUID;

これは機能するが脆い感じがする。実際には、ハイブリッドアプローチを使っている：データキャプチャにはトリガー、セッションコンテキストの設定にはアプリケーションコード。

履歴テーブルパターン#

「何が変わったか」だけでなく「時刻Tの時点で状態はどうだったか」という完全なバージョン履歴が必要なテーブルには、専用の履歴テーブルの方がクリーンだ：

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を更新するたびに、現在の状態をdocument_historyにコピーしてバージョンをインクリメントする。これで任意の時点でのドキュメントを再構成でき、バージョン間の差分を表示でき、古いバージョンの復元も可能だ。

トレードオフはストレージだ。contentカラムが大きく頻繁に変更される場合、履歴テーブルは急速に成長する。ほとんどのアプリケーションではこれは問題ない — ストレージは安く、必要なら古いバージョンをコールドストレージにアーカイブできる。

マルチテナンシー：3つのアプローチ、苦労を選べ#

マルチテナンシーは、最初に追加するのは簡単だが後から追加するのはほぼ不可能なことの一つだ。アプリケーションが複数の組織にサービスを提供する可能性が少しでもあるなら、初日から組み込め。

行レベル：すべてのテーブルにtenant_id#

最も一般的なアプローチ。すべてのテーブルにtenant_idカラムがあり、すべてのクエリがそれでフィルタリングする。

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()
);
 
-- クエリパフォーマンスのため、すべてのインデックスにtenant_idを含める
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

リスクはデータ漏洩だ — WHERE tenant_id = ...を一つ忘れれば、テナントAのデータをテナントBに見せてしまう。PostgreSQLの行レベルセキュリティ（RLS）がこのクラスのバグを排除する：

sql

-- テーブルでRLSを有効化
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- セッション変数に基づくポリシーを作成
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- テーブルオーナーにもRLSを強制
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

これで、アプリケーションコードがWHERE tenant_id = ...句を忘れても、PostgreSQLが自動的に追加する。これは多層防御であり、マルチテナントシステムでPostgreSQLを選ぶ最も強力な理由の一つだ。

sql

-- アプリケーションのコネクションミドルウェアで：
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- これでRLS対応テーブルへのすべてのクエリが自動的にフィルタリング
SELECT * FROM projects;
-- PostgreSQLが内部的に追加：WHERE tenant_id = 'tenant-uuid-here'

メリット：単一データベース、シンプルな運用、効率的なリソース使用、管理者向けのクロステナントクエリが容易。 デメリット：規律が必要（またはRLS）、すべてのクエリがtenant_idに触れる、テナントごとのバックアップ/リストアが難しい。

スキーマ・パー・テナント#

各テナントが独自のPostgreSQLスキーマを持つ。すべてのスキーマは同じデータベースを共有するが、テーブルはネームスペースで分離される。

sql

-- 各テナント用のスキーマを作成
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- テーブルはテナントのスキーマに配置
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- 現在のテナント用にsearch_pathを設定
SET search_path TO tenant_acme, public;
 
-- これで修飾なしのクエリはテナントのスキーマにヒット
SELECT * FROM projects;  -- tenant_acme.projectsにクエリ

メリット：強力な分離、クロステナントデータ漏洩のリスクなし、テナントごとのバックアップ/リストアが容易、テナントがスキーマのバリエーションを持てる。 デメリット：スキーママイグレーションの複雑さ（N個のスキーマをマイグレーションする必要がある）、コネクションプール管理、PostgreSQLには約10,000スキーマという実用的な限界がある。

データベース・パー・テナント#

各テナントが独自のデータベースを持つ。最大の分離。

メリット：完全な分離、独立したスケーリング、容易なバックアップ/リストア、大きなテナントを専用ハードウェアに配置可能。 デメリット：コネクション管理が悪夢、クロステナントクエリが不可能、マイグレーションをN回実行する必要あり、大きな運用オーバーヘッド。

どれを選ぶ？#

ほとんどのSaaSアプリケーションでは：行レベル + RLSから始めろ。 運用が最もシンプルで、RLSは大多数のユースケースに十分な分離を提供する。契約上の分離要件がある場合のみスキーマ・パー・テナントに移行しろ（エンタープライズ顧客、規制対象業界）。データベース・パー・テナントは物理的な分離を絶対に保証しなければならない場合用だ — それでも、運用負荷を管理してくれるマネージドデータベースを検討しろ。

JSON/JSONBカラム：エスケープハッチ#

PostgreSQLのJSONBは驚異的だ。リレーショナルシステムの中にドキュメントデータベースの柔軟性を与えてくれる。しかし、あらゆる強力なツールと同様、乱用しやすい。

JSONBが正しい選択のとき#

動的なユーザー定義属性。 商品カテゴリごとに異なる属性を持つECプラットフォーム — 靴にはsizeとcolor、電子機器にはvoltageとwattageがある。EAVテーブルやすべての属性にカラムを作る代わりに：

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()
);
 
-- データ例：
-- 靴：{"size": "10", "color": "black", "material": "leather"}
-- ノートPC：{"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

アプリケーション設定とコンフィグレーション。 ユーザー設定、フィーチャーフラグ、通知設定 — 頻繁に形が変わり、リレーショナルな整合性が不要なもの。

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ペイロード、APIレスポンス、イベントデータ。 スキーマが外部システムによって制御され、予告なく変更される可能性があるもの。

JSONBのGINインデックス#

インデックスがなければ、JSONB内部のクエリはフルテーブルスキャンが必要になる。GINインデックスで高速化できる：

sql

-- JSONBカラム全体をインデックス（@>包含クエリに最適）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- クエリ：color = "black"のすべての商品を検索
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- クエリ：これらの属性のいずれかを持つ商品を検索
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

頻繁にクエリする特定のパスには、ターゲットインデックスの方が効率的だ：

sql

-- 特定のパスをインデックス
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- これで通常のBツリールックアップになる
SELECT * FROM products
WHERE attributes->>'color' = 'black';

マイグレーションの罠#

JSONBが牙をむくのはここだ：JSON内のフィールドにNOT NULL制約やデフォルト値を簡単に追加できない。 商品属性に新しい必須フィールドを追加するには、既存のすべての行をバックフィルする必要がある。通常のカラムなら、マイグレーションがアトミックに処理する。JSONBの場合、すべての行に触れるUPDATEを書いて、バックフィルが完了するまでアプリケーションコードが欠落フィールドを適切に処理することを祈るしかない。

sql

-- 新しいカラムの追加：クリーン、アトミック、1文
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- 新しいJSONBフィールドの追加：面倒、バックフィルが必要
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- さらにweight_kgの欠落を処理するようアプリケーションコードを更新
-- さらにバリデーションロジックを更新
-- さらにattributesを含むすべてのAPIレスポンスを更新

私のルール：JSONBフィールドをWHERE句で週2回以上クエリしているなら、おそらくカラムにすべきだ。 JSONBは優れたエスケープハッチだ。ひどいデフォルトだ。

スキーママイグレーション：午前3時に本番を壊すな#

スキーママイグレーションは理論が現実と出会う場所だ。紙の上ではスキーマは素晴らしく見えるが、今度は営業時間中にダウンタイムなしで5000万行のテーブルを変更しなければならない。

マイグレーションツール#

ほとんど使ってきた。簡潔な感想：

Drizzle（TypeScript）：現在のお気に入り。スキーマ・アズ・コード、スキーマから生成される型安全なクエリ、クリーンなマイグレーションSQL。開発用のpushコマンドが速い。

Prisma（TypeScript）：シンプルなスキーマには素晴らしいDX。高度なPostgreSQL機能（部分インデックス、カスタム型、RLS）で苦労する。マイグレーションエンジンが驚くような決定をすることがある。

Flyway（Java/CLI）：堅牢で実戦テスト済み、SQLファースト。生のSQLマイグレーションを書いているなら、Flywayが確実に追跡してくれる。魔法なし、驚きなし。

golang-migrate（Go/CLI）：Flywayに似ているがより軽量。Goプロジェクトや、シンプルなup/downマイグレーションランナーが欲しいときに最適。

ゼロダウンタイム問題#

最も危険なスキーマ変更はテーブルをロックするものだ。PostgreSQLでは、ALTER TABLE ... ADD COLUMNにデフォルト値を指定すると、かつてはリライトの間テーブル全体をロックしていた。PostgreSQL 11以降、シンプルなデフォルト（定数）はメタデータのみで瞬時に完了する。しかし他の操作はまだロックする：

sql

-- 安全：メタデータのみ、瞬時（PostgreSQL 11+）
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- 危険：テーブル全体をリライト、フルテーブルロック
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- 危険：バリデーションのためテーブル全体をスキャン
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

NOT NULLカラムの安全な追加#

既存のデータがあるテーブルに単純にNOT NULLを追加することはできない — 既存の行に値がないため失敗する。素朴なアプローチ：

sql

-- これはテーブルをロックしてリライトする。大きなテーブルではやるな。
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

PostgreSQL 11以降、定数デフォルトに関しては実際にこれは安全だ — メタデータのみだ。しかしデフォルトが関数の場合や計算値でバックフィルする必要がある場合は、エクスパンド-コントラクトパターンを使え。

エクスパンド-コントラクトパターン#

これはゼロダウンタイムスキーマ変更のゴールドスタンダードだ。3つのフェーズ：

フェーズ1：エクスパンド — 新しいカラムをnullableとして追加。古いカラムと新しいカラムの両方に書き込むアプリケーションコードをデプロイ。

sql

-- マイグレーション1：nullableカラムを追加
ALTER TABLE users ADD COLUMN normalized_email TEXT;

フェーズ2：マイグレート — 既存の行をバッチでバックフィル。アプリケーションは新しいデータについてはすでに新しいカラムに書き込んでいる。

sql

-- マイグレーション2：バッチでバックフィル（大きなテーブルでは1文で行うな）
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- すべての行がバックフィルされるまで繰り返す

フェーズ3：コントラクト — すべての行がバックフィルされたら、NOT NULL制約を追加し、古いカラムを削除（該当する場合）。

sql

-- マイグレーション3：制約を追加（フルテーブルスキャンを避けるためNOT VALIDを使用、その後別途バリデーション）
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- マイグレーション4：制約をバリデーション（AccessExclusiveLockではなくShareUpdateExclusiveLockを取得）
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

NOT VALID + VALIDATE CONSTRAINTのトリックは極めて重要だ。チェック制約を通常通り追加すると、重いロックを保持しながらテーブル全体をスキャンする。NOT VALIDはスキャンなしで制約を追加し（新しい書き込みにのみ適用）、VALIDATE CONSTRAINTは読み取りや書き込みをブロックしない軽いロックでスキャンする。

インデックス作成#

大きなテーブルでのインデックス作成はデフォルトで書き込みをブロックする。常にCONCURRENTLYを使え：

sql

-- 書き込みをブロック：本番テーブルではやるな
CREATE INDEX idx_users_email ON users (email);
 
-- ノンブロッキング：代わりにこちらを使え
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLYは時間がかかり、トランザクション内では実行できないが、テーブルをロックしない。本番環境では常にこのトレードオフに価値がある。

後悔した決断#

すべてのスキーマには後悔がある。これが私のものだ。

外部IDにSERIALの代わりにUUIDを使わなかったこと#

プロジェクトの初期にSERIAL主キーを使い、URLに直接公開していた：/users/42、/orders/1337。これは情報を漏洩させ（競合他社がユーザー数を推測できた）、ページネーションが推測可能になり、2つのリージョンからのデータベースをマージする必要があったときに壊れた。外部向けIDをUUIDに切り替えるには数ヶ月のマイグレーションが必要だった。

教訓：データベースの外に公開するものにはUUIDを使え。 内部ジョインテーブルにはSERIAL/BIGSERIALを使いたければ使えばいいが、自動インクリメント整数をURLに絶対に出すな。

制約なし（「後で追加する」）#

「スピード重視で、後で追加する」と言って制約なしでテーブルをローンチした。2週間以内に、データにはマイナスの価格、空の名前、テスト中に誰かが入力してクリーンアップされなかったorder_statusの値"oof"が含まれていた。

事後に制約を追加するには以下が必要だった：

すべての無効なデータを見つける
どう処理するか決める（修正、削除、またはそのまま残す）
データを修正/バックフィルし、かつ制約を追加するマイグレーションを書く

これは初日に制約を追加するよりも長くかかった。制約から始めろ。必要なら緩めろ。逆は絶対にやるな。

配列やジョインテーブルの代わりにカンマ区切り文字列#

かつてタグをカンマ区切り文字列で格納したことがある："javascript,react,nextjs"。クエリは悪夢だった：

sql

-- カンマ区切り値のクエリ方法。やるな。
SELECT * FROM posts WHERE tags LIKE '%react%';
-- これは"react-native"や"preact"にもマッチする
 
-- やるべきだったこと：
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のネイティブ配列型を使え：

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);
 
-- クリーンなクエリ
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

配列は独自の属性が不要なシンプルなリストには許容される。関係にメタデータが必要になった瞬間（「誰がこのタグを追加したか」や「いつ追加されたか」など）、ジョインテーブルが必要だ。

カラム名に"type"を使ったこと#

sql

-- その時は問題なさそうだった
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- そしてあらゆるORMでクエリしようとすると：
-- notification.type  ← あらゆる言語のtype systemと衝突
-- "type"はほとんどのSQL方言で予約語
-- 至る所でクオートが必要に：SELECT "type" FROM notifications

kind、category、またはnotification_typeを使え。type以外なら何でもいい。同様に、order（sort_orderやpositionを使え）、user（accountにするかプレフィックスを付けろ）、group（teamやgroup_nameを使え）も避けろ。

最初からcreated_atを追加しなかったこと#

あるテーブルにcreated_atをつけなかった。「必要ない」からだ。3ヶ月後、問題をデバッグする必要があり、レコードがいつ作成されたかわからなかった。後から追加したら、既存の行すべてが同じタイムスタンプ（マイグレーション実行時）になり、過去のデータは役に立たなくなった。

すべてのテーブルにcreated_atをつけろ。例外なし。コストは1カラム。それをつけないコストは、必要になるまでわからない。

ビジネスロジックをデータベースビューに入れたこと#

かつてビューのチェーンを作成した — active_usersがusersをフィルタし、premium_active_usersがactive_usersをフィルタし、レポーティングビューがすべてを結合した。usersテーブルを誰かが変更するまではうまく動いていたが、3つのビューすべてがサイレントに壊れた。クエリプランナーも複数のビューレイヤーを通した最適化に苦労した。

ビューは利便性と読み取り専用のアクセスパターンには最適だ。変化するビジネスロジックを埋め込む場所としては最悪だ。ビジネスルールはバージョン管理され、テストされ、デプロイ可能なアプリケーションコードに保持しろ。ビューはレポーティングのショートカットに使え。アーキテクチャの構成要素としては使うな。

すべてを組み合わせる#

これらのパターンを組み合わせた、よく設計されたスキーマの姿がこれだ。簡略化されたプロジェクト管理システム：

sql

-- 拡張
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- gen_random_uuid()用
 
-- updated_atトリガー関数（再利用可能）
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- テナント
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();
 
-- ユーザー
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を有効化
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- プロジェクト
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);
 
-- タスク
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);
 
-- タスクコメント
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);
 
-- 監査ログ（RLSなし — 管理者専用テーブル）
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);

パターンに注目：

すべてのテーブルにtenant_idとRLSが有効（tenantsとaudit_logを除く）。
すべてのテーブルにcreated_atとupdated_atのトリガー付き。
すべての主キーにUUID（外部向けエンティティ）。
ステータスenum、長さ、範囲にチェック制約。
適切なON DELETE動作の外部キー。
アクセスパターンに合わせたインデックス設計（tenant + project、tenant + status）。
ソフトデリートはusersのみ（アカウント復旧が必要な場合）、projectsはステータスベースのアーカイブ。

最後に#

スキーマ設計は華やかではない。「すべてのテーブルにチェック制約を追加して6ヶ月分のデバッグを節約した」というタイトルのカンファレンストークを聞いたことがある人は誰もいない。しかし、良いスキーマ設計がまさにそれをやってくれる — 問題を静かに防いでくれるので、そもそもその問題が存在していたことすら気づかない。

この記事のパターンは目新しいものではない。マイグレーションを書き、データ破損をデバッグし、本番負荷の下でスキーマをリファクタリングした年月の結果だ。そのすべてが存在するのは、私自身か一緒に働いた誰かが、先にもう一方のやり方を試してその代償を払ったからだ。

制約から始めろ。外部キーを使え。すべてにcreated_atを追加しろ。命名規則を選んで容赦なく徹底しろ。マルチテナンシーにはRLSを使え。JSONBには気をつけろ。マイグレーションをデプロイする前に、本番規模のデータに対してテストしろ。

データベースは基盤だ。正しくやれば、その上に構築されるすべてがシンプルになる。間違えれば、どれだけ巧みなアプリケーションコードを書いても救えない。