// moderation.go is the forum's pre-commit gate (0001 §8). A staged post
// exists only here: approval BIRTHS the artifact (zit forum post runs
// then), rejection means it never existed — so nothing ever needs
// un-syncing and the artifact layer is untouched. Posts that arrived by
// sync are already content; for those, moderation is display state
// (hidden, with a public reason), never deletion.
//
// The moderation log is append-only BY CONSTRUCTION: this file exposes
// no update or delete for it, and it is public — Lobsters' rule, the
// same honesty invariant the rest of quarry keeps.
package oltp
import (
"database/sql"
"errors"
"time"
)
const moderationSchema = `
CREATE TABLE IF NOT EXISTS forum_moderation (
repo TEXT PRIMARY KEY,
enabled INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS forum_staged (
id INTEGER PRIMARY KEY AUTOINCREMENT,
repo TEXT NOT NULL,
kind TEXT NOT NULL, -- 'post' | 'reply'
root TEXT NOT NULL DEFAULT '', -- reply target (thread root hash)
title TEXT NOT NULL DEFAULT '',
body TEXT NOT NULL,
mimetype TEXT NOT NULL DEFAULT '',
author TEXT NOT NULL,
at TEXT NOT NULL,
state TEXT NOT NULL DEFAULT 'pending', -- pending | approved | rejected
-- Advisory hook verdict; a human always outranks it.
ai_action TEXT NOT NULL DEFAULT '',
ai_score REAL NOT NULL DEFAULT 0,
ai_reason TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS forum_staged_by_state ON forum_staged (repo, state, at);
CREATE TABLE IF NOT EXISTS forum_modlog (
repo TEXT NOT NULL,
at TEXT NOT NULL,
actor TEXT NOT NULL, -- an account, or 'hook' for automated actions
action TEXT NOT NULL, -- approve | reject | hide | unhide | enable | disable | ai-*
target TEXT NOT NULL, -- what it acted on: a title, post hash, or staged id
reason TEXT NOT NULL DEFAULT '',
-- author is the account the action was ABOUT, which is not the actor:
-- an entry is "moderator X rejected Y's post". Recorded because the
-- log could not answer "is this about me?" without it — Target is a
-- staged id or a post hash, and an approved post has already left
-- the staged table, so no join recovers whose it was.
author TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS forum_modlog_by_repo ON forum_modlog (repo, at);
-- forum_modlog_by_author is NOT here: it names a migrated column, and a
-- schema CREATE INDEX runs before the ALTER that adds it, so an old
-- database would fail to open. It lives in moderationMigrations, after
-- the column it indexes. TestSchemaIndexesNeverNameAMigratedColumn.
CREATE TABLE IF NOT EXISTS forum_hidden (
repo TEXT NOT NULL,
post TEXT NOT NULL,
reason TEXT NOT NULL,
actor TEXT NOT NULL,
at TEXT NOT NULL,
PRIMARY KEY (repo, post)
);`
// Staged is one held submission.
type Staged struct {
ID int64 `json:"id"`
Repo string `json:"repo"`
Kind string `json:"kind"`
Root string `json:"root"`
Title string `json:"title"`
Body string `json:"body"`
Mimetype string `json:"mimetype"`
Author string `json:"author"`
At string `json:"at"`
State string `json:"state"`
AIAction string `json:"aiAction"`
AIScore float64 `json:"aiScore"`
AIReason string `json:"aiReason"`
// Reason is the MODERATOR's reason, carried only on a rejected row
// read by its own author. It lives in the public modlog, which is
// where a rejection is recorded; this copies it onto the one row
// whose author is entitled to be told without going to look.
Reason string `json:"reason,omitempty"`
}
// ModEntry is one public moderation-log line.
// moderationMigrations widens a log written before `author` existed.
//
// The column lands empty on every existing row, which is the honest
// backfill: those entries were written when nothing knew the author, and
// inventing one from Target would guess. They stay visible to a
// moderator and to nobody else — which is what the log being public
// WAS, so nothing an author could see before is lost. Every author
// already has their own reason on their own row (StagedMine reads it
// through target, not through this column).
var moderationMigrations = []string{
`ALTER TABLE forum_modlog ADD COLUMN author TEXT NOT NULL DEFAULT ''`,
`CREATE INDEX IF NOT EXISTS forum_modlog_by_author ON forum_modlog (repo, author, at)`,
}
// ModEntry is one moderation action: who did it, what they did, and who
// it was done to.
type ModEntry struct {
At string `json:"at"`
Actor string `json:"actor"`
Action string `json:"action"`
Target string `json:"target"`
Reason string `json:"reason"`
// Author is the account the action was about, empty on entries
// written before the column existed and on actions with no subject
// (changing the moderation setting is about the repository).
Author string `json:"author,omitempty"`
}
func now() string { return time.Now().UTC().Format(time.RFC3339) }
// SetModeration switches the pre-commit gate for a repo.
func (d *DB) SetModeration(repo string, enabled bool) error {
on := 0
if enabled {
on = 1
}
_, err := d.w.Exec(`INSERT INTO forum_moderation (repo, enabled) VALUES (?, ?)
ON CONFLICT (repo) DO UPDATE SET enabled = excluded.enabled`, repo, on)
return err
}
// ModerationEnabled reports whether the gate is on.
func (d *DB) ModerationEnabled(repo string) bool {
var on int
err := d.db.QueryRow(`SELECT enabled FROM forum_moderation WHERE repo = ?`, repo).Scan(&on)
return err == nil && on == 1
}
// StagePost holds a submission for review and returns its queue id.
func (d *DB) StagePost(repo, kind, root, title, body, mimetype, author string) (int64, error) {
res, err := d.w.Exec(`INSERT INTO forum_staged (repo, kind, root, title, body, mimetype, author, at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, repo, kind, root, title, body, mimetype, author, now())
if err != nil {
return 0, err
}
return res.LastInsertId()
}
// StagedPending lists a repo's queue, oldest first.
func (d *DB) StagedPending(repo string) ([]Staged, error) {
rows, err := d.db.Query(`SELECT id, repo, kind, root, title, body, mimetype, author, at, state,
ai_action, ai_score, ai_reason
FROM forum_staged WHERE repo = ? AND state = 'pending' ORDER BY at, id`, repo)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Staged
for rows.Next() {
var s Staged
if err := rows.Scan(&s.ID, &s.Repo, &s.Kind, &s.Root, &s.Title, &s.Body, &s.Mimetype,
&s.Author, &s.At, &s.State, &s.AIAction, &s.AIScore, &s.AIReason); err != nil {
return nil, err
}
out = append(out, s)
}
return out, rows.Err()
}
// StagedMine lists one account's own pending submissions in a repo,
// oldest first.
//
// The author is the one reader who must always be able to see their own
// held content, and today they are the one reader who cannot. A staged
// submission lives in forum_staged and never in the DAG, and every
// forum surface reads the DAG -- so the author's post is invisible to
// them BY CONSTRUCTION rather than by any filter somebody wrote. That
// is why the silence is total: there was no code path that decided to
// hide it, so there was none to soften either.
//
// jva measured the whole of it from a non-moderator account (G-118):
// after `staged: true` the post is absent from forum.list, absent from
// the forum page, and the queue answers 403 -- and the rejection REASON
// is published in a modlog the author is never pointed at. An author
// who sees less than a stranger does is the defect, in this path and in
// the attachment one (G-119).
//
// REJECTED rows are returned too, and that is the second half of the
// same defect. Approval announces itself — the post appears — but a
// rejection removed the row from this listing and told the author
// nothing, so their own text vanished without a word and the REASON sat
// in a public modlog they were never pointed at. A stranger reading the
// log learned why; the author did not. The body is still in
// forum_staged, so returning the row also gives them their writing back.
//
// An APPROVED row is not returned: the post itself is the answer, and
// repeating it here would show every author a permanent list of
// everything they have ever written.
//
// Scoped to ONE author by the query itself rather than by a caller's
// filter: a queue read is the moderation letter's business, and this
// must not become a second way to read it. The modlog join is scoped
// the same way — it rides on a row this author already owns.
func (d *DB) StagedMine(repo, author string) ([]Staged, error) {
if author == "" {
return nil, nil
}
rows, err := d.db.Query(`SELECT s.id, s.repo, s.kind, s.root, s.title, s.body, s.mimetype,
s.author, s.at, s.state, s.ai_action, s.ai_score, s.ai_reason,
COALESCE((SELECT m.reason FROM forum_modlog m
WHERE m.repo = s.repo AND m.action = 'reject'
AND m.target = 'staged:' || s.id
ORDER BY m.at DESC, m.rowid DESC LIMIT 1), '')
FROM forum_staged s
WHERE s.repo = ? AND s.author = ?
AND (s.state = 'pending' OR s.state = 'rejected')
ORDER BY s.at, s.id`, repo, author)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Staged
for rows.Next() {
var s Staged
if err := rows.Scan(&s.ID, &s.Repo, &s.Kind, &s.Root, &s.Title, &s.Body, &s.Mimetype,
&s.Author, &s.At, &s.State, &s.AIAction, &s.AIScore, &s.AIReason, &s.Reason); err != nil {
return nil, err
}
out = append(out, s)
}
return out, rows.Err()
}
// StagedByID fetches one queue entry.
func (d *DB) StagedByID(repo string, id int64) (Staged, bool, error) {
var s Staged
err := d.db.QueryRow(`SELECT id, repo, kind, root, title, body, mimetype, author, at, state,
ai_action, ai_score, ai_reason
FROM forum_staged WHERE repo = ? AND id = ?`, repo, id).
Scan(&s.ID, &s.Repo, &s.Kind, &s.Root, &s.Title, &s.Body, &s.Mimetype,
&s.Author, &s.At, &s.State, &s.AIAction, &s.AIScore, &s.AIReason)
if errors.Is(err, sql.ErrNoRows) {
return Staged{}, false, nil
}
return s, err == nil, err
}
// ResolveStaged moves pending → approved|rejected. It refuses an entry
// that is not pending, so two moderators racing the same submission
// release it exactly once.
func (d *DB) ResolveStaged(repo string, id int64, state string) error {
res, err := d.w.Exec(`UPDATE forum_staged SET state = ?
WHERE repo = ? AND id = ? AND state = 'pending'`, state, repo, id)
if err != nil {
return err
}
n, err := res.RowsAffected()
if err != nil {
return err
}
if n == 0 {
return errors.New("oltp: staged entry is not pending")
}
return nil
}
// ReopenStaged puts an approved-but-unreleased entry back in the queue
// — the artifact write failed after the claim, and a person must see it
// rather than it rotting approved-but-absent.
func (d *DB) ReopenStaged(repo string, id int64) error {
_, err := d.w.Exec(`UPDATE forum_staged SET state = 'pending'
WHERE repo = ? AND id = ? AND state = 'approved'`, repo, id)
return err
}
// SetStagedAI attaches the hook's advisory verdict.
func (d *DB) SetStagedAI(repo string, id int64, action string, score float64, reason string) error {
_, err := d.w.Exec(`UPDATE forum_staged SET ai_action = ?, ai_score = ?, ai_reason = ?
WHERE repo = ? AND id = ?`, action, score, reason, repo, id)
return err
}
// AppendModLog records one public moderation action. There is no way to
// edit or remove an entry.
func (d *DB) AppendModLog(repo, actor, action, target, reason, about string) error {
_, err := d.w.Exec(`INSERT INTO forum_modlog (repo, at, actor, action, target, reason, author)
VALUES (?, ?, ?, ?, ?, ?, ?)`, repo, now(), actor, action, target, reason, about)
return err
}
// ModLog lists a repo's moderation actions, newest first: the moderator's
// view, which is every entry.
func (d *DB) ModLog(repo string, limit int) ([]ModEntry, error) {
return d.modLog(limit, `WHERE repo = ?`, repo)
}
// ModLogAbout lists only the entries ABOUT one account: an author's view
// of their own history.
//
// The filter is on `author`, which is the account the action was done to
// — not the actor, who is the moderator. Empty values never match, so an
// author sees nothing from entries written before the column existed, and
// an action about the repository rather than a person is a moderator
// entry only.
func (d *DB) ModLogAbout(repo, about string, limit int) ([]ModEntry, error) {
return d.modLog(limit, `WHERE repo = ? AND author <> '' AND author = ?`, repo, about)
}
// modLog is the one SELECT behind both views.
//
// One query rather than two, because the two answers differing was the
// whole of 7e905225b0fe's neighbour problem in miniature: the log's
// every-row and one-row shapes must agree about what an entry IS, or the
// author and the moderator are reading different tables that happen to
// share a name.
func (d *DB) modLog(limit int, where string, args ...any) ([]ModEntry, error) {
q := `SELECT at, actor, action, target, reason, author FROM forum_modlog ` +
where + ` ORDER BY at DESC, rowid DESC LIMIT ?`
args = append(args, limit)
rows, err := d.db.Query(q, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ModEntry
for rows.Next() {
var e ModEntry
if err := rows.Scan(&e.At, &e.Actor, &e.Action, &e.Target, &e.Reason, &e.Author); err != nil {
return nil, err
}
out = append(out, e)
}
return out, rows.Err()
}
// HidePost collapses a synced-in post behind its reason; UnhidePost
// restores it. Both are display state — the artifact is never touched.
func (d *DB) HidePost(repo, post, reason, actor string) error {
_, err := d.w.Exec(`INSERT INTO forum_hidden (repo, post, reason, actor, at)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT (repo, post) DO UPDATE SET reason = excluded.reason, actor = excluded.actor, at = excluded.at`,
repo, post, reason, actor, now())
return err
}
func (d *DB) UnhidePost(repo, post string) error {
_, err := d.w.Exec(`DELETE FROM forum_hidden WHERE repo = ? AND post = ?`, repo, post)
return err
}
// HiddenPosts returns post → reason for one repo.
func (d *DB) HiddenPosts(repo string) (map[string]string, error) {
rows, err := d.db.Query(`SELECT post, reason FROM forum_hidden WHERE repo = ?`, repo)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[string]string{}
for rows.Next() {
var p, r string
if err := rows.Scan(&p, &r); err != nil {
return nil, err
}
out[p] = r
}
return out, rows.Err()
}