Multi-tenant schema: preventing cross-tenant references with a composite FK

-- ======================================================================
-- Q: Multi-tenant schema for assets and alerts: how do you ensure that an alert
-- does not point to another tenant's asset?
--
-- The composite foreign key (tenant_id, asset_id) makes it impossible in the
-- database to link an alert to another tenant's asset, even if the code has a
-- bug. tenant_id goes first in all indexes. RLS is a second line of defense: the
-- application runs SET LOCAL app.tenant_id at the start of every transaction.
-- ======================================================================

CREATE TABLE assets (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES tenants(id),
    hostname  text   NOT NULL,
    UNIQUE (tenant_id, hostname),
    UNIQUE (tenant_id, id)              -- target for the composite FK
);

CREATE TABLE alerts (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint NOT NULL,
    asset_id   bigint NOT NULL,
    severity   smallint NOT NULL CHECK (severity BETWEEN 1 AND 5),
    status     text NOT NULL DEFAULT 'open' CHECK (status IN ('open','ack','closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY (tenant_id, asset_id) REFERENCES assets (tenant_id, id)
);

-- Defense in depth: Row Level Security
ALTER TABLE alerts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON alerts
    USING (tenant_id = current_setting('app.tenant_id')::bigint);