Files

74 lines
3.0 KiB
SQL

-- sys_audit is the field-level trail for settings resources and access
-- policies. It had no org column: the org lived inside record_key, in a shape
-- that differed per writer, so no caller could read one org's trail without
-- knowing every writer's key grammar. org_id makes the org a first-class
-- column and the indexes make an org-scoped, time-ordered page cheap.
-- +goose Up
ALTER TABLE sys_audit ADD COLUMN org_id text NOT NULL DEFAULT 'org-sys';
ALTER TABLE sys_audit ADD CONSTRAINT sys_audit_org_nonempty CHECK (org_id <> '');
-- Backfill from the two key grammars written before this migration.
-- resources: bare kind for org-sys, <org>/<kind> otherwise.
-- access_policies: <level>/<org>/<workspace>/<objectKind>/<objectID>/<policyID>;
-- instance-level policies carry no org; reset rows are reset/instance or
-- reset/org/<org>.
--
-- record_key was never rewritten by the org rename, so rows written while the
-- default org was still called "default" carry that dead id. They belong to
-- org-sys, which is what the rename made the same org.
UPDATE sys_audit SET org_id = 'org-sys'
WHERE table_name = 'access_policies'
AND (split_part(record_key, '/', 2) = 'default' OR record_key = 'reset/org/default');
-- Org ids may themselves contain '/', which the old validation allowed. Match
-- the longest existing org prefix rather than the first path segment, so
-- acme/dev never resolves to acme.
UPDATE sys_audit a SET org_id = (
SELECT o.id FROM orgs o
WHERE a.record_key LIKE o.id || '/%'
ORDER BY length(o.id) DESC
LIMIT 1
)
WHERE a.table_name = 'resources'
AND a.org_id = 'org-sys'
AND EXISTS (SELECT 1 FROM orgs o WHERE a.record_key LIKE o.id || '/%');
UPDATE sys_audit a SET org_id = (
SELECT o.id FROM orgs o
WHERE a.record_key LIKE split_part(a.record_key, '/', 1) || '/' || o.id || '/%'
ORDER BY length(o.id) DESC
LIMIT 1
)
WHERE a.table_name = 'access_policies'
AND a.org_id = 'org-sys'
AND split_part(a.record_key, '/', 1) IN ('org', 'workspace', 'object')
AND EXISTS (
SELECT 1 FROM orgs o
WHERE a.record_key LIKE split_part(a.record_key, '/', 1) || '/' || o.id || '/%'
);
UPDATE sys_audit a SET org_id = o.id
FROM orgs o
WHERE a.table_name = 'access_policies'
AND a.org_id = 'org-sys'
AND a.record_key = 'reset/org/' || o.id;
CREATE INDEX idx_sys_audit_org_at ON sys_audit (org_id, at DESC, id DESC);
CREATE INDEX idx_sys_audit_org_record ON sys_audit (org_id, table_name, record_key, at DESC, id DESC);
CREATE INDEX idx_events_org_at ON events (org_id, at DESC, id DESC);
CREATE INDEX idx_identity_events_at ON identity_events (at DESC, id DESC);
CREATE INDEX idx_access_denials_org_minute ON access_denials (org_id, minute_at DESC, id DESC);
CREATE INDEX idx_access_denials_minute ON access_denials (minute_at, id);
-- +goose Down
DROP INDEX idx_access_denials_minute;
DROP INDEX idx_access_denials_org_minute;
DROP INDEX idx_identity_events_at;
DROP INDEX idx_events_org_at;
DROP INDEX idx_sys_audit_org_record;
DROP INDEX idx_sys_audit_org_at;
ALTER TABLE sys_audit DROP CONSTRAINT sys_audit_org_nonempty;
ALTER TABLE sys_audit DROP COLUMN org_id;