# User Reports Implementation Plan > **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task. **Goal:** Let authenticated users report accounts/records by selecting a label, and give admins a queue to review and act on those reports. **Architecture:** New `_reports` SQLite table + db helper functions, a `dev.hatk.createReport` XRPC endpoint registered as a core handler, three admin REST endpoints in server.ts, and a new "Reports" tab in admin.html. **Tech Stack:** TypeScript, SQLite (better-sqlite3), vanilla HTML/CSS/JS admin UI --- ### Task 1: Create the `_reports` table in database init **Files:** - Modify: `packages/hatk/src/database/db.ts:67-127` (inside `initDatabase`, after the `_preferences` table creation) **Step 1: Add the table creation SQL** After the `_preferences` table block (line ~123) and before the OAuth DDL line, add: ```typescript // Reports table (user-submitted moderation reports) await run(`CREATE TABLE IF NOT EXISTS _reports ( id INTEGER PRIMARY KEY AUTOINCREMENT, subject_uri TEXT NOT NULL, subject_did TEXT NOT NULL, label TEXT NOT NULL, reason TEXT, reported_by TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'open', resolved_by TEXT, resolved_at TEXT, created_at TEXT NOT NULL )`) await run(`CREATE INDEX IF NOT EXISTS idx_reports_status ON _reports(status)`) await run(`CREATE INDEX IF NOT EXISTS idx_reports_subject_uri ON _reports(subject_uri)`) ``` **Step 2: Verify the app boots** Run: `cd packages/hatk && npx vite build` Expected: Build succeeds --- ### Task 2: Add report database helper functions **Files:** - Modify: `packages/hatk/src/database/db.ts` (add new exported functions at the end, before the final closing brace or after the last export) **Step 1: Add the helper functions** Add these exports to `db.ts`: ```typescript export async function insertReport(report: { subjectUri: string subjectDid: string label: string reason?: string reportedBy: string }): Promise<{ id: number }> { const createdAt = new Date().toISOString() await run( `INSERT INTO _reports (subject_uri, subject_did, label, reason, reported_by, created_at) VALUES ($1, $2, $3, $4, $5, $6)`, [report.subjectUri, report.subjectDid, report.label, report.reason || null, report.reportedBy, createdAt], ) const rows = await all<{ id: number }>(`SELECT last_insert_rowid() as id`) return { id: rows[0].id } } export async function queryReports(opts: { status?: string label?: string limit?: number offset?: number }): Promise<{ reports: any[]; total: number }> { const conditions: string[] = [] const params: unknown[] = [] let idx = 1 if (opts.status) { conditions.push(`r.status = $${idx++}`) params.push(opts.status) } if (opts.label) { conditions.push(`r.label = $${idx++}`) params.push(opts.label) } const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '' const limit = opts.limit || 50 const offset = opts.offset || 0 const countRows = await all<{ count: number }>(`SELECT COUNT(*) as count FROM _reports r ${where}`, params) const total = Number(countRows[0]?.count || 0) const rows = await all( `SELECT r.*, rp.handle as reported_by_handle FROM _reports r LEFT JOIN _repos rp ON r.reported_by = rp.did ${where} ORDER BY r.created_at DESC LIMIT $${idx++} OFFSET $${idx++}`, [...params, limit, offset], ) return { reports: rows, total } } export async function resolveReport( id: number, action: 'resolved' | 'dismissed', resolvedBy: string, ): Promise<{ subjectUri: string; label: string } | null> { const rows = await all<{ subject_uri: string; label: string; status: string }>( `SELECT subject_uri, label, status FROM _reports WHERE id = $1`, [id], ) if (!rows.length) return null if (rows[0].status !== 'open') return null await run(`UPDATE _reports SET status = $1, resolved_by = $2, resolved_at = $3 WHERE id = $4`, [ action, resolvedBy, new Date().toISOString(), id, ]) return { subjectUri: rows[0].subject_uri, label: rows[0].label } } export async function getOpenReportCount(): Promise { const rows = await all<{ count: number }>(`SELECT COUNT(*) as count FROM _reports WHERE status = 'open'`) return Number(rows[0]?.count || 0) } ``` **Step 2: Verify build** Run: `cd packages/hatk && npx vite build` Expected: Build succeeds --- ### Task 3: Register `dev.hatk.createReport` XRPC handler **Files:** - Modify: `packages/hatk/src/server.ts:27` (add imports) - Modify: `packages/hatk/src/server.ts:167-203` (inside the `if (oauth)` block, add the new handler) **Step 1: Add imports** Add `insertReport` to the existing import from `'./database/db.ts'` (line 6-27): ```typescript import { // ... existing imports ... insertReport, } from './database/db.ts' ``` **Step 2: Add the handler** Inside the `if (oauth) {` block (after the existing `dev.hatk.uploadBlob` handler, around line 202), add: ```typescript registerCoreXrpcHandler('dev.hatk.createReport', async (_params, _cursor, _limit, viewer, input) => { if (!viewer) throw new InvalidRequestError('Authentication required') const body = input as { subject?: any; label?: string; reason?: string } if (!body.subject) throw new InvalidRequestError('Missing subject') if (!body.label || typeof body.label !== 'string') throw new InvalidRequestError('Missing or invalid label') // Validate label exists in definitions const defs = getLabelDefinitions() if (!defs.some((d) => d.identifier === body.label)) { throw new InvalidRequestError(`Unknown label: ${body.label}`) } // Validate reason length if (body.reason && body.reason.length > 2000) { throw new InvalidRequestError('Reason must be 2000 characters or less') } // Determine subject URI and DID let subjectUri: string let subjectDid: string if (body.subject.uri) { // Record report: { uri, cid } subjectUri = body.subject.uri // Extract DID from at:// URI const match = body.subject.uri.match(/^at:\/\/(did:[^/]+)/) if (!match) throw new InvalidRequestError('Invalid subject URI') subjectDid = match[1] } else if (body.subject.did) { // Account report: { did } subjectUri = `at://${body.subject.did}` subjectDid = body.subject.did } else { throw new InvalidRequestError('Subject must have uri or did') } const result = await insertReport({ subjectUri, subjectDid, label: body.label, reason: body.reason, reportedBy: viewer.did, }) return { id: result.id, subject: body.subject, label: body.label, reason: body.reason || null, reportedBy: viewer.did, createdAt: new Date().toISOString(), } }) ``` **Step 3: Verify build** Run: `cd packages/hatk && npx vite build` Expected: Build succeeds --- ### Task 4: Add admin report endpoints **Files:** - Modify: `packages/hatk/src/server.ts` (add imports + admin routes) **Step 1: Add imports** Add `queryReports`, `resolveReport`, `getOpenReportCount` to the import from `'./database/db.ts'`: ```typescript import { // ... existing imports ... queryReports, resolveReport, getOpenReportCount, } from './database/db.ts' ``` **Step 2: Add the GET /admin/reports endpoint** After the existing `GET /admin/repos` handler (around line 670), add: ```typescript // GET /admin/reports — list reports if (url.pathname === '/admin/reports' && request.method === 'GET') { const denied = requireAdmin(viewer, acceptEncoding) if (denied) return denied const status = url.searchParams.get('status') || 'open' const label = url.searchParams.get('label') || undefined const limit = parseInt(url.searchParams.get('limit') || '50') const offset = parseInt(url.searchParams.get('offset') || '0') const result = await queryReports({ status, label, limit, offset }) return withCors(json(result, 200, acceptEncoding)) } ``` **Step 3: Add the POST /admin/reports/resolve endpoint** Right after the GET handler above: ```typescript // POST /admin/reports/resolve — resolve or dismiss a report if (url.pathname === '/admin/reports/resolve' && request.method === 'POST') { const denied = requireAdmin(viewer, acceptEncoding) if (denied) return denied const { id, action } = JSON.parse(await request.text()) if (!id || !action) return withCors(jsonError(400, 'Missing id or action', acceptEncoding)) if (action !== 'resolve' && action !== 'dismiss') return withCors(jsonError(400, 'Action must be resolve or dismiss', acceptEncoding)) const report = await resolveReport(id, action === 'resolve' ? 'resolved' : 'dismissed', viewer!.did) if (!report) return withCors(jsonError(404, 'Report not found or already resolved', acceptEncoding)) // If resolving, apply the label if (action === 'resolve') { await insertLabels([{ src: 'admin', uri: report.subjectUri, val: report.label }]) } return withCors(json({ ok: true }, 200, acceptEncoding)) } ``` **Step 4: Add openReports to the GET /admin/info response** In the existing `/admin/info` handler (around line 619-636), modify the return to include the open report count. Change: ```typescript return withCors(json({ repos: counts, duckdb: dbInfo, node, collections: collectionCounts }, 200, acceptEncoding)) ``` to: ```typescript const openReports = await getOpenReportCount() return withCors( json({ repos: counts, duckdb: dbInfo, node, collections: collectionCounts, openReports }, 200, acceptEncoding), ) ``` **Step 5: Verify build** Run: `cd packages/hatk && npx vite build` Expected: Build succeeds --- ### Task 5: Add Reports tab to admin UI **Files:** - Modify: `packages/hatk/public/admin.html` **Step 1: Add the Reports tab button to desktop nav** Find the `