Canonical DDL Patterns

CREATE TABLE catalog_menu_items (
    id            uuid          DEFAULT gen_random_uuid() PRIMARY KEY,
    tenant_id     uuid          NOT NULL REFERENCES tenants(id),
    sku           text          NOT NULL,
    name          text          NOT NULL,
    price_cents   integer       NOT NULL CHECK (price_cents >= 0),
    metadata      jsonb         NOT NULL DEFAULT '{}',  -- non-critical fields only
    created_at    timestamptz   NOT NULL DEFAULT now(),
    updated_at    timestamptz   NOT NULL DEFAULT now()
);

-- SKU unique per tenant, not globally
ALTER TABLE catalog_menu_items
    ADD CONSTRAINT uq_menu_items_tenant_sku UNIQUE (tenant_id, sku);

-- Partial index for common active-item filter
CREATE INDEX idx_menu_items_tenant_active
    ON catalog_menu_items (tenant_id)
    WHERE metadata->>'status' = 'active';

ALTER TABLE catalog_menu_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON catalog_menu_items
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_insert ON catalog_menu_items
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE TABLE outbox_events (
    id             uuid          DEFAULT gen_random_uuid() PRIMARY KEY,
    tenant_id      uuid          NOT NULL REFERENCES tenants(id),
    aggregate_type text          NOT NULL,
    aggregate_id   uuid          NOT NULL,
    event_type     text          NOT NULL,
    payload        jsonb         NOT NULL,
    created_at     timestamptz   NOT NULL DEFAULT now(),
    published_at   timestamptz,
    error          text
);

CREATE INDEX idx_outbox_unpublished
    ON outbox_events (created_at)
    WHERE published_at IS NULL;

BEGIN;

INSERT INTO catalog_menu_items (tenant_id, sku, name, price_cents)
VALUES ($1, $2, $3, $4)
RETURNING id INTO _item_id;

INSERT INTO outbox_events (tenant_id, aggregate_type, aggregate_id, event_type, payload)
VALUES (
    $1,
    'menu_item',
    _item_id,
    'catalog.menu_item.created',
    jsonb_build_object('sku', $2, 'name', $3, 'price_cents', $4)
);

COMMIT;