-- security_authz: 2026-10 audit fixes.
-- AZ-A1: display names end up in git idents; strip `<`, `>`, CR and LF from existing rows (new writes are
-- sanitized in code) and blank out names that were nothing but crud.
UPDATE accounts
SET display_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(display_name, '<', ''), '>', ''), char(13), ' '), char(10), ' '))
WHERE display_name IS NOT NULL
AND (display_name LIKE '%<%' OR display_name LIKE '%>%' OR display_name LIKE '%' || char(10) || '%'
OR display_name LIKE '%' || char(13) || '%');
UPDATE accounts SET display_name = NULL WHERE display_name IS NOT NULL AND TRIM(display_name) = '';
-- AZ-A2: profile and repository websites are rendered as hrefs; only absolute http(s) URLs survive.
UPDATE accounts SET website = NULL
WHERE website IS NOT NULL AND LOWER(website) NOT LIKE 'http://%' AND LOWER(website) NOT LIKE 'https://%';
UPDATE repos SET website = NULL
WHERE website IS NOT NULL AND LOWER(website) NOT LIKE 'http://%' AND LOWER(website) NOT LIKE 'https://%';
-- AZ-A8 / AZ-A19: org-level settings. `org_default_permission` is what a plain member gets on every
-- repository the org owns (owners always get admin); `members_visibility` hides the roster from non-members.
ALTER TABLE accounts ADD COLUMN org_default_permission TEXT NOT NULL DEFAULT 'read'
CHECK (org_default_permission IN ('none', 'read', 'write'));
ALTER TABLE accounts ADD COLUMN members_visibility TEXT NOT NULL DEFAULT 'public'
CHECK (members_visibility IN ('public', 'members'));
-- AZ-A8: nobody joins an organization without accepting an invitation.
CREATE TABLE org_invitations (
id TEXT PRIMARY KEY,
org_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
role TEXT NOT NULL CHECK (role IN ('owner', 'member')),
invited_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
UNIQUE (org_id, user_id)
);
CREATE INDEX org_invitations_user ON org_invitations(user_id, expires_at);