| 1 | -- Moderation (lib/moderation.ts): suspended accounts, blocked users and reports to site administrators. |
| 2 | |
| 3 | -- A site administrator suspended this person: accounts.status is 'disabled' while it lasts (nothing signs in, |
| 4 | -- no token, session, agent or runner works) and their repositories, profile and conversations are hidden from |
| 5 | -- everyone else. Unlike disabling, suspending keeps their tokens, so unsuspending restores everything. NULL is not suspended. |
| 6 | ALTER TABLE accounts ADD COLUMN suspended_at TEXT; |
| 7 | ALTER TABLE accounts ADD COLUMN suspended_by TEXT; |
| 8 | ALTER TABLE accounts ADD COLUMN suspended_reason TEXT; |
| 9 | |
| 10 | -- A person, or an organization (by one of its owners), blocked a person: in the blocker's repositories the |
| 11 | -- blocked person can't open issues or pull requests, comment, review or react, and the two can't invite each other. |
| 12 | CREATE TABLE IF NOT EXISTS user_blocks ( |
| 13 | blocker_id TEXT NOT NULL REFERENCES accounts(id), |
| 14 | blocked_id TEXT NOT NULL REFERENCES accounts(id), |
| 15 | created_at TEXT NOT NULL, |
| 16 | created_by TEXT, -- the organization owner who blocked; the person themselves otherwise |
| 17 | PRIMARY KEY (blocker_id, blocked_id) |
| 18 | ); |
| 19 | CREATE INDEX IF NOT EXISTS user_blocks_blocked ON user_blocks(blocked_id); |
| 20 | |
| 21 | -- Reports of abusive content, the queue site administrators work through. `excerpt` keeps what was reported |
| 22 | -- as it was then, so editing or deleting it afterwards hides nothing from the administrators. |
| 23 | CREATE TABLE IF NOT EXISTS abuse_reports ( |
| 24 | id TEXT PRIMARY KEY, |
| 25 | reporter_id TEXT NOT NULL REFERENCES accounts(id), |
| 26 | kind TEXT NOT NULL, -- user, repository, issue, pull_request or comment |
| 27 | target TEXT NOT NULL, -- login, owner/name, owner/name#12, or owner/name#12 and the comment id |
| 28 | url TEXT NOT NULL, |
| 29 | account_id TEXT, -- the person reported, or who wrote the content |
| 30 | repo_id TEXT, |
| 31 | excerpt TEXT, |
| 32 | reason TEXT NOT NULL, |
| 33 | body TEXT NOT NULL DEFAULT '', |
| 34 | state TEXT NOT NULL DEFAULT 'open', -- open or resolved |
| 35 | created_at TEXT NOT NULL, |
| 36 | resolved_at TEXT, |
| 37 | resolved_by TEXT, |
| 38 | resolution TEXT |
| 39 | ); |
| 40 | CREATE INDEX IF NOT EXISTS abuse_reports_state ON abuse_reports(state, created_at); |
| 41 | CREATE INDEX IF NOT EXISTS abuse_reports_reporter ON abuse_reports(reporter_id, created_at); |