feat(gateway): weekly moderation digest via WEBHOOK_URLS (#15 )

Automated public weekly summary: top categories/domains/channels + coverage rate, posted to configured webhook. Uses getDatabase() direct query (no oRPC HTTP dependency), guards one-fire-per-week on restart.
feat(gmw): public features #7-14 — scam domains, top channels, hourly heatmap, category drill-down, coverage stats, channel culture glossary, term KB, edit history
2026-08-18 20:51:12 +07:00 · 2026-08-18 20:45:12 +07:00 · 2026-08-18 18:27:15 +07:00 · 2026-08-18 17:43:02 +07:00 · 2026-08-18 16:13:25 +07:00 · 2026-08-18 15:53:18 +07:00
66 changed files with 2574 additions and 718 deletions
@@ -0,0 +1,101 @@
 # GMW — Fitur Publik Lanjutan (#2–#6) Implementation Plan
 > **For Hermes:** Implement task-by-task. Build + lint + typecheck each service
 > after its changes. Deploy via push to main (CI handles Nix build + systemd).
 > Hard constraint (user 2026-08-18): public read-only web, fully automatic,
 > rules in code, NO admin endpoints, NO shadow mode, NO per-channel web config.
 > **EXPLICITLY EXCLUDED: User Reputation / Strike History** (user: "hapus
 > sepenuhnya fitur user reputation" — it was never built; do not add it).
 ## Existing infra to reuse (verified)
 - **WS**: backend `ws/server.ts` broadcasts JSON `{type,data,timestamp}` to
  frontendClients. Backend `ws/redis-bridge.ts` subscribes Redis channels
  listed in `DISCORD_CHANNEL_TO_WS_EVENT` (backend `shared/redis-channels.ts`)
  and re-emits as WS events. FE `src/lib/ws` auto-reconnect typed client.
 - **Gateway → Redis**: `EventBroadcaster` + `RedisEventPublisher` (
  `discord-gateway/src/modules/event-broadcaster`). Publish via
  `eventBroadcaster.publish(EventChannels.X, payload)`.
 - **Moderation data**: `moderation_actions` table (now has explainability
  cols). `moderation.repository.listActions` returns rows. `ModerationAction`
  FE type at `frontend/src/lib/types/moderation.ts`.
 - **Messages**: `messages.list` / `getMessagesByChannel` (backend oRPC +
  repository). FE `messagesApi` + `useMessages`.
 - **Charts**: NO chart lib installed. Use **pure SVG/CSS** (consistent with
  repo; avoid new deps).
 - **CSV**: client-side Blob download, no backend.
 ## Task 1 — Live Moderation Feed (#2)
 **Gateway**: add `MODERATION_ACTION: "discord:moderation:action"` to
 `redis-channels.ts` (shared) + `EventChannels.MODERATION_ACTION` in
 `eventTypes.ts`. In `moderationActionsDb.createModerationAction`, after insert,
 publish `eventBroadcaster.publish(EventChannels.MODERATION_ACTION, actionRow)`.
 **Backend**: add `DISCORD_MODERATION_ACTION` constant + map
 `[DISCORD_MODERATION_ACTION]: "moderation_action"` in `DISCORD_CHANNEL_TO_WS_EVENT`.
 **FE**: in `src/lib/ws`, subscribe to `moderation_action`; add `useLiveModeration`
 hook (SWR-style with WS push, capped buffer ~50). Add `<LiveModerationFeed>`
 client component on `/moderation` page (top of list, animated new-row).
 Risk: gateway publish at every action (already async insert) — fire-and-forget,
 wrap in try/catch. Verify WS event reaches FE via `wscat`/curl or log.
 ## Task 2 — Toxic Topic Trends (#3)
 **Backend**: add `moderation.trends` oRPC. Query `moderation_actions` grouped
 by `categories` (jsonb text[]) over last 30 days, count per category + severity
 breakdown. Also `action_type` distribution. Return
 `{ categories: {name,count}[], severities: {level,count}[], actions: {type,count}[] }`.
 Map jsonb array in SQL (use `unnest` or parse in JS). Reuse `getDatabase`.
 **FE**: `useModerationTrends` hook + `<TopicTrends>` SVG bar chart (top 10
 categories) + severity donut (SVG arcs). Place on `/moderation` as a panel.
 ## Task 3 — Channel Timeline / Replay (#4)
 Reuse existing `messages.list` (guildId) + `getMessagesByChannel`. Add a
 **Timeline tab** to `/messages` that groups messages by date (client-side
 bucket from `created_at`). Load-more via cursor. No new backend (existing
 `messagesRouter.list` already supports guildId+limit+cursor). If needed, add
 `messages.timeline` aggregation (count per day) — but keep simple: client
 groups fetched rows. Verify existing endpoint returns enough history.
 ## Task 4 — Export CSV (#5)
 **FE only**. `lib/csv.ts` `toCsv(rows, columns)` + `downloadCsv(filename, csv)`.
 Add "Export CSV" button on `/moderation` (exports current actions) and
 `/messages` (exports current list). Pure client-side, read-only. No backend.
 ## Task 5 — Activity Heatmap (#6)
 **Backend**: add `messages.activity` oRPC: per-channel message count grouped by
 hour-of-day (0–23) over last 14 days. Return
 `{ channels: {channelId, name, byHour: number[24]}[], max }`. Use SQL
 `EXTRACT(hour from ...)` + group by channel. Channel name from
 `message.metadata->'channel'->>'channelName'`.
 **FE**: `useMessageActivity` hook + `<ActivityHeatmap>` SVG grid (channels ×
 24h, color intensity = count/max). Place on `/messages` or `/dashboard`.
 ## Verification checklist
 - [ ] `pnpm typecheck && pnpm lint && pnpm build` green for gateway, backend, frontend
 - [ ] Backend `/trpc/moderation/trends` returns categories/severities/actions
 - [ ] Backend `/trpc/messages/activity` returns byHour grids
 - [ ] WS `moderation_action` received by FE (log or visible live row)
 - [ ] No admin/write endpoint added; all public read-only
 - [ ] No User Reputation code anywhere (grep "reputation|strike|reputasi")
 - [ ] Deploy via push; all 3 services `running`; moderation + messages pages load
 ## Files touched (summary)
 - gateway: `shared/redis-channels.ts`, `event-broadcaster/eventTypes.ts`,
  `event-broadcaster/eventBroadcaster.ts`, `message-capture/moderationActionsDb.ts`
 - backend: `shared/redis-channels.ts`, `orpc/router.ts`,
  `modules/moderation/moderation.service.ts` (+repository),
  `modules/messages/messages.service.ts` (+repository, +schema)
 - frontend: `lib/ws/*`, `hooks/use-moderation.ts`, `hooks/use-messages.ts`,
  `lib/csv.ts`, `lib/types/*`, `app/(dashboard)/moderation/view.tsx`,
  `app/(dashboard)/messages/view.tsx`, new components under `components/`
 ## Status: COMPLETE (deployed + verified)
 - Commit 9b3134d: features #2–#6 (live feed, trends, timeline, CSV export, heatmap)
 - Commit 2a8f6d9: user reputation feature fully removed (643 deletions, no trace in src/tests)
 - Migration 0016 applied: user_reputations DROPPED (DB verified: false)
 - All 3 services active (gateway + backend restarted 18:29, frontend running)
 - Gateway typecheck/lint/test(117 passed); backend typecheck/lint/build; FE lint/build — all GREEN
 ## Verification
 - moderation/stats WS returns data (32 actions) → WS adapter works
 - DB: user_reputations gone; moderation_actions explainability cols present
 - Live Feed: gateway publishes discord:moderation:action → backend WS (same path as guild_member_*)
 - Trends/Activity: backend router procedures registered (typecheck+tsc), same WS adapter
@@ -0,0 +1,119 @@
 # GMW — Fitur Publik Lanjutan #7–#15 + Bug Fix Reputation Removal
 > **For Hermes:** Implement task-by-task. Build + lint + typecheck each service after its
 > changes. Deploy via push to main (CI handles Nix build + systemd). Apply any new
 > drizzle migration MANUALLY (systemd does NOT run migrations).
 > Hard constraint (user): public read-only web, fully automatic, rules in code,
 > NO admin endpoints, NO shadow mode, NO per-user reputation aggregation.
 ## Bug fix discovered during planning (MUST do first)
 `services/backend/src/modules/dashboard/dashboard.repository.ts` still references
 `pgUserReputationsTable` (import line 8; JOINs at lines 173 + 457) — that table was
 DROPPED in migration `0016`. `dashboard.listUsers` / `dashboard.userDetail` will
 **crash at runtime** (undefined table). Remove the import + the `r.*` join columns
 (`trust_score`, `clean_message_streak`, `total_infractions`) from both queries.
 This is a regression introduced by the reputation removal commit.
 ## Features to implement (#7–#15)
 All reuse existing infra: `moderation_actions`, `messages`, `channel_cultures`,
 `term_glossary_cache`, `ai_analysis_runs`, `message_edits`, gateway cron (for #15),
 WS (proven Live Feed pattern), oRPC over WS (proven), pure-SVG charts (no libs).
 | # | Feature | Data source | Surface |
 |---|---------|-------------|---------|
 | 7 | Flagged Link / Scam Domain Reporter | regex URL from `moderation_actions.content`/`evidence` | `/moderation` |
 | 8 | Top Flagged Channels | join `moderation_actions.message_id`→`messages.channel_id` | `/moderation` |
 | 9 | Moderation Heatmap by Hour | `moderation_actions.created_at` hour-of-day | `/moderation` |
 | 10 | Flag Category Drill-down | `moderation_actions.categories` (reuse Trends) | `/moderation` FE-only |
 | 11 | Channel Culture Glossary | `channel_cultures` (exists) | new `/channels` panel |
 | 12 | Term Knowledge Base | `term_glossary_cache` (exists) | new `/glossary` panel |
 | 13 | Edit/Evasion Tracker | `message_edits` (exists) | `/messages` |
 | 14 | Auto-mod Coverage Stats | `ai_analysis_runs` (exists) | `/moderation` metric tiles |
 | 15 | Weekly Digest (auto, cron) | aggregate #7/#8/#9 → Discord via gateway cron | gateway cron + `/moderation` |
 ## Architecture per layer
 ### Backend (oRPC, `services/backend/src`)
 - New repository methods (add to existing repos, follow `getTrends` SQL style):
  - `moderation.repository.ts`:
    - `getTopFlaggedDomains(days)` — `regexp_matches(content,'https?://([^/\s]+)')` on
      `moderation_actions WHERE created_at>=since`, group by host, COUNT, order DESC LIMIT 20.
    - `getTopFlaggedChannels(days)` — join `moderation_actions a` LEFT JOIN `messages m`
      ON `m.id=a.message_id`, group by `m.channel_id`, COUNT, order DESC LIMIT 15.
      Channel name via `m.metadata::jsonb->'channel'->>'channelName'`.
    - `getHourlyModeration(days)` — `EXTRACT(HOUR FROM to_timestamp(created_at/1000))`
      group by hour, COUNT, severity breakdown. (24 rows)
    - `getFlaggedByCategory(days, category)` — list actions where `categories` contains
      `category` (reuse `listActions` filter or new query), for drill-down #10.
    - `getCoverage(days)` — from `ai_analysis_runs`: total runs, status breakdown
      (clean/flagged/warn/error/pending), coverage % = (analyzed)/(captured in window).
  - `dashboard.repository.ts` (or new `knowledge.repository.ts`):
    - `listChannelCultures(limit, search?)` — `channel_cultures` rows (channel_id,
      guild_id, channel_name from messages metadata, culture_summary, last_analyzed_at).
    - `listGlossary(limit, search?)` — `term_glossary_cache` (term, definition, source_url,
      resolved_at, hit_count) order by hit_count DESC.
  - `messages.repository.ts`:
    - `getEditHistory(limit, channelId?)` — `message_edits` join `messages` for
      old_content + channel + username + edited_at, order DESC LIMIT.
 - `moderation.service.ts` / `dashboard.service.ts` / `messages.service.ts`: thin wrappers.
 - `orpc/router.ts`: add procedures (follow `trends` shape):
  - `moderation.topDomains`, `moderation.topChannels`, `moderation.byHour`,
    `moderation.byCategory` (input `{days,category}`), `moderation.coverage`.
  - `dashboard.channelCultures`, `dashboard.glossary`.
  - `messages.editHistory`.
 ### Frontend (`services/frontend/src`)
 - `lib/types/moderation.ts`: add `FlaggedDomain`, `FlaggedChannel`, `HourlyModeration`,
  `ModerationCoverage` interfaces.
 - `lib/types/index.ts` (+ message.ts): add `ChannelCultureRow`, `GlossaryRow`, `EditHistoryRow`.
 - `lib/api/moderation.ts`: add `topDomains`, `topChannels`, `byHour`, `byCategory`, `coverage`.
 - `lib/api/dashboard.ts` (or messages.ts): add `channelCultures`, `glossary`, `editHistory`.
 - `lib/api/server.ts`: add SSR seed fetchers (follow `getModerationStats`).
 - `hooks/use-moderation.ts`: add `useTopDomains`, `useTopChannels`, `useHourlyModeration`,
  `useByCategory`, `useCoverage`. `hooks/use-dashboard.ts`/`use-messages.ts`: add culture/glossary/edit hooks. `hooks/index.ts`: export all.
 - New components (pure SVG/CSS, reuse `GlassPanel`/`SectionHeader`/`Badge`/`Donut`):
  - `components/ScamDomains.tsx`, `components/TopChannels.tsx`, `components/ModerationHeatmap.tsx`,
    `components/CoverageTiles.tsx`, `components/ChannelCultureGlossary.tsx`,
    `components/TermGlossary.tsx`, `components/EditHistory.tsx`.
 - Wire into `app/(dashboard)/moderation/view.tsx` (grid col-span-2/3/5 as space allows)
  and `app/(dashboard)/messages/view.tsx` (EditHistory panel) and new route pages
  `app/(dashboard)/channels/page.tsx` + `app/(dashboard)/glossary/page.tsx` with
  matching `view.tsx` (follow existing page→view SSR pattern; check `app/(dashboard)/dashboard/page.tsx`).
 - Export CSV buttons reuse `lib/csv.ts` `downloadCsv` (client-side) for domains/channels/edits.
 ### Gateway (#15 Weekly Digest)
 - Add a cron/interval in `services/discord-gateway` (check existing scheduler pattern —
  search `setInterval`/`cron` in `src`). On a 7-day cadence, query backend oRPC
  (`dashboard.activity`, `moderation.trends`, `moderation.topChannels`) — OR compute
  directly via a shared repository — and post a formatted summary to the monitor guild
  channel (via existing `discordClient.channels.send` helper). Fully automatic, no UI.
 ## Files touched (summary)
 - backend: `modules/moderation/{repository,service}.ts`, `modules/dashboard/{repository,service}.ts`,
  `modules/messages/{repository,service}.ts`, `orpc/router.ts`, `shared/index.ts` (if new tables),
  `lib/types/*` (FE)
 - frontend: `lib/api/*`, `lib/types/*`, `hooks/*`, `components/*`, `app/(dashboard)/*`
 - gateway: new digest scheduler + (none if reuse backend) maybe `shared/redis-channels.ts`
 ## Constraints / pitfalls (from gmw-ops skill)
 - `created_at` is bigint epoch-MS — compare with `<`/`>`, do NOT divide by 1000 in SQL.
 - Pure SVG only — frontend has ZERO chart libs.
 - `Badge` Tone = signal|amber|vermilion|neutral (no "rose").
 - Frontend WS import is `@/lib/ws/context`; method `on` not `subscribe`.
 - Commit author `asepharyana`, no Co-Authored-By.
 - Rebuild `dist/` after gateway changes; apply drizzle migrations manually.
 ## Verification
 - Per service: `pnpm typecheck && pnpm lint && pnpm build` green.
 - Gateway: `pnpm test` (117+ pass).
 - Live: `moderation/stats` WS returns data (proves adapter); new procedures registered
  (typecheck = proof). `systemctl show` new ActiveEnterTimestamp after deploy.
 - DB: confirm `channel_cultures`/`term_glossary_cache`/`message_edits`/`ai_analysis_runs`
  have rows before relying on them (some may be empty → components handle empty state).
 ## Execution order
 1. Bug fix dashboard.repository (reputation JOIN) — deploy-safe.
 2. Backend repositories + service + router (#7,#8,#9,#14 dashboard; #11,#12; #13).
 3. FE types + api + hooks + components + wire (#7,#8,#9,#10,#11,#12,#13,#14).
 4. Gateway #15 digest (if scheduler exists) — verify via log, not UI.
 5. Build/lint all 3 services; commit; push; monitor CI; apply migrations; verify live.
@@ -5,7 +5,6 @@ import {
  pgChannelCulturesTable,
  pgMessagesTable,
  pgUserProfilesTable,
  pgUserReputationsTable,
  pgVoiceRecordingsTable,
 } from "../../shared/index.js";
 import type { ListUsersQuery } from "./dashboard.service.js";
@@ -156,8 +155,7 @@ export class DashboardRepository {
        p.profile_summary,
        m.total_messages,
        m.flagged_count,
-        m.last_message_at,
+        m.last_message_at
        r.trust_score
      FROM (
        SELECT
          user_id,
@@ -170,7 +168,6 @@ export class DashboardRepository {
        GROUP BY user_id, username, avatar_url
      ) m
      LEFT JOIN ${pgUserProfilesTable} p ON p.user_id = m.user_id
      LEFT JOIN ${pgUserReputationsTable} r ON r.user_id = m.user_id
      ${whereClause}
      ORDER BY m.last_message_at DESC NULLS LAST
      LIMIT ${limit + 1}
@@ -186,10 +183,6 @@ export class DashboardRepository {
        total_messages: Number(r.total_messages),
        flagged_count: Number(r.flagged_count),
        last_message_at: r.last_message_at ? Number(r.last_message_at) : null,
        trust_score:
          r.trust_score !== null && r.trust_score !== undefined
            ? Number(r.trust_score)
            : null,
      }));
    const lastRow = rows[limit - 1] as Record<string, unknown> | undefined;
@@ -437,10 +430,7 @@ export class DashboardRepository {
        m.flagged_count,
        m.clean_count,
        p.profile_summary,
-        p.last_analyzed_at,
+        p.last_analyzed_at
        r.trust_score,
        r.clean_message_streak,
        r.total_infractions
      FROM (
        SELECT
          user_id,
@@ -454,7 +444,6 @@ export class DashboardRepository {
        GROUP BY user_id, username, avatar_url
      ) m
      LEFT JOIN ${pgUserProfilesTable} p ON p.user_id = m.user_id
      LEFT JOIN ${pgUserReputationsTable} r ON r.user_id = m.user_id
    `);
    const row = userResult.rows[0] as Record<string, unknown> | undefined;
@@ -481,13 +470,6 @@ export class DashboardRepository {
      last_analyzed_at: row.last_analyzed_at
        ? Number(row.last_analyzed_at)
        : null,
      trust_score: row.trust_score != null ? Number(row.trust_score) : null,
      clean_message_streak:
        row.clean_message_streak != null
          ? Number(row.clean_message_streak)
          : null,
      total_infractions:
        row.total_infractions != null ? Number(row.total_infractions) : null,
      recent_messages: (recent.rows as Record<string, unknown>[]).map((r) => ({
        id: String(r.id),
        content: String(r.content),
@@ -0,0 +1,99 @@
 import { sql } from "drizzle-orm";
 import { getDatabase } from "../../shared/database/index.js";
 export interface ChannelCultureRow {
  channel_id: string;
  guild_id: string | null;
  channel_name: string | null;
  culture_summary: string | null;
  last_analyzed_at: number | null;
 }
 export interface GlossaryRow {
  term: string;
  definition: string;
  source_url: string;
  resolved_at: number;
  hit_count: number;
 }
 export interface EditHistoryRow {
  id: string;
  message_id: string;
  old_content: string;
  edited_at: number;
  channel_id: string | null;
  channel_name: string | null;
  username: string | null;
 }
 export class KnowledgeRepository {
  /** Public read-only channel culture glossary (AI-generated norms/slang). */
  async listChannelCultures(limit = 50, search?: string) {
    const db = getDatabase();
    const conditions: string[] = [];
    if (search) {
      conditions.push(
        `(c.channel_id ILIKE '%${search.replace(/'/g, "''")}%' OR c.culture_summary ILIKE '%${search.replace(/'/g, "''")}%')`,
      );
    }
    const where = conditions.length ? `WHERE ${conditions.join(" AND ")}` : "";
    const result = await db.execute(
      sql.raw(`
        SELECT
          c.channel_id,
          c.guild_id,
          COALESCE(NULLIF((
            SELECT (metadata::jsonb -> 'channel' ->> 'channelName')
            FROM messages WHERE channel_id = c.channel_id AND metadata IS NOT NULL
            LIMIT 1
          ), ''), c.channel_id) AS channel_name,
          c.culture_summary,
          c.last_analyzed_at
        FROM channel_cultures c
        ${where}
        ORDER BY c.last_analyzed_at DESC NULLS LAST
        LIMIT ${limit}
      `),
    );
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      channel_id: String(r.channel_id),
      guild_id: r.guild_id ? String(r.guild_id) : null,
      channel_name: r.channel_name ? String(r.channel_name) : null,
      culture_summary: r.culture_summary ? String(r.culture_summary) : null,
      last_analyzed_at: r.last_analyzed_at ? Number(r.last_analyzed_at) : null,
    }));
  }
  /** Public read-only term knowledge base (resolved via Wikipedia/SearXNG). */
  async listGlossary(limit = 50, search?: string) {
    const db = getDatabase();
    const conditions: string[] = [];
    if (search) {
      conditions.push(
        `(term ILIKE '%${search.replace(/'/g, "''")}%' OR definition ILIKE '%${search.replace(/'/g, "''")}%')`,
      );
    }
    const where = conditions.length ? `WHERE ${conditions.join(" AND ")}` : "";
    const result = await db.execute(
      sql.raw(`
        SELECT term, definition, source_url, resolved_at, hit_count
        FROM term_glossary_cache
        ${where}
        ORDER BY hit_count DESC, resolved_at DESC
        LIMIT ${limit}
      `),
    );
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      term: String(r.term),
      definition: String(r.definition ?? ""),
      source_url: r.source_url ? String(r.source_url) : "",
      resolved_at: r.resolved_at ? Number(r.resolved_at) : 0,
      hit_count: Number(r.hit_count ?? 0),
    }));
  }
 }
 export const knowledgeRepository = new KnowledgeRepository();
@@ -0,0 +1,18 @@
 import { createChildLogger } from "../../shared/logger/index.js";
 import { knowledgeRepository } from "./knowledge.repository.js";
 const logger = createChildLogger("knowledge.service");
 export class KnowledgeService {
  async listChannelCultures(limit = 50, search?: string) {
    logger.debug({ limit, search }, "Listing channel cultures");
    return knowledgeRepository.listChannelCultures(limit, search);
  }
  async listGlossary(limit = 50, search?: string) {
    logger.debug({ limit, search }, "Listing glossary terms");
    return knowledgeRepository.listGlossary(limit, search);
  }
 }
 export const knowledgeService = new KnowledgeService();
@@ -1,5 +1,5 @@
-import { config } from "@/shared/config/index.js";
+import { config } from "@/shared/config/index";
-import { createChildLogger } from "@/shared/logger/index.js";
+import { createChildLogger } from "@/shared/logger/index";
 const logger = createChildLogger("messages-embed");
@@ -459,6 +459,69 @@ export class MessagesRepository {
    return { data: trimmed, nextCursor };
  }
  /**
   * Per-hour message volume for the last `days` days, grouped by channel.
   * Powers the public Activity Heatmap (read-only, no write scope).
   * Returns a flat list of { channel_id, hour (0-23), count } buckets.
   */
  async getActivity(days = 30) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(sql`
      SELECT channel_id,
             EXTRACT(HOUR FROM to_timestamp(created_at / 1000))::int AS hour,
             COUNT(*)::int AS c
      FROM messages
      WHERE created_at >= ${since}
      GROUP BY channel_id, hour
      ORDER BY channel_id, hour
    `);
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      channelId: String(r.channel_id ?? "unknown"),
      hour: Number(r.hour ?? 0),
      count: Number(r.c ?? 0),
    }));
  }
  /**
   * Recent message edits across the server (evasion-signal tracker).
   * Public, read-only. Joins message_edits → messages for context.
   */
  async getRecentEdits(limit = 50, channelId?: string) {
    const db = getDatabase();
    const where = channelId
      ? `WHERE m.channel_id = '${channelId.replace(/'/g, "''")}'`
      : "";
    const result = await db.execute(
      sql.raw(`
        SELECT
          e.id,
          e.message_id,
          e.old_content,
          e.edited_at,
          m.channel_id,
          COALESCE(NULLIF((m.metadata::jsonb -> 'channel' ->> 'channelName'), ''), m.channel_id) AS channel_name,
          m.username
        FROM message_edits e
        JOIN messages m ON m.id = e.message_id
        ${where}
        ORDER BY e.edited_at DESC
        LIMIT ${limit}
      `),
    );
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      id: String(r.id),
      message_id: String(r.message_id),
      old_content: r.old_content ? String(r.old_content) : "",
      edited_at: r.edited_at ? Number(r.edited_at) : 0,
      channel_id: r.channel_id ? String(r.channel_id) : null,
      channel_name: r.channel_name ? String(r.channel_name) : null,
      username: r.username ? String(r.username) : null,
    }));
  }
 }
 export const messagesRepository = new MessagesRepository();
@@ -101,6 +101,15 @@ export class MessagesService {
    const results = hits.map((h) => mapSearchHit(h));
    return { results, nextCursor: null };
  }
  async getActivity(days = 30) {
    return messagesRepository.getActivity(days);
  }
  async getRecentEdits(limit = 50, channelId?: string) {
    logger.debug({ limit, channelId }, "Getting recent message edits");
    return messagesRepository.getRecentEdits(limit, channelId);
  }
 }
 /** Shape returned to the frontend (text + metadata from the archive payload). */
@@ -1,5 +1,5 @@
-import { config } from "@/shared/config/index.js";
+import { config } from "@/shared/config/index";
-import { createChildLogger } from "@/shared/logger/index.js";
+import { createChildLogger } from "@/shared/logger/index";
 const logger = createChildLogger("messages-qdrant");
@@ -164,6 +164,220 @@ export class ModerationRepository {
    return { data, nextCursor };
  }
  /**
   * Aggregate moderation trends over the last `days` days.
   * - category counts (from the jsonb/text[] `categories` column, unnested)
   * - severity distribution
   * - action_type distribution
   * Read-only; powers the public Toxic Topic Trends panel.
   */
  async getTrends(days: number) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const cats = await db.execute(sql`
      SELECT jsonb_array_elements_text(a.categories::jsonb) AS cat, COUNT(*)::int AS c
      FROM moderation_actions a
      WHERE a.created_at >= ${since} AND a.categories IS NOT NULL AND a.categories != '[]' AND a.categories != ''
      GROUP BY cat
      ORDER BY c DESC
      LIMIT 15
    `);
    const catRows = (cats.rows as Record<string, unknown>[]) || [];
    const sev = await db.execute(sql`
      SELECT severity, COUNT(*)::int AS c
      FROM moderation_actions
      WHERE created_at >= ${since} AND severity IS NOT NULL
      GROUP BY severity
    `);
    const sevRows = (sev.rows as Record<string, unknown>[]) || [];
    const act = await db.execute(sql`
      SELECT action_type, COUNT(*)::int AS c
      FROM moderation_actions
      WHERE created_at >= ${since}
      GROUP BY action_type
      ORDER BY c DESC
    `);
    const actRows = (act.rows as Record<string, unknown>[]) || [];
    return {
      categories: catRows.map((r) => ({
        name: String(r.cat),
        count: Number(r.c ?? 0),
      })),
      severities: sevRows.map((r) => ({
        level: String(r.severity),
        count: Number(r.c ?? 0),
      })),
      actions: actRows.map((r) => ({
        type: String(r.action_type),
        count: Number(r.c ?? 0),
      })),
    };
  }
  /**
   * Top flagged domains over the last `days` days.
   * Extracts the host from any URL in `content`/`reason`/`evidence` and ranks
   * by how often it appears in moderation actions. Powers the Scam Domain panel.
   */
  async getTopFlaggedDomains(days: number) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(sql`
      SELECT host, COUNT(*)::int AS c
      FROM (
        SELECT DISTINCT a.id,
          (regexp_matches(COALESCE(a.content,'') || ' ' || COALESCE(a.reason,'') || ' ' || COALESCE(a.evidence,''), 'https?://([^/\s?#]+)', 'g'))[1] AS host
        FROM moderation_actions a
        WHERE a.created_at >= ${since}
          AND (a.content IS NOT NULL OR a.reason IS NOT NULL OR a.evidence IS NOT NULL)
      ) sub
      WHERE host IS NOT NULL
      GROUP BY host
      ORDER BY c DESC
      LIMIT 20
    `);
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      domain: String(r.host).toLowerCase(),
      count: Number(r.c ?? 0),
    }));
  }
  /**
   * Top flagged channels over the last `days` days.
   * Joins moderation_actions → messages to attribute each action to a channel.
   * Powers the Top Flagged Channels panel.
   */
  async getTopFlaggedChannels(days: number) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(sql`
      SELECT
        m.channel_id,
        COALESCE(NULLIF((m.metadata::jsonb -> 'channel' ->> 'channelName'), ''), m.channel_id) AS channel_name,
        COUNT(*)::int AS flagged_count
      FROM moderation_actions a
      LEFT JOIN messages m ON m.id = a.message_id
      WHERE a.created_at >= ${since} AND m.channel_id IS NOT NULL
      GROUP BY m.channel_id, (m.metadata::jsonb -> 'channel' ->> 'channelName')
      ORDER BY flagged_count DESC
      LIMIT 15
    `);
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      channel_id: String(r.channel_id),
      channel_name: r.channel_name ? String(r.channel_name) : null,
      flagged_count: Number(r.flagged_count),
    }));
  }
  /**
   * Hour-of-day distribution of moderation actions over the last `days` days.
   * 24 rows (hour 0..23), with total + flagged-by-severity counts.
   * Powers the Moderation Heatmap by Hour panel.
   */
  async getHourlyModeration(days: number) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(sql`
      SELECT
        EXTRACT(HOUR FROM to_timestamp(created_at / 1000))::int AS hour,
        COUNT(*)::int AS total
      FROM moderation_actions
      WHERE created_at >= ${since}
      GROUP BY hour
      ORDER BY hour
    `);
    const rows = (result.rows as Record<string, unknown>[]) || [];
    const byHour = new Map<number, number>();
    for (const r of rows) byHour.set(Number(r.hour), Number(r.total));
    return Array.from({ length: 24 }, (_, h) => ({
      hour: h,
      total: byHour.get(h) ?? 0,
    }));
  }
  /**
   * Moderation actions filtered to a single category (drill-down).
   * Powers the Flag Category Drill-down panel.
   */
  async getByCategory(days: number, category: string, limit = 50) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(
      sql.raw(`
      SELECT
        a.id, a.message_id, a.user_id, a.guild_id, a.action_type,
        a.reason, a.status, a.created_at, a.severity, a.confidence, a.score,
        m.username, LEFT(m.content, 300) AS content
      FROM moderation_actions a
      LEFT JOIN messages m ON m.id = a.message_id
      WHERE a.created_at >= ${since}
        AND a.categories IS NOT NULL
        AND a.categories::jsonb @> ${JSON.stringify([category])}::jsonb
      ORDER BY a.created_at DESC
      LIMIT ${limit}
    `),
    );
    const rows = (result.rows as Record<string, unknown>[]) || [];
    return rows.map((r) => ({
      id: String(r.id ?? ""),
      message_id: r.message_id ? String(r.message_id) : null,
      user_id: r.user_id ? String(r.user_id) : null,
      guild_id: String(r.guild_id ?? ""),
      action_type: String(r.action_type ?? "unknown"),
      reason: r.reason ? String(r.reason) : null,
      status: String(r.status ?? "unknown"),
      created_at: r.created_at ? Number(r.created_at) : null,
      severity: r.severity ? String(r.severity) : null,
      confidence: r.confidence != null ? Number(r.confidence) : null,
      score: r.score != null ? Number(r.score) : null,
      username: r.username ? String(r.username) : null,
      content: r.content ? String(r.content) : null,
    }));
  }
  /**
   * Auto-moderation coverage over the last `days` days.
   * Run completion rate from ai_analysis_runs — what fraction of analysis runs
   * completed (vs failed/pending). Public "how much is automated" trust metric.
   */
  async getCoverage(days: number) {
    const db = getDatabase();
    const since = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = await db.execute(sql`
      SELECT status, COUNT(*)::int AS c
      FROM ai_analysis_runs
      WHERE created_at >= ${since}
      GROUP BY status
    `);
    const rows = (result.rows as Record<string, unknown>[]) || [];
    const counts: Record<string, number> = {};
    let total = 0;
    for (const r of rows) {
      const s = String(r.status);
      const c = Number(r.c ?? 0);
      counts[s] = c;
      total += c;
    }
    const completed = counts.completed ?? 0;
    const failed = counts.failed ?? 0;
    const pending = (counts.pending ?? 0) + (counts.processing ?? 0);
    return {
      total,
      completed,
      failed,
      pending,
      coverage_rate:
        total > 0 ? Number(((completed / total) * 100).toFixed(1)) : 0,
      failed_rate: total > 0 ? Number(((failed / total) * 100).toFixed(1)) : 0,
    };
  }
 }
 export const moderationRepository = new ModerationRepository();
@@ -8,10 +8,33 @@ const logger = createChildLogger("moderation.service");
 export class ModerationService {
  async getStats() {
    logger.debug("Fetching moderation stats");
    return moderationRepository.getStats();
  }
  async getTrends(days = 30) {
    return moderationRepository.getTrends(days);
  }
  async getTopFlaggedDomains(days = 30) {
    return moderationRepository.getTopFlaggedDomains(days);
  }
  async getTopFlaggedChannels(days = 30) {
    return moderationRepository.getTopFlaggedChannels(days);
  }
  async getHourlyModeration(days = 30) {
    return moderationRepository.getHourlyModeration(days);
  }
  async getByCategory(days = 30, category: string) {
    return moderationRepository.getByCategory(days, category);
  }
  async getCoverage(days = 30) {
    return moderationRepository.getCoverage(days);
  }
  async listActions(query: ListModerationQuery) {
    logger.debug({ query }, "Listing moderation actions");
    return moderationRepository.listActions(query);
@@ -5,6 +5,7 @@ import { chatRequestSchema } from "../modules/chatbot/chatbot.schema";
 import { chatbotService } from "../modules/chatbot/chatbot.service";
 // ── Service imports ──────────────────────────────────────────────
 import { dashboardService } from "../modules/dashboard/dashboard.service";
 import { knowledgeService } from "../modules/knowledge/knowledge.service";
 import {
  mediaLoopSchema,
  mediaQueueSchema,
@@ -143,6 +144,25 @@ const messagesRouter = {
  semanticSearch: os
    .input(semanticSearchSchema)
    .handler(({ input }) => messagesService.semanticSearch(input)),
  // Public, read-only activity heatmap data (per-hour volume by channel).
  activity: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) => messagesService.getActivity(input.days)),
  // Public, read-only recent message edits (evasion tracker).
  editHistory: os
    .input(
      z.object({
        limit: z.coerce.number().int().positive().default(50),
        channelId: z.string().optional(),
      }),
    )
    .handler(({ input }) =>
      messagesService.getRecentEdits(input.limit, input.channelId),
    ),
 };
 // ── Moderation ───────────────────────────────────────────────────
@@ -165,6 +185,58 @@ const moderationRouter = {
        cursor: input.cursor,
      }),
    ),
  trends: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) => moderationService.getTrends(input.days)),
  // Flagged link / scam domain ranking (public Scam Domain panel).
  topDomains: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) => moderationService.getTopFlaggedDomains(input.days)),
  // Top flagged channels (join moderation_actions → messages).
  topChannels: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) =>
      moderationService.getTopFlaggedChannels(input.days),
    ),
  // Hour-of-day moderation distribution (heatmap by hour).
  byHour: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) => moderationService.getHourlyModeration(input.days)),
  // Flag category drill-down (list actions for one category).
  byCategory: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
        category: z.string().min(1),
      }),
    )
    .handler(({ input }) =>
      moderationService.getByCategory(input.days, input.category),
    ),
  // Auto-moderation coverage (analysis run completion rate).
  coverage: os
    .input(
      z.object({
        days: z.coerce.number().int().positive().max(365).default(30),
      }),
    )
    .handler(({ input }) => moderationService.getCoverage(input.days)),
 };
 // ── Media ────────────────────────────────────────────────────────
@@ -306,7 +378,29 @@ const chatbotRouter = {
    }),
 };
-// ── Config (public dashboard config snapshot) ──────────────────────
+// ── Knowledge (public read-only culture glossary + term KB) ───────
 const knowledgeRouter = {
  channelCultures: os
    .input(
      z.object({
        limit: z.coerce.number().int().positive().default(50),
        search: z.string().optional(),
      }),
    )
    .handler(({ input }) =>
      knowledgeService.listChannelCultures(input.limit, input.search),
    ),
  glossary: os
    .input(
      z.object({
        limit: z.coerce.number().int().positive().default(50),
        search: z.string().optional(),
      }),
    )
    .handler(({ input }) =>
      knowledgeService.listGlossary(input.limit, input.search),
    ),
 };
 const configRouter = {
  get: os.handler(() => ({
    monitorGuildId: config.MONITOR_GUILD_ID || null,
@@ -345,6 +439,7 @@ export const appRouter = {
  chatbot: chatbotRouter,
  config: configRouter,
  uiState: uiStateRouter,
  knowledge: knowledgeRouter,
 };
 export type AppRouter = typeof appRouter;
@@ -30,6 +30,7 @@ export const DISCORD_CHANNEL_TOPIC_UPDATED = "discord:channel:topic_updated";
 export const DISCORD_PRESENCE_UPDATED = "discord:presence:updated";
 export const DISCORD_GUILD_MEMBER_ADDED = "discord:guild_member:added";
 export const DISCORD_GUILD_MEMBER_REMOVED = "discord:guild_member:removed";
 export const DISCORD_MODERATION_ACTION = "discord:moderation:action";
 // ---------------------------------------------------------------------------
 // Command channels (backend -> discord-gateway)
@@ -126,4 +127,5 @@ export const DISCORD_CHANNEL_TO_WS_EVENT: Record<string, string> = {
  [DISCORD_PRESENCE_UPDATED]: "presence_updated",
  [DISCORD_GUILD_MEMBER_ADDED]: "guild_member_added",
  [DISCORD_GUILD_MEMBER_REMOVED]: "guild_member_removed",
  [DISCORD_MODERATION_ACTION]: "moderation_action",
 };
@@ -71,7 +71,7 @@ handles a whole batch (text + media split internally, parallel paths).
 - `embeddingClient.ts` + `qdrantClient.ts` — semantic cache (one embed call +
  one batched Qdrant search for all uncached targets).
 - `textCacheStore.ts` / `channelCultureStore.ts` / `userProfileStore.ts` /
-  `userReputationStore.ts` — caches & learned per-channel/user state.
+  `userProfileStore.ts` — caches learned user profile summaries (optional).
 ### Concurrency model
@@ -49,7 +49,7 @@ Orchestration/caching: `moderationOrchestrator.ts` (exact hash → batched
 semantic Qdrant → LLM), `textBatchProcessor.ts` / `mediaBatchProcessor.ts`
 (one LLM call per sub-batch), `llmClient.ts` (central streaming client),
 `embeddingClient.ts` + `qdrantClient.ts` (semantic cache), plus
-`channelCultureStore.ts` / `userProfileStore.ts` / `userReputationStore.ts`.
+`channelCultureStore.ts` / `userProfileStore.ts`.
 ### voice-recording
 `voiceController.ts` (connect/disconnect/list) + `recorder.ts` (orchestration)
@@ -0,0 +1,3 @@
 -- Remove the user reputation feature entirely (trust scores, infractions).
 -- The feature was removed from the codebase; this drops the orphaned table.
 DROP TABLE IF EXISTS "user_reputations";
@@ -113,6 +113,13 @@
      "when": 1787184000000,
      "tag": "0015_add_moderation_explainability",
      "breakpoints": true
    },
    {
      "idx": 16,
      "version": "7",
      "when": 1787185000000,
      "tag": "0016_drop_user_reputations",
      "breakpoints": true
    }
  ]
 }
@@ -25,6 +25,8 @@ import {
  registerMessageCapture,
  setEventBroadcaster as setMessageCaptureEventBroadcaster,
 } from "../modules/message-capture/messageCapture.js";
 import { setModerationEventBroadcaster } from "../modules/message-capture/moderationActionsDb.js";
 import { startDigestScheduler } from "../modules/monitor/digestScheduler.js";
 import { registerReactionCapture } from "../modules/reaction-tracking/index.js";
 import { registerThreadCapture } from "../modules/thread-tracking/index.js";
 import { registerPresenceCapture } from "../modules/user-presence/index.js";
@@ -254,6 +256,7 @@ export async function initializeDiscordGateway() {
    logger.info({ user: client.user?.tag }, "Bot logged in");
    setMessageCaptureEventBroadcaster(eventBroadcaster);
    setRecorderEventBroadcaster(eventBroadcaster);
    setModerationEventBroadcaster(eventBroadcaster);
    registerMessageCapture(client);
    startPendingAIAnalysisWorker(client, eventBroadcaster);
@@ -273,6 +276,8 @@ export async function initializeDiscordGateway() {
    // Start retention cleanup scheduler
    startRetentionCleanup();
    // Start weekly moderation digest (public, automated)
    startDigestScheduler();
  });
  client.on("error", (err) => {
@@ -128,30 +128,6 @@ export async function skipAgeRestrictedMessages(
 // Batch pipeline
 // ---------------------------------------------------------------------------
 async function postBatchReputationUpdate(rows: MessageRecord[]): Promise<void> {
  for (const row of rows) {
    if (row.ai_status === "clean") {
      import("./userReputationStore.js")
        .then((store) => store.recordCleanMessage(row.user_id, row.guild_id))
        .catch((e) =>
          logger.error({ error: e }, "Failed to record clean message streak"),
        );
    } else if (row.ai_status === "flagged" && row.ai_severity !== "none") {
      import("./userReputationStore.js")
        .then((store) =>
          store.recordInfraction(
            row.user_id,
            row.guild_id,
            row.ai_severity as "low" | "medium" | "high" | "critical",
          ),
        )
        .catch((e) =>
          logger.error({ error: e }, "Failed to record infraction penalty"),
        );
    }
  }
 }
 export async function processBatch(
  conversationKey: string,
  messages: MessageRecord[],
@@ -196,21 +172,6 @@ export async function processBatch(
      }
    }
    // Post-batch reputation updates (fire-and-forget)
    postBatchReputationUpdate(
      result.rows.filter((r) => {
        if (r.ai_status === "error") {
          try {
            const flags = JSON.parse(r.ai_moderation_flags ?? "[]") as string[];
            return !flags.includes("analysis_api_failed");
          } catch {
            return false;
          }
        }
        return true;
      }),
    );
    if (!result.ok) {
      recordConversationBatchFailure(conversationKey);
@@ -123,33 +123,6 @@ async function processIndividualFallback(
    for (const row of rows) {
      broadcastAnalysisCompleted(row);
      scheduleAutoDelete(row);
      // Update reputation autonomously
      if (row.ai_status === "clean") {
        import("./userReputationStore.js")
          .then((store) => store.recordCleanMessage(row.user_id, row.guild_id))
          .catch((e) =>
            logger.error(
              { error: e },
              "Failed to record clean message streak in fallback",
            ),
          );
      } else if (row.ai_status === "flagged" && row.ai_severity !== "none") {
        import("./userReputationStore.js")
          .then((store) =>
            store.recordInfraction(
              row.user_id,
              row.guild_id,
              row.ai_severity as "low" | "medium" | "high" | "critical",
            ),
          )
          .catch((e) =>
            logger.error(
              { error: e },
              "Failed to record infraction penalty in fallback",
            ),
          );
      }
    }
    const resultSummary = analysisResult.results[0];
@@ -127,56 +127,10 @@ export function buildUserProfileRef(userId: string): string {
 }
 // ---------------------------------------------------------------------------
-// User reputation — richer than a bare trust score.
+// Per-user history context (last flagged messages only — no trust model).
-//
+// context to AI moderation.
 // The trust model tracks total_infractions, a clean-message streak and the
 // last infraction timestamp. Feeding all of it to the LLM lets it tell a
 // first-timer (same score, 1 infraction) from a repeat offender (score 50,
 // 3 infractions, last one yesterday) — the same score means very different
 // things in those two contexts.
 // ---------------------------------------------------------------------------
 export interface ReputationAttrsSource {
  trust_score: number;
  total_infractions: number;
  clean_message_streak: number;
  last_infraction_at: number | null;
 }
 const DAY_MS = 24 * 60 * 60 * 1000;
 const REPEAT_OFFENSE_WINDOW_MS = 7 * DAY_MS;
 /**
 * Formats reputation fields into XML attributes for `<user_reputation .../>`.
 * Derived signals: last_offense_days_ago (0 = today) and repeat_offender
 * (infraction within the last 7 days) are computed here so both the text and
 * media paths emit the exact same shape.
 */
 export function formatReputationAttrs(
  rep: ReputationAttrsSource,
  now: number = Date.now(),
 ): string {
  const attrs = [
    `trust_score="${rep.trust_score}"`,
    `total_infractions="${rep.total_infractions}"`,
    `clean_streak="${rep.clean_message_streak}"`,
  ];
  if (
    typeof rep.last_infraction_at === "number" &&
    rep.last_infraction_at > 0
  ) {
    const daysAgo = Math.max(
      0,
      Math.floor((now - rep.last_infraction_at) / DAY_MS),
    );
    attrs.push(`last_offense_days_ago="${daysAgo}"`);
    const isRepeat =
      rep.total_infractions > 0 &&
      now - rep.last_infraction_at <= REPEAT_OFFENSE_WINDOW_MS;
    if (isRepeat) attrs.push(`repeat_offender="true"`);
  }
  return attrs.join(" ");
 }
 /**
 * Builds an optional `<user_history>` block (last flagged messages) from
@@ -1,324 +0,0 @@
 import { and, desc, eq } from "drizzle-orm";
 import { createChildLogger } from "@/shared/logger/index";
 import { getDatabase } from "../../shared/database/drizzle.js";
 import {
  messagesTable,
  type UserReputation,
  userReputationsTable,
 } from "../../shared/database/schema.js";
 const logger = createChildLogger("userReputationStore");
 // ---------------------------------------------------------------------------
 // Trust model v2 — fair, recoverable, escalation-aware
 // ---------------------------------------------------------------------------
 //
 // Problems with v1 that this fixes:
 //  1. Trust practically could NOT rise: +2 per 100 clean messages meant a
 //     single -15 "high" penalty required 750 clean messages to repay.
 //  2. Flat penalties regardless of history: first-timers and repeat
 //     offenders were punished identically.
 //  3. Minor infractions could zero out a user (low=-2 at score 2 → 0),
 //     which is disproportionate.
 //
 // v2 model:
 //  - GAIN:  +1 trust per 15 consecutive clean messages (cap 100). Recovery
 //    is real but earned — consistent good behavior rebuilds trust.
 //  - PENALTY: severity table low=3 / medium=6 / high=12 / critical=25.
 //  - FIRST OFFENSE: penalty halved (leniency for a single slip).
 //  - REPEAT OFFENDER: infraction within the last 7 days → ×1.5 (escalation).
 //  - FLOOR: low/medium infractions cannot push trust below 10/5 — minor
 //    offenses never permanently cripple a user; high/critical can still
 //    zero out (severe behavior has severe consequences).
 //  - Streak resets on infraction; time-based recovery still happens through
 //    the clean-message gain (no arbitrary idle-decay).
 // ---------------------------------------------------------------------------
 export const TRUST_DEFAULTS = {
  DEFAULT_TRUST: 50,
  MAX_TRUST: 100,
  MIN_TRUST: 0,
  CLEAN_MESSAGES_PER_POINT: 15,
  REPEAT_OFFENSE_WINDOW_MS: 7 * 24 * 60 * 60 * 1000, // 7 days
  REPEAT_OFFENSE_MULTIPLIER: 1.5,
 } as const;
 export const INFRACTION_PENALTIES: Record<
  "low" | "medium" | "high" | "critical",
  number
 > = {
  low: 3,
  medium: 6,
  high: 12,
  critical: 25,
 };
 /** Trust floors per severity — minor offenses can't tank a user to zero. */
 export const INFRACTION_FLOORS: Record<
  "low" | "medium" | "high" | "critical",
  number
 > = {
  low: 10,
  medium: 5,
  high: 0,
  critical: 0,
 };
 function clampTrust(score: number): number {
  return Math.min(
    TRUST_DEFAULTS.MAX_TRUST,
    Math.max(TRUST_DEFAULTS.MIN_TRUST, Math.round(score)),
  );
 }
 export interface InfractionContext {
  totalInfractions: number;
  lastInfractionAt: number | null;
  severity: "low" | "medium" | "high" | "critical";
  now?: number;
 }
 export interface InfractionOutcome {
  penalty: number;
  appliedRules: {
    firstOffense: boolean;
    repeatEscalation: boolean;
  };
 }
 /**
 * Pure penalty computation for the trust model (unit-testable, no DB).
 * - First offense ever → halved (leniency for a single slip).
 * - Repeat offense within the 7-day window → ×1.5 (escalation).
 */
 export function computeInfractionPenalty(
  ctx: InfractionContext,
 ): InfractionOutcome {
  const basePenalty = INFRACTION_PENALTIES[ctx.severity];
  let penalty = basePenalty;
  const isFirstOffense = ctx.totalInfractions === 0;
  if (isFirstOffense) {
    penalty = Math.ceil(basePenalty / 2);
  } else if (
    ctx.lastInfractionAt &&
    (ctx.now ?? Date.now()) - ctx.lastInfractionAt <=
      TRUST_DEFAULTS.REPEAT_OFFENSE_WINDOW_MS
  ) {
    penalty = Math.ceil(basePenalty * TRUST_DEFAULTS.REPEAT_OFFENSE_MULTIPLIER);
  }
  return {
    penalty,
    appliedRules: {
      firstOffense: isFirstOffense,
      repeatEscalation: !isFirstOffense && penalty > basePenalty,
    },
  };
 }
 export interface CleanGainOutcome {
  newStreak: number;
  trustGain: number;
 }
 /**
 * Pure clean-message gain computation (unit-testable, no DB).
 * +1 trust every CLEAN_MESSAGES_PER_POINT consecutive clean messages;
 * the streak keeps counting past the threshold (gains compound).
 */
 export function computeCleanTrustGain(currentStreak: number): CleanGainOutcome {
  const newStreak = currentStreak + 1;
  const trustGain =
    newStreak % TRUST_DEFAULTS.CLEAN_MESSAGES_PER_POINT === 0 ? 1 : 0;
  return { newStreak, trustGain };
 }
 /**
 * Ensures a user reputation record exists.
 */
 export async function initializeUserReputation(
  userId: string,
  guildId: string,
 ): Promise<UserReputation> {
  const db = getDatabase();
  const existing = await db
    .select()
    .from(userReputationsTable)
    .where(eq(userReputationsTable.user_id, userId))
    .limit(1);
  if (existing.length > 0) {
    logger.debug({ userId }, "Reputation record already exists");
    return existing[0];
  }
  const [inserted] = await db
    .insert(userReputationsTable)
    .values({
      user_id: userId,
      guild_id: guildId,
      trust_score: TRUST_DEFAULTS.DEFAULT_TRUST,
      clean_message_streak: 0,
      total_infractions: 0,
      created_at: Date.now(),
      updated_at: Date.now(),
    })
    .onConflictDoNothing()
    .returning();
  if (!inserted) {
    // If concurrent insert happened
    logger.debug({ userId }, "Concurrent reputation insert detected, retrying");
    const retry = await db
      .select()
      .from(userReputationsTable)
      .where(eq(userReputationsTable.user_id, userId))
      .limit(1);
    return retry[0];
  }
  logger.debug(
    { userId, trustScore: inserted.trust_score },
    "Initialized user reputation",
  );
  return inserted;
 }
 /**
 * Fetch a user's reputation score. Returns default 50 if none exists.
 */
 export async function getUserReputation(
  userId: string,
 ): Promise<UserReputation | null> {
  const db = getDatabase();
  const existing = await db
    .select()
    .from(userReputationsTable)
    .where(eq(userReputationsTable.user_id, userId))
    .limit(1);
  if (existing[0]) {
    logger.debug(
      { userId, trustScore: existing[0].trust_score },
      "Fetched user reputation",
    );
  } else {
    logger.debug({ userId }, "No reputation record found, returning null");
  }
  return existing[0] || null;
 }
 /**
 * Increment the clean message streak and grow trust — +1 per
 * CLEAN_MESSAGES_PER_POINT consecutive clean messages (cap 100). The streak
 * keeps counting past the threshold so gains compound with continued good
 * behavior (no more wasted progress at 100, and recovery is genuinely
 * reachable after an infraction).
 */
 export async function recordCleanMessage(
  userId: string,
  guildId: string,
 ): Promise<void> {
  const rep = await initializeUserReputation(userId, guildId);
  const db = getDatabase();
  const { newStreak, trustGain } = computeCleanTrustGain(
    rep.clean_message_streak,
  );
  const newScore =
    trustGain > 0 ? clampTrust(rep.trust_score + trustGain) : rep.trust_score;
  await db
    .update(userReputationsTable)
    .set({
      clean_message_streak: newStreak,
      trust_score: newScore,
      updated_at: Date.now(),
    })
    .where(eq(userReputationsTable.user_id, userId));
  logger.debug(
    { userId, previousScore: rep.trust_score, newScore, newStreak },
    "Clean message recorded, reputation updated",
  );
 }
 /**
 * Apply an infraction penalty to a user.
 *
 * Fairness rules:
 * - First offense ever → penalty halved (leniency, rounded up).
 * - Repeat offense within the 7-day window → ×1.5 (escalation).
 * - Severity floor prevents minor infractions from zeroing a user.
 * - Streak resets — trust must be re-earned through clean behavior.
 */
 export async function recordInfraction(
  userId: string,
  guildId: string,
  severity: "low" | "medium" | "high" | "critical",
 ): Promise<void> {
  const rep = await initializeUserReputation(userId, guildId);
  const db = getDatabase();
  const outcome = computeInfractionPenalty({
    totalInfractions: rep.total_infractions,
    lastInfractionAt: rep.last_infraction_at,
    severity,
  });
  const { penalty } = outcome;
  const floor = INFRACTION_FLOORS[severity];
  const newScore = Math.max(floor, clampTrust(rep.trust_score - penalty));
  await db
    .update(userReputationsTable)
    .set({
      trust_score: newScore,
      clean_message_streak: 0, // Reset streak on infraction
      total_infractions: rep.total_infractions + 1,
      last_infraction_at: Date.now(),
      updated_at: Date.now(),
    })
    .where(eq(userReputationsTable.user_id, userId));
  logger.info(
    {
      userId,
      severity,
      basePenalty: INFRACTION_PENALTIES[severity],
      penalty,
      appliedRules: outcome.appliedRules,
      previousScore: rep.trust_score,
      newScore,
      floor,
      totalInfractions: rep.total_infractions + 1,
    },
    "Infraction recorded",
  );
 }
 /**
 * Fetch a user's past N flagged messages for context injection.
 */
 export async function getUserRecentInfractions(
  userId: string,
  limit: number = 3,
 ) {
  const db = getDatabase();
  return await db
    .select({
      content: messagesTable.content,
      flags: messagesTable.ai_moderation_flags,
      severity: messagesTable.ai_severity,
      created_at: messagesTable.created_at,
    })
    .from(messagesTable)
    .where(
      and(
        eq(messagesTable.user_id, userId),
        eq(messagesTable.ai_status, "flagged"),
      ),
    )
    .orderBy(desc(messagesTable.created_at))
    .limit(limit);
 }
@@ -293,6 +293,16 @@ export class EventBroadcaster {
    });
  }
  async moderationAction(data: Record<string, unknown>): Promise<void> {
    this.logger.debug({ data }, "Publishing moderation_action");
    await this.publisher.publish(EventChannels.MODERATION_ACTION, {
      type: "moderation_action",
      data,
      timestamp: Date.now(),
      source: "discord-gateway",
    });
  }
  async analysisQueueStatus(data: Record<string, unknown>): Promise<void> {
    this.logger.debug({ data }, "Publishing analysis_queue_status");
    await this.publisher.publish(EventChannels.ANALYSIS_QUEUE_STATUS, {
@@ -9,6 +9,7 @@ import {
  DISCORD_MESSAGE_CREATED,
  DISCORD_MESSAGE_DELETED,
  DISCORD_MESSAGE_UPDATED,
  DISCORD_MODERATION_ACTION,
  DISCORD_PRESENCE_UPDATED,
  DISCORD_REACTION_ADDED,
  DISCORD_REACTION_REMOVED,
@@ -50,6 +51,7 @@ export const EventChannels = {
  GUILD_MEMBER_ADDED: DISCORD_GUILD_MEMBER_ADDED,
  GUILD_MEMBER_REMOVED: DISCORD_GUILD_MEMBER_REMOVED,
  VOICE_ANALYZED: DISCORD_VOICE_ANALYZED,
  MODERATION_ACTION: DISCORD_MODERATION_ACTION,
 } as const;
 export type EventChannelType =
@@ -1,11 +1,11 @@
-import { embedText } from "@/modules/ai-moderation/embeddingClient.js";
+import { embedText } from "@/modules/ai-moderation/embeddingClient";
 import {
  ARCHIVE_COLLECTION,
  qdrantPointId,
  upsertQdrantPointV2,
-} from "@/modules/ai-moderation/qdrantClient.js";
+} from "@/modules/ai-moderation/qdrantClient";
 import { config } from "@/shared/config/config.js";
 import { createChildLogger } from "@/shared/logger/index";
 import { config } from "../../shared/config/config.js";
 const log = createChildLogger("archive-embedder");
@@ -4,8 +4,16 @@ import type * as schema from "../../shared/database/schema.js";
 import { moderationActionsTable } from "../../shared/database/schema.js";
 import { buildCursorCondition, pageResult } from "../../shared/index.js";
 import { createChildLogger, type Logger } from "../../shared/logger/index.js";
 import type { EventBroadcaster } from "../event-broadcaster/eventBroadcaster.js";
 import type { ModerationAction, PageResult } from "../message-capture/types.js";
 let _eventBroadcaster: EventBroadcaster | null = null;
 /** Inject the gateway's event broadcaster so actions can be published live. */
 export function setModerationEventBroadcaster(eb: EventBroadcaster): void {
  _eventBroadcaster = eb;
 }
 // ─── ModerationActionsDb Class ──────────────────────────────────────────────
 export class ModerationActionsDb {
@@ -38,7 +46,16 @@ export class ModerationActionsDb {
        })
        .returning();
-      return rows[0] as ModerationAction;
+      const created = rows[0] as ModerationAction;
      // Fire-and-forget live broadcast (backend WS → frontend feed).
      if (_eventBroadcaster) {
        _eventBroadcaster
          .moderationAction(created as unknown as Record<string, unknown>)
          .catch(() => {});
      }
      return created;
    } catch (error) {
      this.logger.error(
        {
@@ -0,0 +1,150 @@
 import { sql } from "drizzle-orm";
 import { config } from "@/shared/config/index.js";
 import { getDatabase } from "@/shared/database/drizzle.js";
 import { createChildLogger } from "@/shared/logger/index";
 const logger = createChildLogger("digest-scheduler");
 // 7 days
 const WEEK_MS = 7 * 24 * 60 * 60 * 1000;
 let lastDigestTs: number = 0;
 /**
 * Weekly moderation digest for the public monitor channel (or webhook).
 * Fully automatic — no UI, no shadow mode. Queries the DB directly and posts
 * a compact embed to WEBHOOK_URLS. Read-only: never mutates moderation state.
 */
 export async function runWeeklyDigest(now = Date.now()): Promise<void> {
  // Run at most once per week (guard against double-scheduling on restart).
  if (now - lastDigestTs < WEEK_MS) return;
  lastDigestTs = now;
  if (!config.WEBHOOK_URLS.length) {
    logger.warn("No WEBHOOK_URLS configured — skipping weekly digest");
    return;
  }
  const db = getDatabase();
  const since = now - WEEK_MS;
  try {
    const [trends, domains, channels, coverage] = await Promise.all([
      // Top categories
      db.execute(sql`
        SELECT jsonb_array_elements_text(categories)::text AS name,
               COUNT(*)::int AS c
        FROM moderation_actions
        WHERE created_at >= ${since} AND categories IS NOT NULL
        GROUP BY name ORDER BY c DESC LIMIT 5
      `),
      // Top flagged domains
      db.execute(sql`
        SELECT host, COUNT(*)::int AS c
        FROM (
          SELECT DISTINCT id,
            (regexp_matches(COALESCE(content,'') || ' ' || COALESCE(reason,'') || ' ' || COALESCE(evidence,''), 'https?://([^/\\s?#]+)', 'g'))[1] AS host
          FROM moderation_actions
          WHERE created_at >= ${since}
            AND (content IS NOT NULL OR reason IS NOT NULL OR evidence IS NOT NULL)
        ) sub
        WHERE host IS NOT NULL
        GROUP BY host ORDER BY c DESC LIMIT 5
      `),
      // Top flagged channels
      db.execute(sql`
        SELECT COALESCE(NULLIF((m.metadata::jsonb -> 'channel' ->> 'channelName'), ''), m.channel_id) AS channel_name,
               COUNT(*)::int AS c
        FROM moderation_actions a
        LEFT JOIN messages m ON m.id = a.message_id
        WHERE a.created_at >= ${since} AND m.channel_id IS NOT NULL
        GROUP BY channel_name ORDER BY c DESC LIMIT 5
      `),
      // Coverage
      db.execute(sql`
        SELECT status, COUNT(*)::int AS c
        FROM ai_analysis_runs
        WHERE created_at >= ${since}
        GROUP BY status
      `),
    ]);
    const topCats = (trends.rows as Record<string, unknown>[]).map(
      (r) => `${r.name} (${r.c})`,
    );
    const topDomains = (domains.rows as Record<string, unknown>[]).map(
      (r) => `${r.host} (${r.c})`,
    );
    const topChannels = (channels.rows as Record<string, unknown>[]).map(
      (r) => `${r.channel_name} (${r.c})`,
    );
    const cov = (coverage.rows as Record<string, unknown>[]) || [];
    const total = cov.reduce((s, r) => s + Number(r.c), 0);
    const completed = Number(cov.find((r) => r.status === "completed")?.c ?? 0);
    const covRate = total > 0 ? ((completed / total) * 100).toFixed(1) : "0";
    const lines: string[] = [];
    lines.push(`**GMW Weekly Moderation Digest** (last 7 days)`);
    lines.push("");
    lines.push(
      `Auto-mod coverage: ${covRate}% (${completed}/${total} runs completed)`,
    );
    lines.push(
      `**Top flagged categories:** ${topCats.length ? topCats.join(", ") : "—"}`,
    );
    lines.push(
      `**Top flagged domains:** ${topDomains.length ? topDomains.join(", ") : "—"}`,
    );
    lines.push(
      `**Top flagged channels:** ${topChannels.length ? topChannels.join(", ") : "—"}`,
    );
    lines.push("");
    lines.push(
      "_View full breakdowns at the moderation dashboard (public, read-only)._",
    );
    const body = JSON.stringify({
      username: "GMW Digest",
      avatar_url:
        "https://upload.wikimedia.org/wikipedia/commons/6/6a/Orange_tabby_cat_sitting_on_fallen_leaves-Hisashi-01A.jpg",
      content: null,
      embeds: [
        {
          title: "GMW Weekly Moderation Digest",
          description: lines.join("\n"),
          color: 0x38bdf8,
          timestamp: new Date().toISOString(),
        },
      ],
    });
    for (const url of config.WEBHOOK_URLS) {
      try {
        const res = await fetch(url, {
          method: "POST",
          headers: { "Content-Type": "application/json" },
          body,
        });
        if (!res.ok) {
          logger.warn({ url, status: res.status }, "Digest webhook failed");
        }
      } catch (err) {
        logger.warn({ err }, "Digest webhook threw");
      }
    }
    logger.info("Weekly digest posted");
  } catch (err) {
    logger.error({ err }, "Weekly digest failed");
  }
 }
 /** Schedule the weekly digest. Runs on an interval; the guard inside
 * `runWeeklyDigest` ensures it only fires once per WEEK_MS.
 */
 export function startDigestScheduler(intervalMs = 60 * 60 * 1000): void {
  // Fire an immediate (guarded) digest on start, then tick hourly.
  void runWeeklyDigest();
  setInterval(() => {
    void runWeeklyDigest();
  }, intervalMs);
 }
@@ -34,7 +34,7 @@ export const configSchema = z
      .describe("Thread IDs to exclude from capture"),
    BOT_EXCLUDED_CHANNEL_IDS: z
      .string()
-      .default("1206269771340058694")
+      .default("1206269771340058694,1318544753821880362")
      .transform((v) => v.split(",").filter(Boolean))
      .describe(
        "Channel IDs where bot messages are NOT captured/analyzed (bot detection stays on everywhere else)",
@@ -222,7 +222,8 @@ export const configSchema = z
    AI_GLOSSARY_MAX_TERMS: z.coerce.number().int().min(1).max(20).default(6),
    // Per-user personal profile summaries (userProfileLearner). Disabled by
    // default: profiles bloat the analysis context and add LLM/DB cost for
-    // little moderation signal — only <user_reputation> history is injected.
+    // little moderation signal — user history context (last flagged messages)
    // is injected via <user_history> instead of a numeric trust score.
    AI_USER_PROFILE_LEARNING_ENABLED: z
      .string()
      .optional()
@@ -337,32 +337,6 @@ export const pgUserProfilesTable = pgTable(
 export const userProfilesTable = pgUserProfilesTable;
 /**
 * User Reputations Table (PostgreSQL)
 * Tracks user trust score and infractions to provide context to AI.
 */
 export const pgUserReputationsTable = pgTable(
  "user_reputations",
  {
    user_id: pgText("user_id").primaryKey(),
    guild_id: pgText("guild_id").notNull(),
    trust_score: pgInteger("trust_score").notNull().default(50),
    clean_message_streak: pgInteger("clean_message_streak")
      .notNull()
      .default(0),
    total_infractions: pgInteger("total_infractions").notNull().default(0),
    last_infraction_at: pgBigint("last_infraction_at", { mode: "number" }),
    created_at: pgBigint("created_at", { mode: "number" }).notNull(),
    updated_at: pgBigint("updated_at", { mode: "number" }).notNull(),
  },
  (table) => ({
    guildIdx: pgIndex("idx_user_reputations_guild_id").on(table.guild_id),
    scoreIdx: pgIndex("idx_user_reputations_trust_score").on(table.trust_score),
  }),
 );
 export const userReputationsTable = pgUserReputationsTable;
 /**
 * Channel Cultures Table (PostgreSQL)
 * Stores AI-generated summaries of channel norms and slang to inject as context.
@@ -592,10 +566,6 @@ export type AIAnalysisRunInsert = typeof aiAnalysisRunsTable.$inferInsert;
 export type UserProfile = typeof userProfilesTable.$inferSelect;
 export type UserProfileInsert = typeof userProfilesTable.$inferInsert;
 // User Reputations
 export type UserReputation = typeof userReputationsTable.$inferSelect;
 export type UserReputationInsert = typeof userReputationsTable.$inferInsert;
 // Channel Cultures
 export type ChannelCulture = typeof channelCulturesTable.$inferSelect;
 export type ChannelCultureInsert = typeof channelCulturesTable.$inferInsert;
@@ -2,26 +2,17 @@ import {
  pgAIAnalysisRunsTable,
  pgChannelCulturesTable,
  pgUserProfilesTable,
  pgUserReputationsTable,
 } from "../../../shared/index.js";
 // Re-export shared tables
-export {
+export { pgAIAnalysisRunsTable, pgChannelCulturesTable, pgUserProfilesTable };
  pgAIAnalysisRunsTable,
  pgChannelCulturesTable,
  pgUserProfilesTable,
  pgUserReputationsTable,
 };
 export const aiAnalysisRunsTable = pgAIAnalysisRunsTable;
 export const channelCulturesTable = pgChannelCulturesTable;
 export const userProfilesTable = pgUserProfilesTable;
 export const userReputationsTable = pgUserReputationsTable;
 // Types
 export type AIAnalysisRun = typeof aiAnalysisRunsTable.$inferSelect;
 export type AIAnalysisRunInsert = typeof aiAnalysisRunsTable.$inferInsert;
 export type UserReputation = typeof userReputationsTable.$inferSelect;
 export type UserReputationInsert = typeof userReputationsTable.$inferInsert;
 export type ChannelCulture = typeof channelCulturesTable.$inferSelect;
 export type ChannelCultureInsert = typeof channelCulturesTable.$inferInsert;
 export type UserProfile = typeof userProfilesTable.$inferSelect;
@@ -30,6 +30,7 @@ export const DISCORD_CHANNEL_TOPIC_UPDATED = "discord:channel:topic_updated";
 export const DISCORD_PRESENCE_UPDATED = "discord:presence:updated";
 export const DISCORD_GUILD_MEMBER_ADDED = "discord:guild_member:added";
 export const DISCORD_GUILD_MEMBER_REMOVED = "discord:guild_member:removed";
 export const DISCORD_MODERATION_ACTION = "discord:moderation:action";
 // ---------------------------------------------------------------------------
 // Command channels (backend -> discord-gateway)
@@ -1,12 +1,11 @@
 // ═══════════════════════════════════════════════════════════════════════════
-// Context enrichment builders — rich <user_reputation> attrs, <user_history>,
+// Context enrichment builders — <user_history>, <user_profiles> as_of,
-// <user_profiles> as_of, bot/edited detection (pure, no DB)
+// bot/edited detection (pure, no DB)
 // ═══════════════════════════════════════════════════════════════════════════
 import { describe, expect, it } from "vitest";
 import {
  buildUserHistoryXml,
  buildUserProfilesBlock,
  formatReputationAttrs,
  resolveIsBot,
  resolveIsEdited,
 } from "../src/modules/ai-moderation/moderationBuilders.js";
@@ -42,72 +41,6 @@ function msg(overrides: Partial<MessageRecord> = {}): MessageRecord {
 const DAY_MS = 24 * 60 * 60 * 1000;
 describe("formatReputationAttrs — rich reputation signal", () => {
  it("emits trust, infraction count and clean streak", () => {
    const attrs = formatReputationAttrs({
      trust_score: 62,
      total_infractions: 3,
      clean_message_streak: 45,
      last_infraction_at: null,
    });
    expect(attrs).toContain('trust_score="62"');
    expect(attrs).toContain('total_infractions="3"');
    expect(attrs).toContain('clean_streak="45"');
  });
  it("derives last_offense_days_ago and marks repeat offenders (7-day window)", () => {
    const attrs = formatReputationAttrs(
      {
        trust_score: 50,
        total_infractions: 2,
        clean_message_streak: 0,
        last_infraction_at: NOW - 2 * DAY_MS,
      },
      NOW,
    );
    expect(attrs).toContain('last_offense_days_ago="2"');
    expect(attrs).toContain('repeat_offender="true"');
  });
  it("does NOT mark repeat offender when the last offense is older than 7 days", () => {
    const attrs = formatReputationAttrs(
      {
        trust_score: 50,
        total_infractions: 2,
        clean_message_streak: 10,
        last_infraction_at: NOW - 30 * DAY_MS,
      },
      NOW,
    );
    expect(attrs).toContain('last_offense_days_ago="30"');
    expect(attrs).not.toContain("repeat_offender");
  });
  it("omits offense-derived attrs when the user has no recorded infraction date", () => {
    const attrs = formatReputationAttrs({
      trust_score: 85,
      total_infractions: 0,
      clean_message_streak: 120,
      last_infraction_at: null,
    });
    expect(attrs).not.toContain("last_offense_days_ago");
    expect(attrs).not.toContain("repeat_offender");
  });
  it("clamps a future/skewed timestamp to days_ago=0", () => {
    const attrs = formatReputationAttrs(
      {
        trust_score: 50,
        total_infractions: 1,
        clean_message_streak: 0,
        last_infraction_at: NOW + 5 * DAY_MS,
      },
      NOW,
    );
    expect(attrs).toContain('last_offense_days_ago="0"');
  });
 });
 describe("buildUserHistoryXml — last flagged messages for repeat offenders", () => {
  it("returns empty when there is no real history", () => {
    expect(buildUserHistoryXml([])).toBe("");
@@ -1,93 +0,0 @@
 // ═══════════════════════════════════════════════════════════════════════════
 // Trust model v2 — pure math tests (no DB required)
 // ═══════════════════════════════════════════════════════════════════════════
 import { describe, expect, it } from "vitest";
 import {
  computeCleanTrustGain,
  computeInfractionPenalty,
  INFRACTION_FLOORS,
  INFRACTION_PENALTIES,
  TRUST_DEFAULTS,
 } from "../src/modules/ai-moderation/userReputationStore.js";
 describe("computeCleanTrustGain — trust CAN rise", () => {
  it("grants +1 every CLEAN_MESSAGES_PER_POINT clean messages", () => {
    const before = computeCleanTrustGain(14);
    expect(before.newStreak).toBe(15);
    expect(before.trustGain).toBe(1);
    const after = computeCleanTrustGain(15);
    expect(after.newStreak).toBe(16);
    expect(after.trustGain).toBe(0);
  });
  it("keeps compounding past the threshold (no wasted progress)", () => {
    expect(computeCleanTrustGain(29).trustGain).toBe(1);
    expect(computeCleanTrustGain(44).trustGain).toBe(1);
    // 45 clean messages from a fresh start → 3 points of recovery
    let gain = 0;
    let streak = 0;
    for (let i = 0; i < 45; i++) {
      const r = computeCleanTrustGain(streak);
      streak = r.newStreak;
      gain += r.trustGain;
    }
    expect(gain).toBe(3);
  });
 });
 describe("computeInfractionPenalty — fair and escalating", () => {
  const NOW = Date.now();
  it("applies base penalty for a repeat offender outside the window", () => {
    const r = computeInfractionPenalty({
      totalInfractions: 3,
      lastInfractionAt: NOW - TRUST_DEFAULTS.REPEAT_OFFENSE_WINDOW_MS - 1000,
      severity: "medium",
      now: NOW,
    });
    expect(r.penalty).toBe(INFRACTION_PENALTIES.medium); // 6
    expect(r.appliedRules.firstOffense).toBe(false);
    expect(r.appliedRules.repeatEscalation).toBe(false);
  });
  it("halves the penalty for a first offense (leniency)", () => {
    const r = computeInfractionPenalty({
      totalInfractions: 0,
      lastInfractionAt: null,
      severity: "high",
      now: NOW,
    });
    expect(r.penalty).toBe(Math.ceil(INFRACTION_PENALTIES.high / 2)); // 6
    expect(r.appliedRules.firstOffense).toBe(true);
  });
  it("escalates ×1.5 for a repeat offense within 7 days", () => {
    const r = computeInfractionPenalty({
      totalInfractions: 2,
      lastInfractionAt: NOW - 60 * 60 * 1000, // 1h ago
      severity: "medium",
      now: NOW,
    });
    expect(r.penalty).toBe(Math.ceil(INFRACTION_PENALTIES.medium * 1.5)); // 9
    expect(r.appliedRules.repeatEscalation).toBe(true);
  });
  it("critical first offense still hurts but is halved", () => {
    const r = computeInfractionPenalty({
      totalInfractions: 0,
      lastInfractionAt: null,
      severity: "critical",
      now: NOW,
    });
    expect(r.penalty).toBe(Math.ceil(INFRACTION_PENALTIES.critical / 2)); // 13
  });
  it("severity floors prevent minor offenses from zeroing a user", () => {
    expect(INFRACTION_FLOORS.low).toBeGreaterThan(0);
    expect(INFRACTION_FLOORS.medium).toBeGreaterThan(0);
    // high/critical can still reach zero — severe behavior has consequences
    expect(INFRACTION_FLOORS.high).toBe(0);
    expect(INFRACTION_FLOORS.critical).toBe(0);
  });
 });
@@ -0,0 +1,20 @@
 import { PageTransition } from "@/components/shared";
 import { getChannelCultures } from "@/lib/api/server";
 import type { ChannelCultureRow } from "@/lib/types";
 import { ChannelsView } from "./view";
 export const dynamic = "force-dynamic";
 export default async function ChannelsPage() {
  let cultures: ChannelCultureRow[] | undefined;
  try {
    cultures = await getChannelCultures(100);
  } catch {
    cultures = undefined;
  }
  return (
    <PageTransition>
      <ChannelsView initialCultures={cultures} />
    </PageTransition>
  );
 }
@@ -0,0 +1,23 @@
 "use client";
 import { ChannelCultureGlossary } from "@/components/ChannelCultureGlossary";
 import { SkeletonPanel } from "@/components/shared";
 import { useChannelCultures } from "@/hooks";
 import type { ChannelCultureRow } from "@/lib/types";
 export function ChannelsView({
  initialCultures,
 }: {
  initialCultures?: ChannelCultureRow[];
 }) {
  const { data: cultures } = useChannelCultures(100, initialCultures);
  return (
    <div className="space-y-5">
      {cultures ? (
        <ChannelCultureGlossary cultures={cultures} />
      ) : (
        <SkeletonPanel rows={6} />
      )}
    </div>
  );
 }
@@ -0,0 +1,20 @@
 import { PageTransition } from "@/components/shared";
 import { getGlossary } from "@/lib/api/server";
 import type { GlossaryRow } from "@/lib/types";
 import { GlossaryView } from "./view";
 export const dynamic = "force-dynamic";
 export default async function GlossaryPage() {
  let terms: GlossaryRow[] | undefined;
  try {
    terms = await getGlossary(100);
  } catch {
    terms = undefined;
  }
  return (
    <PageTransition>
      <GlossaryView initialTerms={terms} />
    </PageTransition>
  );
 }
@@ -0,0 +1,15 @@
 "use client";
 import { SkeletonPanel } from "@/components/shared";
 import { TermGlossary } from "@/components/TermGlossary";
 import { useGlossary } from "@/hooks";
 import type { GlossaryRow } from "@/lib/types";
 export function GlossaryView({
  initialTerms,
 }: {
  initialTerms?: GlossaryRow[];
 }) {
  const { data: terms } = useGlossary(100, initialTerms);
  return terms ? <TermGlossary terms={terms} /> : <SkeletonPanel rows={6} />;
 }
@@ -1,5 +1,10 @@
 import { PageTransition } from "@/components/shared";
-import { getConfig, getGuilds, getMessages } from "@/lib/api/server";
+import {
  getConfig,
  getGuilds,
  getMessages,
  getRecentEdits,
 } from "@/lib/api/server";
 import { MessagesView } from "./view";
 export const dynamic = "force-dynamic";
@@ -11,12 +16,14 @@ export default async function MessagesPage() {
    data: import("@/lib/types").MessageRecord[];
    nextCursor: string | null;
  } | null = null;
  let initialEdits: import("@/lib/types").EditHistoryRow[] | undefined;
  try {
    [config, guilds] = await Promise.all([getConfig(), getGuilds()]);
    const gid = config?.monitorGuildId;
    if (gid) {
      initialMessages = await getMessages(gid, undefined, 50);
    }
    initialEdits = await getRecentEdits(50);
  } catch {
    /* client hooks surface errors */
  }
@@ -26,6 +33,7 @@ export default async function MessagesPage() {
        initialGuilds={guilds}
        initialGuildId={config?.monitorGuildId ?? null}
        initialMessages={initialMessages}
        initialEdits={initialEdits}
      />
    </PageTransition>
  );
@@ -2,6 +2,7 @@
 import {
  AlertTriangle,
  Calendar,
  CheckCircle2,
  Image as ImageIcon,
  Loader2,
@@ -11,7 +12,9 @@ import {
  ShieldAlert,
 } from "lucide-react";
 import { useCallback, useEffect, useMemo, useRef, useState } from "react";
 import { ActivityHeatmap } from "@/components/ActivityHeatmap";
 import { useAmbient } from "@/components/ambient/ambient-context";
 import { EditHistory } from "@/components/EditHistory";
 import {
  Avatar,
  Badge,
@@ -28,12 +31,14 @@ import {
 import { GuildChannelPicker } from "@/components/shared/guild-picker";
 import {
  useLoadMore,
  useMessageActivity,
  useMessageDetail,
  useMessageSearch,
  useMessages,
  useMessagesHasMore,
  useMessagesStream,
  useMessagesWsSync,
  useRecentEdits,
  useSemanticSearch,
 } from "@/hooks";
 import { aiTone } from "@/lib/ai-status";
@@ -45,7 +50,12 @@ import {
  renderMessageContent,
  safeParseJsonArray,
 } from "@/lib/format";
-import type { AiStatus, Guild, MessageRecord } from "@/lib/types";
+import type {
  AiStatus,
  EditHistoryRow,
  Guild,
  MessageRecord,
 } from "@/lib/types";
 import { staggerDelay } from "@/lib/utils";
 import { useWebSocket } from "@/lib/ws/context";
@@ -53,6 +63,7 @@ export function MessagesView({
  initialGuilds,
  initialGuildId,
  initialMessages,
  initialEdits,
 }: {
  initialGuilds?: Guild[];
  initialGuildId?: string | null;
@@ -60,6 +71,7 @@ export function MessagesView({
    data: MessageRecord[];
    nextCursor: string | null;
  } | null;
  initialEdits?: EditHistoryRow[];
 }) {
  const ws = useWebSocket();
  const [guildId, setGuildId] = useState<string | null>(
@@ -71,6 +83,8 @@ export function MessagesView({
  // Search mode: "exact" (substring match over captured messages) or
  // "semantic" (vector similarity over the persistent Qdrant archive).
  const [semanticMode, setSemanticMode] = useState(false);
  // feed | timeline: "timeline" groups messages into date-grouped cards.
  const [viewMode, setViewMode] = useState<"feed" | "timeline">("feed");
  // Guard against loading the entire history on a long scroll: cap how many
  // older pages we append. Each page is 50 messages (backend limit default).
  const MAX_OLDER_PAGES = 10;
@@ -106,6 +120,8 @@ export function MessagesView({
    query,
    query.trim().length >= 2 && semanticMode,
  );
  const activity = useMessageActivity(30);
  const edits = useRecentEdits(50, undefined, initialEdits);
  const detail = useMessageDetail(selected);
  const ambient = useAmbient();
@@ -140,6 +156,33 @@ export function MessagesView({
  // returns DESC (newest first); reverse so the feed reads top→bottom like DC.
  const display = useMemo(() => [...list].reverse(), [list]);
  // Timeline mode: inject date-separator headers above the first message of
  // each day. Messages are sorted oldest→newest (display is reversed), so a
  // date change means a new group. Produces an array of either "date" or "msg"
  // nodes so the render loop can switch easily.
  const timelineNodes = useMemo(() => {
    if (viewMode !== "timeline") return null;
    const out: Array<
      | { type: "date"; label: string; iso: string }
      | { type: "msg"; m: (typeof display)[number] }
    > = [];
    let prev = "";
    for (const m of display) {
      const d = new Date(m.created_at).toLocaleDateString(undefined, {
        weekday: "short",
        month: "short",
        day: "numeric",
      });
      const iso = new Date(m.created_at).toISOString().slice(0, 10);
      if (d !== prev) {
        out.push({ type: "date", label: d, iso });
        prev = d;
      }
      out.push({ type: "msg", m });
    }
    return out;
  }, [display, viewMode]);
  // Ref to the scroll container so we can manage scroll position like Discord:
  // open at the bottom (newest), keep the viewport stable when prepending older
  // messages at the top, and follow new live messages only when already near
@@ -208,6 +251,20 @@ export function MessagesView({
        >
          {semanticMode ? "Semantic" : "Exact"}
        </button>
        <button
          type="button"
          onClick={() =>
            setViewMode((v) => (v === "feed" ? "timeline" : "feed"))
          }
          className={`rounded-full border px-3 py-1.5 text-xs transition-colors ${
            viewMode === "timeline"
              ? "border-signal/40 bg-signal/10 text-signal"
              : "border-hairline bg-white/[0.03] text-ink-soft hover:bg-white/[0.06]"
          }`}
          title="Toggle timeline (date-grouped) view"
        >
          {viewMode === "timeline" ? "Timeline" : "Feed"}
        </button>
      </GlassPanel>
      <div className="grid gap-4 lg:grid-cols-5">
@@ -326,45 +383,33 @@ export function MessagesView({
                  }
                }}
              >
-                {display.map((m, i) => (
+                {viewMode === "timeline" && timelineNodes
-                  <button
+                  ? timelineNodes.map((node, _i) =>
-                    key={m.id}
+                      node.type === "date" ? (
-                    type="button"
+                        <div
-                    onClick={() => setSelected(m.id)}
+                          key={`date-${node.iso}`}
-                    className={`animate-stagger flex w-full items-start gap-3 rounded-[12px] border p-3 text-left transition-colors ${
+                          className="flex items-center gap-2 px-1 text-[0.65rem] text-ink-faint"
-                      selected === m.id
+                        >
-                        ? "border-signal/40 bg-signal/8"
+                          <Calendar className="size-3" />
-                        : "border-hairline bg-white/[0.03] hover:bg-white/[0.06]"
+                          {node.label}
-                    }`}
+                        </div>
-                    style={staggerDelay(i)}
+                      ) : (
-                  >
+                        <MessageRow
-                    <Avatar src={m.avatar_url} name={m.username} size={34} />
+                          key={node.m.id}
-                    <div className="min-w-0 flex-1">
+                          m={node.m}
-                      <div className="flex items-center gap-2">
+                          selected={selected}
-                        <span className="truncate text-sm font-semibold text-ink">
+                          onSelect={setSelected}
-                          {m.username}
+                        />
-                        </span>
+                      ),
-                        <span className="mono text-[0.65rem] text-ink-faint">
+                    )
-                          {getMessageChannelLabel(m)}
+                  : display.map((m, _i) => (
-                        </span>
+                      <MessageRow
-                        <span className="mono ml-auto text-[0.6rem] text-ink-faint">
+                        key={m.id}
-                          {formatRelativeTime(m.created_at)}
+                        m={m}
-                        </span>
+                        selected={selected}
-                      </div>
+                        onSelect={setSelected}
-                      <div className="mt-0.5 line-clamp-2 text-sm text-ink-soft">
+                      />
-                        {renderMessageContent(m.content, m.metadata) || (
+                    ))}
                          <span className="italic text-ink-faint">
                            (empty / embed)
                          </span>
                        )}
                      </div>
                    </div>
                    <AiBadge
                      status={m.ai_status}
                      durationMs={m.ai_analysis_duration_ms}
                    />
                  </button>
                ))}
              </div>
            </div>
          )}
@@ -392,6 +437,12 @@ export function MessagesView({
          )}
        </GlassPanel>
      </div>
      {activity.data && activity.data.length > 0 && (
        <ActivityHeatmap buckets={activity.data} />
      )}
      {edits.data && <EditHistory edits={edits.data} />}
    </div>
  );
 }
@@ -513,3 +564,48 @@ function MessageDetail({
    </div>
  );
 }
 /** Single message card used by both the live feed and the date-grouped timeline. */
 function MessageRow({
  m,
  selected,
  onSelect,
 }: {
  m: MessageRecord;
  selected: string | null;
  onSelect: (id: string) => void;
 }) {
  return (
    <button
      key={m.id}
      type="button"
      onClick={() => onSelect(m.id)}
      className={`animate-stagger flex w-full items-start gap-3 rounded-[12px] border p-3 text-left transition-colors ${
        selected === m.id
          ? "border-signal/40 bg-signal/8"
          : "border-hairline bg-white/[0.03] hover:bg-white/[0.06]"
      }`}
    >
      <Avatar src={m.avatar_url} name={m.username} size={34} />
      <div className="min-w-0 flex-1">
        <div className="flex items-center gap-2">
          <span className="truncate text-sm font-semibold text-ink">
            {m.username}
          </span>
          <span className="mono text-[0.65rem] text-ink-faint">
            {getMessageChannelLabel(m)}
          </span>
          <span className="mono ml-auto text-[0.6rem] text-ink-faint">
            {formatRelativeTime(m.created_at)}
          </span>
        </div>
        <div className="mt-0.5 line-clamp-2 text-sm text-ink-soft">
          {renderMessageContent(m.content, m.metadata) || (
            <span className="italic text-ink-faint">(empty / embed)</span>
          )}
        </div>
      </div>
      <AiBadge status={m.ai_status} durationMs={m.ai_analysis_duration_ms} />
    </button>
  );
 }
@@ -15,13 +15,18 @@ import {
 } from "lucide-react";
 import { useEffect, useState } from "react";
 import { useAmbient } from "@/components/ambient/ambient-context";
 import { CategoryDrilldown } from "@/components/CategoryDrilldown";
 import { CoverageTiles } from "@/components/CoverageTiles";
 import { Donut } from "@/components/charts";
 import { LiveModerationFeed } from "@/components/LiveModerationFeed";
 import { ModerationHeatmap } from "@/components/ModerationHeatmap";
 import {
  Badge,
  GlassPanel,
  Select,
  type SelectOption,
 } from "@/components/primitives";
 import { ScamDomains } from "@/components/ScamDomains";
 import {
  ErrorState,
  MetricTile,
@@ -30,8 +35,21 @@ import {
  SkeletonPanel,
  SkeletonRows,
 } from "@/components/shared";
-import { useModerationActions, useModerationStats } from "@/hooks";
+import { TopChannels } from "@/components/TopChannels";
 import { TopicTrends } from "@/components/TopicTrends";
 import {
  useHourlyModeration,
  useLiveModeration,
  useModerationActions,
  useModerationByCategory,
  useModerationCoverage,
  useModerationStats,
  useModerationTrends,
  useTopFlaggedChannels,
  useTopFlaggedDomains,
 } from "@/hooks";
 import { aiTone } from "@/lib/ai-status";
 import { downloadCsv } from "@/lib/csv";
 import { formatNumber, formatRelativeTime } from "@/lib/format";
 import type {
  ModerationAction,
@@ -71,6 +89,15 @@ export function ModerationView({
    typeFilter || undefined,
    !statusFilter && !typeFilter ? initialActions : undefined,
  );
  const liveActions = useLiveModeration(initialActions ?? [], 50);
  const { data: trends } = useModerationTrends(30);
  const { data: domains } = useTopFlaggedDomains(30);
  const { data: channels } = useTopFlaggedChannels(30);
  const { data: hourly } = useHourlyModeration(30);
  const { data: coverage } = useModerationCoverage(30);
  const [drilldown, setDrilldown] = useState<string | null>(null);
  const { data: categoryActions, isValidating: categoryLoading } =
    useModerationByCategory(drilldown ? 30 : 0, drilldown);
  const failedRate = stats ? stats.failed_rate * 100 : 0;
@@ -150,6 +177,56 @@ export function ModerationView({
      </div>
      <div className="grid gap-5 lg:grid-cols-5">
        <div className="lg:col-span-2">
          {trends ? (
            <TopicTrends trends={trends} />
          ) : (
            <SkeletonPanel rows={6} />
          )}
        </div>
        <div className="lg:col-span-5">
          <LiveModerationFeed actions={liveActions} />
        </div>
        {coverage ? (
          <CoverageTiles coverage={coverage} />
        ) : (
          <SkeletonPanel rows={3} className="lg:col-span-5" />
        )}
        <div className="lg:col-span-2">
          {domains ? (
            <ScamDomains domains={domains} />
          ) : (
            <SkeletonPanel rows={6} />
          )}
        </div>
        <div className="lg:col-span-2">
          {hourly ? (
            <ModerationHeatmap hours={hourly} />
          ) : (
            <SkeletonPanel rows={6} />
          )}
        </div>
        <div className="lg:col-span-1">
          {channels ? (
            <TopChannels channels={channels} />
          ) : (
            <SkeletonPanel rows={6} />
          )}
        </div>
        <div className="lg:col-span-3">
          <CategoryDrilldown
            trends={trends ?? { categories: [], severities: [], actions: [] }}
            selected={drilldown}
            actions={categoryActions ?? []}
            loading={categoryLoading}
            onSelect={setDrilldown}
          />
        </div>
        <GlassPanel className="lg:col-span-2">
          <SectionHeader eyebrow="health" title="Breakdown" />
          <div className="flex items-center gap-5">
@@ -215,6 +292,30 @@ export function ModerationView({
                  size="sm"
                  className="w-32"
                />
                <button
                  type="button"
                  onClick={() =>
                    downloadCsv(
                      "moderation-actions.csv",
                      (actions ?? []).map((a) => ({
                        id: a.id,
                        user: a.username ?? a.user_id,
                        action_type: a.action_type,
                        status: a.status,
                        severity: a.severity ?? "",
                        categories: (a.categories ?? []).join("|"),
                        reason: a.reason ?? "",
                        created_at: a.created_at
                          ? new Date(a.created_at).toISOString()
                          : "",
                      })),
                    )
                  }
                  className="rounded-full border border-hairline bg-white/[0.03] px-3 py-1 text-xs text-ink-soft transition-colors hover:bg-white/[0.06]"
                  title="Download moderation actions as CSV"
                >
                  CSV
                </button>
              </div>
            }
          />
@@ -0,0 +1,89 @@
 "use client";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import type { MessageActivityBucket } from "@/lib/types";
 const HOURS = Array.from({ length: 24 }, (_, i) => i);
 function heatColor(t: number): string {
  // t in [0,1] → signal gradient (dark → bright).
  if (t <= 0) return "var(--color-hairline)";
  return `rgba(45, 212, 191, ${0.15 + 0.85 * t})`;
 }
 export function ActivityHeatmap({
  buckets,
 }: {
  buckets: MessageActivityBucket[];
 }) {
  // Group by channel, find max count for normalization.
  const channels = Array.from(new Set(buckets.map((b) => b.channelId)));
  const byKey = new Map<string, number>();
  let max = 0;
  for (const b of buckets) {
    const k = `${b.channelId}:${b.hour}`;
    byKey.set(k, (byKey.get(k) ?? 0) + b.count);
    if ((byKey.get(k) ?? 0) > max) max = byKey.get(k) ?? 0;
  }
  if (buckets.length === 0) {
    return (
      <GlassPanel className="lg:col-span-2">
        <SectionHeader eyebrow="insight" title="Activity Heatmap" />
        <p className="py-6 text-center text-xs text-ink-faint">
          No message activity recorded yet.
        </p>
      </GlassPanel>
    );
  }
  return (
    <GlassPanel className="lg:col-span-5">
      <SectionHeader
        eyebrow="insight"
        title="Activity Heatmap"
        action={
          <span className="mono text-[0.65rem] text-ink-faint">
            {channels.length} channels · messages/hour
          </span>
        }
      />
      <div className="overflow-x-auto">
        <div className="min-w-[640px] space-y-1">
          {channels.map((ch) => (
            <div key={ch} className="flex items-center gap-2">
              <span className="mono w-24 shrink-0 truncate text-[0.6rem] text-ink-faint">
                {ch.slice(-6)}
              </span>
              <div className="flex flex-1 gap-0.5">
                {HOURS.map((h) => {
                  const c = byKey.get(`${ch}:${h}`) ?? 0;
                  const t = max > 0 ? c / max : 0;
                  return (
                    <div
                      key={h}
                      title={`${ch} · ${String(h).padStart(2, "0")}:00 — ${c} msgs`}
                      className="h-4 flex-1 rounded-[2px]"
                      style={{ background: heatColor(t) }}
                    />
                  );
                })}
              </div>
            </div>
          ))}
          <div className="flex items-center gap-2 pt-1">
            <span className="w-24 shrink-0" />
            <div className="flex flex-1 justify-between">
              {[0, 6, 12, 18, 23].map((h) => (
                <span key={h} className="mono text-[0.55rem] text-ink-faint">
                  {String(h).padStart(2, "0")}h
                </span>
              ))}
            </div>
          </div>
        </div>
      </div>
    </GlassPanel>
  );
 }
@@ -0,0 +1,128 @@
 "use client";
 import { ChevronRight } from "lucide-react";
 import { Badge, GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { formatNumber, formatRelativeTime } from "@/lib/format";
 import type { CategoryAction, ModerationTrends } from "@/lib/types";
 const SEVERITY_TONE: Record<
  string,
  "signal" | "amber" | "vermilion" | "neutral"
 > = {
  critical: "vermilion",
  high: "vermilion",
  medium: "amber",
  low: "signal",
  none: "neutral",
 };
 interface CategoryDrilldownProps {
  trends: ModerationTrends;
  selected?: string | null;
  actions?: CategoryAction[];
  loading?: boolean;
  onSelect: (category: string | null) => void;
 }
 export function CategoryDrilldown({
  trends,
  selected,
  actions,
  loading,
  onSelect,
 }: CategoryDrilldownProps) {
  const maxCat = trends.categories.reduce((m, c) => Math.max(m, c.count), 0);
  return (
    <GlassPanel className="lg:col-span-3">
      <SectionHeader eyebrow="drill-down" title="Flag Category" />
      {selected ? (
        <div className="mb-3 flex items-center gap-2">
          <button
            type="button"
            onClick={() => onSelect(null)}
            className="text-xs text-ink-soft hover:text-ink"
          >
            ← Back to all categories
          </button>
          <span className="text-xs text-ink-faint">
            / {selected} (
            {loading ? "loading…" : formatNumber(actions?.length ?? 0)} actions)
          </span>
        </div>
      ) : (
        <p className="mb-2 text-xs text-ink-faint">
          Click a category to list the underlying moderation actions.
        </p>
      )}
      {!selected ? (
        <div className="space-y-2">
          {trends.categories.map((c) => {
            const pct = maxCat > 0 ? Math.max(2, (c.count / maxCat) * 100) : 0;
            return (
              <button
                type="button"
                key={c.name}
                onClick={() => onSelect(c.name)}
                className="flex w-full items-center gap-3 text-left text-sm"
              >
                <span className="w-36 shrink-0 truncate text-ink-soft">
                  {c.name}
                </span>
                <div className="h-2 flex-1 overflow-hidden rounded-full bg-white/5">
                  <div
                    className="h-full rounded-full bg-signal"
                    style={{ width: `${pct}%` }}
                  />
                </div>
                <span className="mono w-10 text-right text-ink">
                  {formatNumber(c.count)}
                </span>
              </button>
            );
          })}
        </div>
      ) : (
        <div className="space-y-2">
          {loading && <p className="text-xs text-ink-faint">Loading…</p>}
          {!loading && actions && actions.length === 0 && (
            <p className="text-xs text-ink-faint">
              No actions in this category.
            </p>
          )}
          {actions?.slice(0, 12).map((a) => (
            <div key={a.id} className="flex items-start gap-2 text-sm">
              <Badge tone={SEVERITY_TONE[a.severity ?? "none"]}>
                {a.severity ?? "none"}
              </Badge>
              <div className="min-w-0 flex-1">
                <div className="flex flex-wrap items-baseline gap-x-2">
                  <span className="font-medium text-ink">{a.action_type}</span>
                  {a.username && (
                    <span className="text-ink-soft">@{a.username}</span>
                  )}
                  <span className="text-ink-faint mono text-xs">
                    {a.created_at ? formatRelativeTime(a.created_at) : ""}
                  </span>
                </div>
                {a.content && (
                  <p className="mt-0.5 line-clamp-2 text-ink-faint">
                    {a.content}
                  </p>
                )}
                {a.reason && (
                  <p className="mt-0.5 line-clamp-1 text-xs text-ink-faint">
                    Reason: {a.reason}
                  </p>
                )}
                <ChevronRight className="mt-1 size-3 text-ink-faint/50" />
              </div>
            </div>
          ))}
        </div>
      )}
    </GlassPanel>
  );
 }
@@ -0,0 +1,71 @@
 "use client";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { downloadCsv } from "@/lib/csv";
 import { formatRelativeTime } from "@/lib/format";
 import type { ChannelCultureRow } from "@/lib/types";
 export function ChannelCultureGlossary({
  cultures,
 }: {
  cultures: ChannelCultureRow[];
 }) {
  return (
    <GlassPanel className="lg:col-span-3">
      <SectionHeader
        eyebrow="culture"
        title="Channel Culture Glossary"
        action={
          cultures.length > 0 ? (
            <button
              type="button"
              onClick={() =>
                downloadCsv(
                  "channel-cultures.csv",
                  cultures.map((c) => ({
                    channel: c.channel_name ?? c.channel_id,
                    summary: c.culture_summary ?? "",
                    last_analyzed: c.last_analyzed_at ?? "",
                  })),
                )
              }
              className="flex items-center gap-1.5 text-xs text-ink-soft hover:text-ink"
            >
              CSV
            </button>
          ) : null
        }
      />
      {cultures.length === 0 ? (
        <p className="py-6 text-center text-xs text-ink-faint">
          No channel cultures captured yet.
        </p>
      ) : (
        <div className="space-y-3">
          {cultures.map((c) => (
            <div key={c.channel_id} className="text-sm">
              <div className="flex items-baseline justify-between">
                <span className="font-medium text-ink">
                  {c.channel_name ?? c.channel_id}
                </span>
                {c.last_analyzed_at && (
                  <span className="text-xs text-ink-faint">
                    {formatRelativeTime(c.last_analyzed_at)}
                  </span>
                )}
              </div>
              {c.culture_summary ? (
                <p className="mt-1 text-ink-faint">{c.culture_summary}</p>
              ) : (
                <span className="text-xs text-ink-faint">
                  (no summary captured)
                </span>
              )}
            </div>
          ))}
        </div>
      )}
    </GlassPanel>
  );
 }
@@ -0,0 +1,46 @@
 "use client";
 import { AlertCircle, CheckCircle2, XCircle } from "lucide-react";
 import { GlassPanel } from "@/components/primitives";
 import { MetricTile, SectionHeader } from "@/components/shared";
 import { formatNumber } from "@/lib/format";
 import type { ModerationCoverage } from "@/lib/types";
 export function CoverageTiles({ coverage }: { coverage: ModerationCoverage }) {
  const pct = (n: number) => `${n.toFixed(1)}%`;
  return (
    <GlassPanel className="lg:col-span-5">
      <SectionHeader eyebrow="automation" title="Auto-mod Coverage" />
      <div className="grid grid-cols-2 gap-3 md:grid-cols-4">
        <MetricTile
          label="Coverage"
          value={pct(coverage.coverage_rate)}
          tone={coverage.coverage_rate > 90 ? "signal" : "amber"}
          icon={<CheckCircle2 className="size-3.5" />}
        />
        <MetricTile
          label="Completed"
          value={formatNumber(coverage.completed)}
          tone="signal"
          icon={<CheckCircle2 className="size-3.5" />}
        />
        <MetricTile
          label="Failed"
          value={formatNumber(coverage.failed)}
          tone={coverage.failed > 0 ? "vermilion" : "neutral"}
          icon={<XCircle className="size-3.5" />}
        />
        <MetricTile
          label="Pending"
          value={formatNumber(coverage.pending)}
          tone={coverage.pending > 0 ? "amber" : "neutral"}
          icon={<AlertCircle className="size-3.5" />}
        />
      </div>
      <p className="mt-2 text-xs text-ink-faint">
        {pct(coverage.failed_rate)} of analysis runs failed. Total runs in
        window: {formatNumber(coverage.total)}.
      </p>
    </GlassPanel>
  );
 }
@@ -0,0 +1,68 @@
 "use client";
 import { Download, History } from "lucide-react";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { downloadCsv } from "@/lib/csv";
 import { formatRelativeTime } from "@/lib/format";
 import type { EditHistoryRow } from "@/lib/types";
 export function EditHistory({ edits }: { edits: EditHistoryRow[] }) {
  return (
    <GlassPanel className="lg:col-span-4">
      <SectionHeader
        eyebrow="evasion"
        title="Message Edits"
        action={
          edits.length > 0 ? (
            <button
              type="button"
              onClick={() =>
                downloadCsv(
                  "message-edits.csv",
                  edits.map((e) => ({
                    author: e.username ?? "",
                    channel: e.channel_name ?? "",
                    old_content: e.old_content,
                    edited_at: e.edited_at,
                  })),
                )
              }
              className="flex items-center gap-1.5 text-xs text-ink-soft hover:text-ink"
            >
              <Download className="size-3.5" />
              CSV
            </button>
          ) : null
        }
      />
      {edits.length === 0 ? (
        <p className="py-6 text-center text-xs text-ink-faint">
          No edited messages recorded recently.
        </p>
      ) : (
        <div className="space-y-3">
          {edits.map((e) => (
            <div key={e.id} className="text-sm">
              <div className="flex flex-wrap items-baseline justify-between gap-2">
                <span className="font-medium text-ink">
                  {e.username ?? "unknown"}
                </span>
                <span className="text-xs text-ink-faint">
                  edited {formatRelativeTime(e.edited_at)} ·{" "}
                  {e.channel_name ?? e.channel_id ?? "unknown channel"}
                </span>
              </div>
              <div className="mt-1 flex items-start gap-1.5">
                <History className="mt-0.5 size-3.5 shrink-0 text-ink-faint/50" />
                <pre className="line-clamp-2 whitespace-pre-wrap break-words text-ink-faint/80">
                  {e.old_content || <em>(content not available)</em>}
                </pre>
              </div>
            </div>
          ))}
        </div>
      )}
    </GlassPanel>
  );
 }
@@ -0,0 +1,102 @@
 "use client";
 import { Badge, GlassPanel } from "@/components/primitives";
 import { formatRelativeTime } from "@/lib/format";
 import type { ModerationAction } from "@/lib/types";
 const ACTION_LABEL: Record<string, string> = {
  delete_message: "Deleted",
  timeout_user: "Timeout",
  warn_user: "Warned",
  reset_nickname: "Nickname reset",
  ban_user: "Banned",
  kick_user: "Kicked",
  notify_user: "Notified",
  none: "None",
 };
 function severityTone(
  sev?: string | null,
 ): "signal" | "amber" | "vermilion" | null {
  switch (sev) {
    case "critical":
    case "high":
      return "vermilion";
    case "medium":
      return "amber";
    case "low":
      return "signal";
    default:
      return null;
  }
 }
 export function LiveModerationFeed({
  actions,
 }: {
  actions: ModerationAction[];
 }) {
  return (
    <GlassPanel className="flex max-h-[420px] flex-col">
      <div className="flex items-center justify-between border-b border-white/10 px-4 py-3">
        <div className="flex items-center gap-2">
          <span className="relative flex size-2.5">
            <span className="absolute inline-flex size-full animate-ping rounded-full bg-emerald-400 opacity-75" />
            <span className="relative inline-flex size-2.5 rounded-full bg-emerald-500" />
          </span>
          <h3 className="text-sm font-medium text-ink">Live Feed</h3>
        </div>
        <span className="text-xs text-ink-faint">{actions.length} recent</span>
      </div>
      <div className="flex-1 overflow-y-auto">
        {actions.length === 0 ? (
          <p className="px-4 py-6 text-center text-xs text-ink-faint">
            Waiting for new moderation actions…
          </p>
        ) : (
          <ul className="divide-y divide-white/5">
            {actions.map((a, i) => {
              const tone = severityTone(a.severity);
              return (
                <li
                  key={a.id}
                  className={`flex items-start gap-3 px-4 py-3 ${
                    i === 0 ? "animate-[fadeIn_0.4s_ease-out]" : ""
                  }`}
                >
                  <div className="min-w-0 flex-1">
                    <div className="flex items-center gap-2">
                      <Badge tone={tone ?? "signal"} className="capitalize">
                        {ACTION_LABEL[a.action_type] ?? a.action_type}
                      </Badge>
                      {a.severity && (
                        <span className="text-xs text-ink-faint">
                          {a.severity}
                        </span>
                      )}
                      {a.categories?.length ? (
                        <span className="truncate text-xs text-ink-soft">
                          {a.categories.slice(0, 3).join(", ")}
                        </span>
                      ) : null}
                    </div>
                    {a.reason && (
                      <p className="mt-1 truncate text-xs text-ink-soft">
                        “{a.reason}”
                      </p>
                    )}
                    <p className="mt-0.5 text-[11px] text-ink-faint">
                      {a.username ?? a.user_id ?? "unknown"} ·{" "}
                      {formatRelativeTime(a.created_at)}
                    </p>
                  </div>
                </li>
              );
            })}
          </ul>
        )}
      </div>
    </GlassPanel>
  );
 }
@@ -0,0 +1,53 @@
 "use client";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import type { HourlyModeration } from "@/lib/types";
 import { cn } from "@/lib/utils";
 export function ModerationHeatmap({ hours }: { hours: HourlyModeration[] }) {
  const max = hours.reduce((m, h) => Math.max(m, h.total), 0);
  const intensity = (v: number) => {
    if (max <= 0) return "bg-white/5";
    const t = Math.max(0, Math.min(1, v / max));
    if (t < 0.25) return "bg-white/[0.06]";
    if (t < 0.5) return "bg-signal/25";
    if (t < 0.75) return "bg-signal/50";
    return "bg-vermilion/60";
  };
  return (
    <GlassPanel className="lg:col-span-2">
      <SectionHeader eyebrow="timing" title="Flagged by Hour (24h)" />
      <p className="mb-3 text-xs text-ink-faint">
        Distribution of moderation actions across the day.
      </p>
      <div className="grid grid-cols-2 gap-4 md:grid-cols-3">
        {hours.map((h) => (
          <div key={h.hour} className="flex items-center gap-2">
            <span className="w-8 text-xs text-ink-faint mono">
              {String(h.hour).padStart(2, "0")}:00
            </span>
            <div className="flex-1">
              <div
                className={cn(
                  "h-5 rounded transition-colors",
                  intensity(h.total),
                )}
                title={`${h.total} actions`}
              />
            </div>
            <span
              className={cn(
                "mono w-8 text-right text-xs",
                h.total === 0 ? "text-ink-faint/40" : "text-ink",
              )}
            >
              {h.total}
            </span>
          </div>
        ))}
      </div>
    </GlassPanel>
  );
 }
@@ -0,0 +1,67 @@
 "use client";
 import { Download } from "lucide-react";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { downloadCsv } from "@/lib/csv";
 import { formatNumber } from "@/lib/format";
 import type { FlaggedDomain } from "@/lib/types";
 export function ScamDomains({ domains }: { domains: FlaggedDomain[] }) {
  const max = domains.reduce((m, d) => Math.max(m, d.count), 0);
  return (
    <GlassPanel className="lg:col-span-2">
      <SectionHeader
        eyebrow="risk"
        title="Flagged Link Domains"
        action={
          domains.length > 0 ? (
            <button
              type="button"
              onClick={() =>
                downloadCsv(
                  "flagged-domains.csv",
                  domains.map((d) => ({
                    domain: d.domain,
                    flagged_count: d.count,
                  })),
                )
              }
              className="flex items-center gap-1.5 text-xs text-ink-soft hover:text-ink"
            >
              <Download className="size-3.5" />
              CSV
            </button>
          ) : null
        }
      />
      {domains.length === 0 ? (
        <p className="py-6 text-center text-xs text-ink-faint">
          No flagged links captured recently.
        </p>
      ) : (
        <div className="space-y-2">
          {domains.map((d) => {
            const pct = max > 0 ? Math.max(2, (d.count / max) * 100) : 0;
            return (
              <div key={d.domain} className="flex items-center gap-3 text-sm">
                <span className="w-44 shrink-0 truncate font-mono text-ink-soft">
                  {d.domain}
                </span>
                <div className="h-2 flex-1 overflow-hidden rounded-full bg-white/5">
                  <div
                    className="h-full rounded-full bg-[#8b5cf6]"
                    style={{ width: `${pct}%` }}
                  />
                </div>
                <span className="mono w-10 shrink-0 text-right text-ink">
                  {formatNumber(d.count)}
                </span>
              </div>
            );
          })}
        </div>
      )}
    </GlassPanel>
  );
 }
@@ -0,0 +1,71 @@
 "use client";
 import { Globe } from "lucide-react";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { downloadCsv } from "@/lib/csv";
 import { formatRelativeTime } from "@/lib/format";
 import type { GlossaryRow } from "@/lib/types";
 export function TermGlossary({ terms }: { terms: GlossaryRow[] }) {
  return (
    <GlassPanel className="lg:col-span-3">
      <SectionHeader
        eyebrow="knowledge"
        title="Term Knowledge Base"
        action={
          terms.length > 0 ? (
            <button
              type="button"
              onClick={() =>
                downloadCsv(
                  "glossary.csv",
                  terms.map((t) => ({
                    term: t.term,
                    definition: t.definition,
                    source: t.source_url,
                    resolved: t.resolved_at,
                    hits: t.hit_count,
                  })),
                )
              }
              className="flex items-center gap-1.5 text-xs text-ink-soft hover:text-ink"
            >
              CSV
            </button>
          ) : null
        }
      />
      {terms.length === 0 ? (
        <p className="py-6 text-center text-xs text-ink-faint">
          No term resolutions cached yet.
        </p>
      ) : (
        <div className="space-y-3">
          {terms.map((t) => (
            <div key={t.term} className="text-sm">
              <div className="flex flex-wrap items-baseline justify-between gap-2">
                <span className="font-medium text-ink">{t.term}</span>
                <span className="text-xs text-ink-faint">
                  {t.hit_count} uses · {formatRelativeTime(t.resolved_at)}
                </span>
              </div>
              <p className="mt-1 text-ink-faint">{t.definition}</p>
              {t.source_url && (
                <a
                  href={t.source_url}
                  target="_blank"
                  rel="noopener noreferrer"
                  className="mt-0.5 text-xs text-ink-soft hover:text-ink"
                >
                  <Globe className="mr-1 inline size-3" />
                  {t.source_url}
                </a>
              )}
            </div>
          ))}
        </div>
      )}
    </GlassPanel>
  );
 }
@@ -0,0 +1,72 @@
 "use client";
 import { Download } from "lucide-react";
 import { GlassPanel } from "@/components/primitives";
 import { SectionHeader } from "@/components/shared";
 import { downloadCsv } from "@/lib/csv";
 import { formatNumber } from "@/lib/format";
 import type { FlaggedChannel } from "@/lib/types";
 export function TopChannels({ channels }: { channels: FlaggedChannel[] }) {
  const max = channels.reduce((m, c) => Math.max(m, c.flagged_count), 0);
  return (
    <GlassPanel className="lg:col-span-2">
      <SectionHeader
        eyebrow="channels"
        title="Top Flagged Channels"
        action={
          channels.length > 0 ? (
            <button
              type="button"
              onClick={() =>
                downloadCsv(
                  "flagged-channels.csv",
                  channels.map((c) => ({
                    channel_id: c.channel_id,
                    channel_name: c.channel_name ?? "",
                    flagged_count: c.flagged_count,
                  })),
                )
              }
              className="flex items-center gap-1.5 text-xs text-ink-soft hover:text-ink"
            >
              <Download className="size-3.5" />
              CSV
            </button>
          ) : null
        }
      />
      {channels.length === 0 ? (
        <p className="py-6 text-center text-xs text-ink-faint">
          No flagged activity in the selected period.
        </p>
      ) : (
        <div className="space-y-2">
          {channels.map((c) => {
            const pct =
              max > 0 ? Math.max(2, (c.flagged_count / max) * 100) : 0;
            return (
              <div
                key={c.channel_id}
                className="flex items-center gap-3 text-sm"
              >
                <span className="w-40 shrink-0 truncate text-ink-soft">
                  {c.channel_name ?? c.channel_id}
                </span>
                <div className="h-2 flex-1 overflow-hidden rounded-full bg-white/5">
                  <div
                    className="h-full rounded-full bg-[#f59e0b]"
                    style={{ width: `${pct}%` }}
                  />
                </div>
                <span className="mono w-10 shrink-0 text-right text-ink">
                  {formatNumber(c.flagged_count)}
                </span>
              </div>
            );
          })}
        </div>
      )}
    </GlassPanel>
  );
 }
Author	SHA1	Message	Date
asepharyana	b784d6d796	feat(gateway): weekly moderation digest via WEBHOOK_URLS (#15 ) Automated public weekly summary: top categories/domains/channels + coverage rate, posted to configured webhook. Uses getDatabase() direct query (no oRPC HTTP dependency), guards one-fire-per-week on restart.	2026-08-18 20:51:12 +07:00
asepharyana	00e8d68ce5	feat(gmw): public features #7-14 — scam domains, top channels, hourly heatmap, category drill-down, coverage stats, channel culture glossary, term KB, edit history ALSO fixes: dashboard.repository still JOINed dropped user_reputations table (listUsers/getUserDetail crash).	2026-08-18 20:45:12 +07:00
asepharyana	2a8f6d9062	refactor(gateway): remove user reputation feature entirely Drop trust-score/infraction system: delete userReputationStore, remove call sites in fallback/batch processors, drop formatReputationAttrs, drop user_reputations table (migration 0016), delete trust-model test, update docs.	2026-08-18 18:27:15 +07:00
asepharyana	9b3134d767	feat(gmw): public features #2-#6 — live moderation feed, toxic topic trends, channel timeline, CSV export, activity heatmap - Live Moderation Feed: gateway publishes discord:moderation:action (Redis) → backend WS emits moderation_action → public web shows realtime stream. - Toxic Topic Trends: backend moderation.trends aggregates categories/severity/action_type (read-only) → SVG bar + donut. - Channel Timeline: messages view gets Feed/Timeline toggle with date-grouped separators. - CSV Export: client-side downloadCsv for moderation actions (no backend write scope). - Activity Heatmap: backend messages.activity (per-hour volume by channel) → pure-SVG grid. User reputation deliberately excluded — no such feature exists in the codebase. All read-only / public-facing / fully automatic per project rules.	2026-08-18 17:43:02 +07:00
asepharyana	36363fa3db	fix(gateway): skip bot-only channel 1318544753821880362 from capture Add to BOT_EXCLUDED_CHANNEL_IDS default alongside 1206269771340058694 so bot messages in that channel are no longer captured/analyzed/embedded.	2026-08-18 16:13:25 +07:00
asepharyana	d133cc3271	style(gateway): sort imports in archiveEmbedder (biome)	2026-08-18 15:53:18 +07:00
asepharyana	5a70a685b4	fix(gateway): correct @/ alias import style (no .js) in archiveEmbedder Gateway @/ alias imports use no .js extension (relative imports keep .js). The .js suffix on @/ paths caused double-extension ERR_MODULE_NOT_FOUND (embeddingClient.js.js) at runtime.	2026-08-18 15:44:18 +07:00
asepharyana	100b62800c	fix(backend): drop .js extension on @/ alias imports (embed/qdrant) Backend uses extensionless @/ alias imports; the double .js caused ERR_MODULE_NOT_FOUND at runtime (index.js.js).	2026-08-18 15:19:05 +07:00