CREATE TABLE roles (
    slug   TEXT NOT NULL,
    tenant TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name   TEXT NOT NULL,
    caps   TEXT NOT NULL DEFAULT '[]',
    PRIMARY KEY (slug, tenant)
);

CREATE TABLE grants (
    tenant    TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    type      TEXT NOT NULL,
    principal TEXT NOT NULL,
    role      TEXT NOT NULL,
    path      TEXT NOT NULL DEFAULT '',

    -- TODO this seems odd
    -- When true, the grant applies system-wide (across all tenants).
    -- The tenant column is still required for ownership/audit.
    system BOOLEAN NOT NULL DEFAULT false,

    PRIMARY KEY (tenant, type, principal, role, path)
);

CREATE INDEX idx_grants_principal ON grants(type, principal);