-- ============================================================================ -- 02 Accounts: members, advertisers, staff, roles, 2FA, sanctions, audit -- ============================================================================ -- ---------------------------------------------------------------------------- -- Members and advertisers (front-end users, guard: web / api) -- ---------------------------------------------------------------------------- CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, uuid uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE, username citext NOT NULL UNIQUE CHECK (username ~ '^[A-Za-z0-9_]{3,30}$'), username_changed_at timestamptz, display_name varchar(80) NOT NULL, email citext UNIQUE, phone varchar(20) UNIQUE, -- E.164, e.g. +2348012345678 password_hash varchar(255), -- null for social-login-only accounts google_id varchar(64) UNIQUE, email_verified_at timestamptz, phone_verified_at timestamptz, account_type varchar(12) NOT NULL DEFAULT 'member' CHECK (account_type IN ('member','advertiser')), status varchar(16) NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','banned','pending_deletion')), suspended_until timestamptz, state_id smallint REFERENCES states(id), lga_id integer REFERENCES lgas(id), locale varchar(8) NOT NULL DEFAULT 'en', -- en, pcm, ha, yo, ig trust_level smallint NOT NULL DEFAULT 0, -- 0 new, 1 regular, 2 trusted, 3 verified reporter two_factor_enabled boolean NOT NULL DEFAULT false, two_factor_reminders_sent integer NOT NULL DEFAULT 0, last_two_factor_reminder_at timestamptz, referral_code varchar(16) NOT NULL UNIQUE, referred_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, last_login_at timestamptz, last_login_ip inet, remember_token varchar(100), -- soft delete / undo-delete deleted_at timestamptz, deleted_by_type varchar(8) CHECK (deleted_by_type IN ('user','staff','system')), deleted_by_id bigint, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT users_contact_required CHECK (email IS NOT NULL OR phone IS NOT NULL) ); CREATE INDEX users_username_trgm ON users USING gin ((username::text) gin_trgm_ops); CREATE INDEX users_display_name_trgm ON users USING gin (display_name gin_trgm_ops); CREATE INDEX users_status_idx ON users (status) WHERE deleted_at IS NULL; CREATE INDEX users_state_idx ON users (state_id, lga_id); CREATE INDEX users_deleted_idx ON users (purge_after) WHERE deleted_at IS NOT NULL; ALTER TABLE towns ADD CONSTRAINT towns_created_by_fk FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL; CREATE TABLE user_profiles ( user_id bigint PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE, bio varchar(500), avatar_path varchar(255), cover_path varchar(255), website_url varchar(255), -- optional, user-supplied, used for ad relevance only with consent (NDPA) interests jsonb NOT NULL DEFAULT '[]'::jsonb, age_range varchar(10) CHECK (age_range IN ('13-17','18-24','25-34','35-44','45-54','55+')), gender varchar(16) CHECK (gender IN ('female','male','other','prefer_not')), ads_personalization_consent boolean NOT NULL DEFAULT false, consent_recorded_at timestamptz, notification_prefs jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); -- Usernames: history (old profile URLs keep redirecting) and a reserved list CREATE TABLE username_history ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, old_username citext NOT NULL, changed_at timestamptz NOT NULL DEFAULT now(), redirect_until timestamptz NOT NULL ); CREATE INDEX username_history_old_idx ON username_history (old_username, redirect_until); CREATE TABLE reserved_usernames ( username citext PRIMARY KEY, reason varchar(60) NOT NULL DEFAULT 'system', -- system, office, public_figure, brand created_at timestamptz NOT NULL DEFAULT now() ); -- ---------------------------------------------------------------------------- -- Staff (guard: staff, signs in only at cytadel.absurdities.ng/login) -- ---------------------------------------------------------------------------- CREATE TABLE staff_users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, uuid uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE, name varchar(100) NOT NULL, email citext NOT NULL UNIQUE, phone varchar(20), password_hash varchar(255) NOT NULL, role varchar(20) NOT NULL CHECK (role IN ('admin','manager','supervisor','finance_officer','ad_reviewer','legal_officer')), is_primary boolean NOT NULL DEFAULT false, -- the first Admin ("Super Admin") status varchar(12) NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended')), suspended_until timestamptz, must_change_password boolean NOT NULL DEFAULT true, password_changed_at timestamptz, two_factor_confirmed_at timestamptz, -- null = must set up 2FA before anything else allowed_ips inet[], -- optional per-staff IP allowlist failed_login_count integer NOT NULL DEFAULT 0, locked_until timestamptz, last_login_at timestamptz, last_login_ip inet, remember_token varchar(100), created_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, deleted_at timestamptz, deleted_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT primary_must_be_admin CHECK (is_primary = false OR role = 'admin') ); -- Only one primary admin can ever exist CREATE UNIQUE INDEX staff_one_primary ON staff_users (is_primary) WHERE is_primary = true; CREATE INDEX staff_role_idx ON staff_users (role) WHERE deleted_at IS NULL; ALTER TABLE site_settings ADD CONSTRAINT site_settings_staff_fk FOREIGN KEY (updated_by_staff_id) REFERENCES staff_users(id) ON DELETE SET NULL; -- Business rules enforced in the database as a safety net (the app enforces them too): -- 1. At most 2 active (non-deleted) Admins. -- 2. The primary Admin can never be deleted, demoted or have the primary flag removed. CREATE OR REPLACE FUNCTION enforce_staff_rules() RETURNS trigger AS $$ DECLARE admin_count integer; BEGIN IF TG_OP = 'DELETE' THEN IF OLD.is_primary THEN RAISE EXCEPTION 'The primary Admin cannot be deleted'; END IF; RETURN OLD; END IF; IF TG_OP = 'UPDATE' AND OLD.is_primary THEN IF NEW.deleted_at IS NOT NULL THEN RAISE EXCEPTION 'The primary Admin cannot be deleted'; END IF; IF NEW.is_primary = false OR NEW.role <> 'admin' THEN RAISE EXCEPTION 'The primary Admin cannot be demoted'; END IF; END IF; IF NEW.role = 'admin' AND NEW.deleted_at IS NULL THEN SELECT count(*) INTO admin_count FROM staff_users WHERE role = 'admin' AND deleted_at IS NULL AND id IS DISTINCT FROM NEW.id; IF admin_count >= 2 THEN RAISE EXCEPTION 'The platform can have at most 2 Admins'; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER staff_rules_biud BEFORE INSERT OR UPDATE OR DELETE ON staff_users FOR EACH ROW EXECUTE FUNCTION enforce_staff_rules(); -- Permission catalogue and role matrix. Staff never get permissions directly; -- changing what a role can do is a data change, not a code change. CREATE TABLE permissions ( code varchar(60) PRIMARY KEY, area varchar(30) NOT NULL, description varchar(200) NOT NULL ); CREATE TABLE role_permissions ( role varchar(20) NOT NULL CHECK (role IN ('admin','manager','supervisor','finance_officer','ad_reviewer','legal_officer')), permission_code varchar(60) NOT NULL REFERENCES permissions(code) ON DELETE CASCADE, PRIMARY KEY (role, permission_code) ); -- ---------------------------------------------------------------------------- -- Two-factor authentication (shared by users and staff) -- ---------------------------------------------------------------------------- CREATE TABLE two_factor_secrets ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_type varchar(5) NOT NULL CHECK (account_type IN ('user','staff')), account_id bigint NOT NULL, method varchar(10) NOT NULL DEFAULT 'totp' CHECK (method IN ('totp','email','sms')), secret_encrypted text NOT NULL, -- encrypted with the application key, never plain confirmed_at timestamptz, last_used_step bigint, -- blocks re-use of the same TOTP code created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (account_type, account_id, method) ); CREATE TABLE two_factor_recovery_codes ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_type varchar(5) NOT NULL CHECK (account_type IN ('user','staff')), account_id bigint NOT NULL, code_hash varchar(255) NOT NULL, -- hashed, shown to the person only once used_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX two_factor_recovery_account_idx ON two_factor_recovery_codes (account_type, account_id); CREATE TABLE trusted_devices ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_type varchar(5) NOT NULL CHECK (account_type IN ('user','staff')), account_id bigint NOT NULL, token_hash char(64) NOT NULL UNIQUE, -- sha256 of the cookie/device token label varchar(100), ip inet, user_agent varchar(255), expires_at timestamptz NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX trusted_devices_account_idx ON trusted_devices (account_type, account_id); -- ---------------------------------------------------------------------------- -- Login hardening -- ---------------------------------------------------------------------------- CREATE TABLE login_attempts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_type varchar(5) NOT NULL CHECK (account_type IN ('user','staff')), identifier varchar(255) NOT NULL, -- email, phone or username as typed ip inet, user_agent varchar(255), successful boolean NOT NULL, failure_reason varchar(40), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX login_attempts_lookup_idx ON login_attempts (account_type, identifier, created_at DESC); CREATE INDEX login_attempts_ip_idx ON login_attempts (ip, created_at DESC); CREATE TABLE one_time_codes ( -- email/SMS verification and password reset OTPs id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_type varchar(5) NOT NULL CHECK (account_type IN ('user','staff')), account_id bigint NOT NULL, purpose varchar(24) NOT NULL CHECK (purpose IN ('verify_email','verify_phone','login_2fa','password_reset','sensitive_action')), code_hash varchar(255) NOT NULL, attempts smallint NOT NULL DEFAULT 0, expires_at timestamptz NOT NULL, consumed_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX one_time_codes_lookup_idx ON one_time_codes (account_type, account_id, purpose, expires_at); -- ---------------------------------------------------------------------------- -- Sanctions: timed suspension or permanent ban, with appeal trail -- ---------------------------------------------------------------------------- CREATE TABLE user_sanctions ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, type varchar(12) NOT NULL CHECK (type IN ('suspension','ban')), reason text NOT NULL, rule_reference varchar(60), -- which published rule was broken evidence jsonb NOT NULL DEFAULT '[]'::jsonb, starts_at timestamptz NOT NULL DEFAULT now(), ends_at timestamptz, -- null for a permanent ban issued_by_staff_id bigint NOT NULL REFERENCES staff_users(id), revoked_at timestamptz, revoked_by_staff_id bigint REFERENCES staff_users(id), revoke_reason text, appeal_status varchar(12) NOT NULL DEFAULT 'none' CHECK (appeal_status IN ('none','pending','upheld','overturned')), appeal_text text, appeal_decided_by_staff_id bigint REFERENCES staff_users(id), created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT ban_has_no_end CHECK (type = 'suspension' OR ends_at IS NULL), CONSTRAINT suspension_has_end CHECK (type = 'ban' OR ends_at IS NOT NULL) ); CREATE INDEX user_sanctions_user_idx ON user_sanctions (user_id, created_at DESC); -- ---------------------------------------------------------------------------- -- Audit: append-only record of staff and sensitive actions -- ---------------------------------------------------------------------------- CREATE TABLE audit_logs ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, actor_type varchar(8) NOT NULL CHECK (actor_type IN ('user','staff','system')), actor_id bigint, action varchar(80) NOT NULL, -- e.g. user.suspend, staff.create, post.restore subject_type varchar(40), subject_id bigint, metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ip inet, user_agent varchar(255), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX audit_logs_actor_idx ON audit_logs (actor_type, actor_id, created_at DESC); CREATE INDEX audit_logs_subject_idx ON audit_logs (subject_type, subject_id); CREATE INDEX audit_logs_action_idx ON audit_logs (action, created_at DESC); CREATE OR REPLACE FUNCTION audit_logs_immutable() RETURNS trigger AS $$ BEGIN RAISE EXCEPTION 'audit_logs is append-only'; END; $$ LANGUAGE plpgsql; CREATE TRIGGER audit_logs_no_change BEFORE UPDATE OR DELETE ON audit_logs FOR EACH ROW EXECUTE FUNCTION audit_logs_immutable(); -- Every time a Supervisor (or anyone) views personal data, it is recorded here. CREATE TABLE sensitive_access_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, staff_id bigint NOT NULL REFERENCES staff_users(id), subject_type varchar(40) NOT NULL, subject_id bigint NOT NULL, fields_viewed text[] NOT NULL, reason varchar(200), ip inet, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX sensitive_access_staff_idx ON sensitive_access_log (staff_id, created_at DESC); CREATE TRIGGER users_updated BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER user_profiles_updated BEFORE UPDATE ON user_profiles FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER staff_users_updated BEFORE UPDATE ON staff_users FOR EACH ROW EXECUTE FUNCTION set_updated_at();