41 lines
1.4 KiB
PL/PgSQL
41 lines
1.4 KiB
PL/PgSQL
-- User management hardening: production-grade admin CRUD (spec §7 user admin).
|
|
-- Adds soft delete, disable toggle, audit timestamps, and an auto-updating
|
|
-- timestamp trigger so the admin user list reflects the last mutation without
|
|
-- every query re-deriving it from audit_logs.
|
|
|
|
-- Per-row lifecycle markers on the users table.
|
|
ALTER TABLE users
|
|
ADD COLUMN disabled boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN deleted_at timestamptz,
|
|
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
|
|
|
|
-- updated_at auto-trigger, shared by any table that carries the column.
|
|
CREATE OR REPLACE FUNCTION felis_set_updated_at()
|
|
RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION felis_set_updated_at();
|
|
|
|
-- quotas gains its own audit timestamp so an admin change (including who made it)
|
|
-- is visible downstream.
|
|
ALTER TABLE quotas
|
|
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now(),
|
|
ADD COLUMN updated_by text;
|
|
|
|
CREATE TRIGGER trg_quotas_updated_at
|
|
BEFORE UPDATE ON quotas
|
|
FOR EACH ROW EXECUTE FUNCTION felis_set_updated_at();
|
|
|
|
-- Efficient lookups for the admin user list: filter by role and exclude
|
|
-- soft-deleted rows in one pass.
|
|
CREATE INDEX idx_users_role_active ON users (role)
|
|
WHERE deleted_at IS NULL AND disabled = false;
|
|
CREATE INDEX idx_users_deleted ON users (deleted_at)
|
|
WHERE deleted_at IS NOT NULL;
|