internal/store/migrations/0029_mr_resolution.up.sql

 1-- Who resolved a merge request and when. The state alone cannot say it:
 2-- updated_at moves for every edit, and the actor only survived in the
 3-- events table.
 4ALTER TABLE merge_requests ADD COLUMN merged_at TEXT NOT NULL DEFAULT '';
 5ALTER TABLE merge_requests ADD COLUMN merged_by INTEGER REFERENCES users(id) ON DELETE SET NULL;
 6ALTER TABLE merge_requests ADD COLUMN closed_at TEXT NOT NULL DEFAULT '';
 7ALTER TABLE merge_requests ADD COLUMN closed_by INTEGER REFERENCES users(id) ON DELETE SET NULL;
 8
 9-- Backfill from the mr.merged events already recorded. json_extract is
10-- available in the SQLite builds gitbay links; the payload is
11-- {"number":N,"sha":"..."} written by the merge path.
12UPDATE merge_requests SET
13    merged_at = COALESCE((
14        SELECT e.created_at FROM events e
15        WHERE e.repo_id = merge_requests.repo_id AND e.kind = 'mr.merged'
16          AND json_extract(e.data_json, '$.number') = merge_requests.number
17        ORDER BY e.id DESC LIMIT 1), ''),
18    merged_by = (
19        SELECT e.actor_id FROM events e
20        WHERE e.repo_id = merge_requests.repo_id AND e.kind = 'mr.merged'
21          AND json_extract(e.data_json, '$.number') = merge_requests.number
22        ORDER BY e.id DESC LIMIT 1)
23WHERE state = 'merged';