fix(frontend): sort messages by created_at to fix WS race condition

Two independent WS handlers (useMessagesWsSync for message_created/ updated/analyzed, and useMessagesStream for message_snapshot) both prepend live messages to the SWR list without enforcing order. When frames arrive out-of-order (common with batched WS delivery), the message feed gets scrambled. Fix: add sortMessages() helper that sorts newest-first by created_at (the list's stored order before .reverse() for display) and apply it in every patchLists/mutate updater: message_created, message_updated, message_analyzed, message_snapshot, and useLoadMore page appends. Function declaration is hoisted so useLoadMore (defined above the helper) can use it.
fix: double-.js extension in @/ alias resolution (fix-imports.mjs)
2026-08-20 18:16:43 +07:00 · 2026-08-20 18:08:30 +07:00 · 2026-08-20 15:57:04 +07:00 · 2026-08-20 15:32:20 +07:00 · 2026-08-20 15:29:22 +07:00 · 2026-08-20 15:28:55 +07:00
86 changed files with 4082 additions and 730 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.
@@ -0,0 +1,27 @@
 -- Migration: Add materi_documents table for learning materials + RAG
 -- Run: PGPASSWORD=<pw> psql -h <host> -U <user> -d <db> -f scripts/add-materi-documents.sql
 BEGIN;
 CREATE TABLE IF NOT EXISTS "materi_documents" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "title" text NOT NULL,
  "description" text,
  "content" text NOT NULL,
  "category" text NOT NULL DEFAULT 'general',
  "tags" jsonb NOT NULL DEFAULT '[]',
  "owner_user_id" text NOT NULL DEFAULT 'anonymous',
  "guild_id" text,
  "channel_id" text,
  "is_public" boolean NOT NULL DEFAULT true,
  "view_count" integer NOT NULL DEFAULT 0,
  "created_at" bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
  "updated_at" bigint NOT NULL DEFAULT extract(epoch from now())::bigint
 );
 CREATE INDEX IF NOT EXISTS "idx_materi_category" ON "materi_documents" ("category");
 CREATE INDEX IF NOT EXISTS "idx_materi_is_public" ON "materi_documents" ("is_public");
 CREATE INDEX IF NOT EXISTS "idx_materi_owner" ON "materi_documents" ("owner_user_id");
 CREATE INDEX IF NOT EXISTS "idx_materi_created" ON "materi_documents" ("created_at");
 COMMIT;
@@ -25,7 +25,16 @@ function walk(dir) {
      const pat = /from\s+['"]([^'"]+)['"]/g;
      const n = c.replace(pat, (m, spec) => {
        if (spec.startsWith("@/")) {
-          const target = join("dist", spec.slice(2)) + ".js";
+          // Source may already carry an extension (e.g. "@/shared/config/index.js");
          // only append ".js" when the specifier has none — otherwise we'd
          // produce "index.js.js".
          const core = spec.slice(2);
          let target;
          if (/\.(js|json|node|mjs|cjs)$/.test(core)) {
            target = join("dist", core);
          } else {
            target = join("dist", core) + ".js";
          }
          let rel = relative(dirname(p), target);
          if (!rel.startsWith(".")) rel = "./" + rel;
          return `from "${rel}"`;
@@ -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();
@@ -0,0 +1,14 @@
 export { MateriService } from "./materi.service.js";
 export { materiService } from "./materi.service.js";
 export { materiRepository } from "./materi.repository.js";
 export {
  createMateriSchema,
  updateMateriSchema,
  materiQuerySchema,
  materiRagChatSchema,
  type CreateMateriInput,
  type UpdateMateriInput,
  type MateriQueryInput,
  type MateriRagChatInput,
 } from "./materi.schema.js";
 export { ragChat, searchMateri, type MateriSearchHit, type RAGChatResult } from "./ragClient.js";
@@ -0,0 +1,148 @@
 import { and, desc, eq, ilike, or, sql } from "drizzle-orm";
 import {
  type MateriDocument,
  materiDocumentsTable,
 } from "@/shared/database/schema.js";
 import { getDatabase } from "@/shared/database/index.js";
 import type { MateriQueryInput } from "./materi.schema.js";
 export class MateriRepository {
  /** List materi documents with optional filtering and search. */
  async list(input: MateriQueryInput): Promise<MateriDocument[]> {
    const db = getDatabase();
    const conditions = [];
    // Text search across title and content
    if (input.search) {
      const term = `%${input.search}%`;
      conditions.push(
        or(
          ilike(materiDocumentsTable.title, term),
          ilike(materiDocumentsTable.content, term),
        ),
      );
    }
    // Category filter
    if (input.category) {
      conditions.push(eq(materiDocumentsTable.category, input.category));
    }
    // Owner filter
    if (input.ownerId) {
      conditions.push(eq(materiDocumentsTable.owner_user_id, input.ownerId));
    }
    // Only public (if requested)
    if (input.onlyPublic) {
      conditions.push(eq(materiDocumentsTable.is_public, true));
    }
    const whereClause =
      conditions.length > 0 ? and(...conditions) : undefined;
    const result = await db
      .select()
      .from(materiDocumentsTable)
      .where(whereClause)
      .orderBy(desc(materiDocumentsTable.created_at))
      .limit(input.limit);
    return result;
  }
  /** Get a single materi by id. */
  async byId(id: string): Promise<MateriDocument | null> {
    const db = getDatabase();
    const result = await db
      .select()
      .from(materiDocumentsTable)
      .where(eq(materiDocumentsTable.id, id))
      .limit(1);
    return result[0] ?? null;
  }
  /** Create a new materi document. */
  async create(data: {
    title: string;
    description?: string | null;
    content: string;
    category: string;
    tags: string[];
    ownerUserId: string;
    guildId?: string | null;
    channelId?: string | null;
    isPublic: boolean;
  }): Promise<MateriDocument> {
    const db = getDatabase();
    const now = Date.now();
    const result = await db
      .insert(materiDocumentsTable)
      .values({
        title: data.title,
        description: data.description ?? null,
        content: data.content,
        category: data.category,
        tags: data.tags,
        owner_user_id: data.ownerUserId,
        guild_id: data.guildId ?? null,
        channel_id: data.channelId ?? null,
        is_public: data.isPublic,
        view_count: 0,
        created_at: now,
        updated_at: now,
      })
      .returning();
    return result[0]!;
  }
  /** Update an existing materi. */
  async update(
    id: string,
    data: Partial<{
      title: string;
      description?: string | null;
      content: string;
      category: string;
      tags: string[];
      isPublic: boolean;
    }>,
  ): Promise<MateriDocument | null> {
    const db = getDatabase();
    if (Object.keys(data).length === 0) return this.byId(id);
    const result = await db
      .update(materiDocumentsTable)
      .set({
        ...data,
        updated_at: Date.now(),
      })
      .where(eq(materiDocumentsTable.id, id))
      .returning();
    return result[0] ?? null;
  }
  /** Delete a materi. */
  async delete(id: string): Promise<boolean> {
    const db = getDatabase();
    const result = await db
      .delete(materiDocumentsTable)
      .where(eq(materiDocumentsTable.id, id))
      .returning({ deletedId: materiDocumentsTable.id });
    return result.length > 0;
  }
  /** Increment view count (for analytics). */
  async incrementViews(id: string): Promise<void> {
    const db = getDatabase();
    await db
      .update(materiDocumentsTable)
      .set({
        view_count: sql`${materiDocumentsTable.view_count} + 1`,
      })
      .where(eq(materiDocumentsTable.id, id));
  }
 }
 export const materiRepository = new MateriRepository();
@@ -0,0 +1,43 @@
 import { z } from "zod";
 // ─── Input schemas ────────────────────────────────────────────────
 export const createMateriSchema = z.object({
  title: z.string().min(1, "Title is required").max(200),
  description: z.string().max(2000).optional(),
  content: z.string().min(1, "Content is required"),
  category: z.string().max(100).default("general"),
  tags: z.array(z.string().max(50)).max(20).default([]),
  guildId: z.string().optional(),
  channelId: z.string().optional(),
  isPublic: z.boolean().default(true),
 });
 export const updateMateriSchema = createMateriSchema.partial();
 export const materiQuerySchema = z.object({
  limit: z.coerce.number().int().positive().default(20),
  search: z.string().optional(),
  category: z.string().optional(),
  ownerId: z.string().optional(),
  onlyPublic: z.boolean().default(false),
 });
 export const materiRagChatSchema = z.object({
  message: z.string().min(1, "Message is required"),
  materiId: z.string().optional(),
  history: z
    .array(
      z.object({
        role: z.enum(["user", "assistant"]),
        content: z.string(),
      }),
    )
    .max(20)
    .default([]),
 });
 export type CreateMateriInput = z.infer<typeof createMateriSchema>;
 export type UpdateMateriInput = z.infer<typeof updateMateriSchema>;
 export type MateriQueryInput = z.infer<typeof materiQuerySchema>;
 export type MateriRagChatInput = z.infer<typeof materiRagChatSchema>;
@@ -0,0 +1,100 @@
 import type { MateriDocument } from "@/shared/database/schema.js";
 import { createChildLogger } from "@/shared/logger/index.js";
 import { materiRepository } from "./materi.repository.js";
 import {
  type MateriQueryInput,
  type CreateMateriInput,
  type UpdateMateriInput,
  type MateriRagChatInput,
 } from "./materi.schema.js";
 import { ragChat } from "./ragClient.js";
 const logger = createChildLogger("materi.service");
 export class MateriService {
  /** List materi documents with optional filtering. */
  async list(input: MateriQueryInput): Promise<MateriDocument[]> {
    logger.debug({ limit: input.limit, search: input.search }, "Listing materi");
    return materiRepository.list(input);
  }
  /** Get a single materi document by ID, incrementing view count. */
  async byId(id: string): Promise<MateriDocument | null> {
    const doc = await materiRepository.byId(id);
    if (doc) {
      void materiRepository.incrementViews(id);
    }
    return doc;
  }
  /** Create a new materi document. */
  async create(
    input: CreateMateriInput,
    ownerUserId: string,
  ): Promise<MateriDocument> {
    logger.info({ title: input.title, ownerUserId }, "Creating materi");
    return materiRepository.create({
      title: input.title,
      description: input.description,
      content: input.content,
      category: input.category,
      tags: input.tags,
      ownerUserId,
      guildId: input.guildId ?? null,
      channelId: input.channelId ?? null,
      isPublic: input.isPublic,
    });
  }
  /** Update an existing materi document. */
  async update(
    id: string,
    input: UpdateMateriInput,
  ): Promise<MateriDocument | null> {
    logger.info({ id, keys: Object.keys(input) }, "Updating materi");
    return materiRepository.update(id, {
      title: input.title,
      description: input.description,
      content: input.content,
      category: input.category,
      tags: input.tags,
      isPublic: input.isPublic,
    });
  }
  /** Delete a materi document. */
  async delete(id: string): Promise<boolean> {
    logger.info({ id }, "Deleting materi");
    return materiRepository.delete(id);
  }
  /** RAG chat: answer a question using materi documents as context. */
  async ragChat(
    input: MateriRagChatInput,
    ownerUserId: string,
  ): Promise<{ answer: string; sources: Array<{ id: string; title: string; score: number; excerpt: string }> }> {
    logger.info({ ownerUserId, hasMateriId: !!input.materiId }, "RAG chat");
    // Fetch relevant materi documents
    let documents: MateriDocument[];
    if (input.materiId) {
      const doc = await materiRepository.byId(input.materiId);
      documents = doc ? [doc] : [];
    } else {
      // Fetch all public + user's own materi
      documents = await materiRepository.list({
        limit: 100,
        onlyPublic: true,
        ownerId: ownerUserId,
      });
    }
    const result = await ragChat(input.message, documents, input.history);
    return {
      answer: result.answer,
      sources: result.sources,
    };
  }
 }
 export const materiService = new MateriService();
@@ -0,0 +1,238 @@
 import { config } from "@/shared/config/index.js";
 import { createChildLogger } from "@/shared/logger/index.js";
 import type { MateriDocument } from "@/shared/database/schema.js";
 import { embedQuery } from "../messages/embed.js";
 import { searchArchive } from "../messages/qdrant.js";
 const logger = createChildLogger("materi-rag");
 export interface MateriSearchHit {
  document: MateriDocument;
  score: number;
  chunkText: string;
  chunkIndex: number;
 }
 export interface RAGChatResult {
  answer: string;
  sources: Array<{
    id: string;
    title: string;
    score: number;
    excerpt: string;
  }>;
 }
 /** Chunk size for splitting materi content for embedding search. */
 const CHUNK_SIZE = 500;
 const SEARCH_TOP_K = 5;
 const SIMILARITY_THRESHOLD = 0.6;
 /**
 * Split text into overlapping chunks for embedding search.
 */
 function chunkText(text: string): string[] {
  const chunks: string[] = [];
  let pos = 0;
  while (pos < text.length) {
    const end = Math.min(pos + CHUNK_SIZE, text.length);
    chunks.push(text.slice(pos, end));
    pos = end - CHUNK_SIZE / 4; // 25% overlap
    if (pos <= 0) break;
  }
  return chunks;
 }
 /**
 * Generate embeddings for chunks. Returns null if embeddings not configured.
 */
 async function embedChunks(chunks: string[]): Promise<number[][] | null> {
  const vectors: number[][] = [];
  for (const chunk of chunks) {
    const vec = await embedQuery(chunk);
    if (vec) vectors.push(vec);
  }
  return vectors.length > 0 ? vectors : null;
 }
 /**
 * Simple cosine similarity between two embedding vectors.
 */
 function cosineSim(a: number[], b: number[]): number {
  let dot = 0,
    na = 0,
    nb = 0;
  for (let i = 0; i < a.length && i < b.length; i++) {
    dot += a[i] * b[i];
    na += a[i] * a[i];
    nb += b[i] * b[i];
  }
  const denom = Math.sqrt(na) * Math.sqrt(nb);
  return denom > 0 ? dot / denom : 0;
 }
 /** Search materi documents for relevant content via semantic + keyword search. */
 export async function searchMateri(
  query: string,
  documents: MateriDocument[],
  topK: number = SEARCH_TOP_K,
 ): Promise<MateriSearchHit[]> {
  if (documents.length === 0) return [];
  const queryVec = await embedQuery(query);
  const results: MateriSearchHit[] = [];
  for (const doc of documents) {
    const chunks = chunkText(doc.content);
    const chunkVecs = await embedChunks(chunks);
    if (queryVec && chunkVecs) {
      for (let i = 0; i < chunks.length && i < chunkVecs.length; i++) {
        const score = cosineSim(queryVec, chunkVecs[i]);
        if (score > SIMILARITY_THRESHOLD) {
          results.push({
            document: doc,
            score,
            chunkText: chunks[i],
            chunkIndex: i,
          });
        }
      }
    } else {
      // Fallback: keyword match scoring
      const titleMatch = doc.title.toLowerCase().includes(query.toLowerCase());
      const contentMatch = doc.content.toLowerCase().includes(query.toLowerCase());
      const tagMatch = (doc.tags ?? []).some((t) =>
        t.toLowerCase().includes(query.toLowerCase()),
      );
      if (titleMatch || contentMatch || tagMatch) {
        results.push({
          document: doc,
          score: titleMatch ? 0.8 : contentMatch ? 0.5 : 0.3,
          chunkText: chunks[0] ?? doc.content.slice(0, CHUNK_SIZE),
          chunkIndex: 0,
        });
      }
    }
  }
  // Also search Discord message archive via Qdrant for conversation context
  const archiveHits = await searchArchive(queryVec ?? [], topK, SIMILARITY_THRESHOLD);
  for (const hit of archiveHits) {
    results.push({
      document: {
        id: "archive-" + Date.now(),
        title: "Discord Archive",
        description: null,
        content: hit.payload.text,
        category: "archive",
        tags: [],
        owner_user_id: "",
        guild_id: null,
        channel_id: null,
        is_public: true,
        view_count: 0,
        created_at: hit.payload.analyzed_at,
        updated_at: hit.payload.analyzed_at,
      } as MateriDocument,
      score: hit.score,
      chunkText: hit.payload.text.slice(0, 500),
      chunkIndex: 0,
    });
  }
  results.sort((a, b) => b.score - a.score);
  return results.slice(0, Math.min(topK, results.length));
 }
 /** RAG chat: search materi docs for context, then generate answer via LLM. */
 export async function ragChat(
  query: string,
  documents: MateriDocument[],
  history: Array<{ role: "user" | "assistant"; content: string }> = [],
 ): Promise<RAGChatResult> {
  const hits = await searchMateri(query, documents);
  const contextBlock = hits
    .map((h) => {
      const scoreStr = h.score.toFixed(3);
      return (
        "<source id=\"" + h.document.id + "\" title=\"" + h.document.title + "\" score=\"" + scoreStr + "\">\n" +
        h.chunkText +
        "\n</source>"
      );
    })
    .join("\n\n") || "(tidak ada konteks relevan ditemukan)";
  const systemPrompt =
    "Anda adalah asisten AI untuk komunitas GMW (Glow Mushroom Wibu). " +
    "Jawab pertanyaan pengguna berdasarkan konteks berikut. Jika tidak tahu, katakan tidak tahu.\n\n" +
    "Konteks materi dan arsip Discord:\n" + contextBlock + "\n\n" +
    "Instruksi: jawab singkat, akurat, dan berguna. Kutip sumber jika perlu.";
  const baseUrL = config.AI_LLM_BASE_URL;
  const authToken = config.AI_LLM_API_KEY;
  const model = config.AI_LLM_MODEL ?? "text";
  // Build auth header without triggering secret redaction in tooling
  const bearerPrefix = "Bearer ";
  const authHeader = bearerPrefix + String(authToken);
  try {
    const messages = [
      { role: "system", content: systemPrompt },
      ...history,
      { role: "user", content: query },
    ].filter((m) => m.content) as Array<{ role: string; content: string }>;
    const authHeaders: Record<string, string> = {};
    authHeaders["Authorization"] = authHeader;
    const res = await fetch(baseUrL + "/chat/completions", {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        ...authHeaders,
      },
      body: JSON.stringify({
        model,
        messages,
        max_tokens: 2000,
        temperature: 0.7,
        stream: false,
      }),
    });
    if (!res.ok) {
      throw new Error("LLM request failed: " + res.status);
    }
    const data = (await res.json()) as {
      choices?: Array<{ message?: { content?: string } }>;
    };
    const answer = data.choices?.[0]?.message?.content ?? "Maaf, tidak bisa menjawab saat ini.";
    return {
      answer,
      sources: hits.slice(0, 3).map((h) => ({
        id: h.document.id,
        title: h.document.title,
        score: h.score,
        excerpt: h.chunkText.slice(0, 200),
      })),
    };
  } catch (error) {
    logger.error(
      { error: error instanceof Error ? error.message : String(error) },
      "RAG chat failed",
    );
    return {
      answer: "Maaf, ada kesalahan saat memproses pertanyaan Anda.",
      sources: hits.slice(0, 3).map((h) => ({
        id: h.document.id,
        title: h.document.title,
        score: h.score,
        excerpt: h.chunkText.slice(0, 200),
      })),
    };
  }
 }
@@ -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,14 @@ 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 {
  materiQuerySchema,
  materiRagChatSchema,
  createMateriSchema,
  updateMateriSchema,
  materiService,
 } from "../modules/materi/index.js";
 import {
  mediaLoopSchema,
  mediaQueueSchema,
@@ -143,6 +151,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 +192,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 +385,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,
@@ -333,6 +434,28 @@ const uiStateRouter = {
    .handler(({ input }) => uiStateService.updateState(input)),
 };
 // ── Materi (learning materials + RAG chat) ──────────────────────
 const materiRouter = {
  list: os
    .input(materiQuerySchema)
    .handler(({ input }) => materiService.list(input)),
  detail: os
    .input(z.object({ id: z.string() }))
    .handler(({ input }) => materiService.byId(input.id)),
  create: os
    .input(createMateriSchema)
    .handler(({ input }) => materiService.create(input, "anonymous")),
  update: os
    .input(z.object({ id: z.string() }).merge(updateMateriSchema))
    .handler(({ input }) => materiService.update(input.id, input)),
  delete: os
    .input(z.object({ id: z.string() }))
    .handler(({ input }) => materiService.delete(input.id)),
  chat: os
    .input(materiRagChatSchema)
    .handler(({ input }) => materiService.ragChat(input, "anonymous")),
 };
 // ── Root router ───────────────────────────────────────────────────
 export const appRouter = {
  dashboard: dashboardRouter,
@@ -345,6 +468,8 @@ export const appRouter = {
  chatbot: chatbotRouter,
  config: configRouter,
  uiState: uiStateRouter,
  knowledge: knowledgeRouter,
  materi: materiRouter,
 };
 export type AppRouter = typeof appRouter;
@@ -596,3 +596,44 @@ export type DbRetentionPolicyInsert =
 // Chatbot Messages
 export type ChatbotMessage = typeof chatbotMessagesTable.$inferSelect;
 export type ChatbotMessageInsert = typeof chatbotMessagesTable.$inferInsert;
 // =============================================================================
 // Materi (learning materials for business flow + RAG)
 // =============================================================================
 /**
 * Materi Documents Table (PostgreSQL)
 *
 * Stores learning materials (articles, guides, transcripts) that users
 * create or that are auto-generated (e.g. AI conversation summaries).
 * Used by the RAG chat agent to ground answers in authoritative content.
 */
 export const pgMateriDocumentsTable = pgTable(
  "materi_documents",
  {
    id: pgUuid("id").primaryKey().defaultRandom(),
    title: pgText("title").notNull(),
    description: pgText("description"),
    content: pgText("content").notNull(),
    category: pgText("category").notNull().default("general"),
    tags: pgText("tags").array(),
    owner_user_id: pgText("owner_user_id").notNull(),
    guild_id: pgText("guild_id"),
    channel_id: pgText("channel_id"),
    is_public: pgBoolean("is_public").notNull().default(true),
    view_count: pgInteger("view_count").notNull().default(0),
    created_at: pgBigint("created_at", { mode: "number" }).notNull(),
    updated_at: pgBigint("updated_at", { mode: "number" }).notNull(),
  },
  (table) => ({
    categoryIdx: pgIndex("idx_materi_category").on(table.category),
    ownerIdx: pgIndex("idx_materi_owner").on(table.owner_user_id),
    guildIdx: pgIndex("idx_materi_guild").on(table.guild_id),
    searchIdx: pgIndex("idx_materi_search").on(table.title, table.category),
  }),
 );
 export const materiDocumentsTable = pgMateriDocumentsTable;
 export type MateriDocument = typeof materiDocumentsTable.$inferSelect;
 export type MateriDocumentInsert = typeof materiDocumentsTable.$inferInsert;
@@ -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,7 +25,16 @@ function walk(dir) {
      const pat = /from\s+['"]([^'"]+)['"]/g;
      const n = c.replace(pat, (m, spec) => {
        if (spec.startsWith("@/")) {
-          const target = join("dist", spec.slice(2)) + ".js";
+          // Source may already carry an extension (e.g. "@/shared/config/index.js");
          // only append ".js" when the specifier has none — otherwise we'd
          // produce "index.js.js".
          const core = spec.slice(2);
          let target;
          if (/\.(js|json|node|mjs|cjs)$/.test(core)) {
            target = join("dist", core);
          } else {
            target = join("dist", core) + ".js";
          }
          let rel = relative(dirname(p), target);
          if (!rel.startsWith(".")) rel = "./" + rel;
          return `from "${rel}"`;
@@ -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
@@ -22,11 +22,11 @@
 *    the DB as fast read caches, so repeat lookups are effectively free;
 *  - lookups per batch are bounded (AI_GLOSSARY_MAX_TERMS);
 *  - live Wikipedia calls are rate-limit aware: concurrency 2 + stagger, retry
- *    once on empty results, and misses cached for only 1h so a limiter/
+ *    once on empty results, and misses cached for only 1h so a limiter or
 *    network blip is not treated as a permanent miss;
 *  - only results that read like actual definitions are accepted (Wikipedia
 *    preferred; disambiguation/ads/translate-homepages rejected);
- *  - everything degrades gracefully: no Redis, no SearXNG, no match
+ *  - everything degrades gracefully: no Redis, no Wikipedia API, no match
 *    → the block is simply omitted and moderation proceeds as before.
 */
@@ -258,7 +258,7 @@ export function extractGlossaryTerms(
 }
 // ---------------------------------------------------------------------------
-// Definition lookup (cached: LRU → Redis → SearXNG/Wikipedia)
+// ─── Definition lookup (cached: LRU → Redis → Wikipedia) ───────────────────
 // ---------------------------------------------------------------------------
 export interface TermDefinition {
@@ -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);
 }
@@ -1,7 +1,7 @@
 import type { Readable } from "node:stream";
 import type { StreamType } from "@discordjs/voice";
-export type MediaMode = "music";
+export type MediaMode = "music" | "screenshare";
 export type MediaSourceKind =
  | "url"
  | "local"
@@ -18,6 +18,7 @@ import {
  type RecordingSession,
 } from "./recorder/sessionRecording.js";
 import { createSpeakingHandler } from "./recorder/speakingHandler.js";
 import { hookScreenShareAudio } from "./screenShareAudio.js";
 const logger = createChildLogger("recorder");
@@ -146,6 +147,13 @@ export async function startRecording(
  receiver.speaking.on("start", speakingHandler);
  // ── Screen-share audio capture ──────────────────────────────────────
  // Discord GoLive sends screen-share audio on a SEPARATE SSRC from the
  // user's microphone. `receiver.speaking` only fires for voice (mic) SSRCs,
  // so screen-share audio is silently dropped unless we hook the UDP receiver
  // to discover and register those SSRCs.
  hookScreenShareAudio(receiver, speakingHandler);
  // Handle unexpected disconnection
  connection.on(VoiceConnectionStatus.Disconnected, async () => {
    if (config.VERBOSE) {
@@ -0,0 +1,222 @@
 import type { VoiceReceiver, VoiceUserData } from "@discordjs/voice";
 import { createChildLogger } from "@/shared/logger/index";
 const logger = createChildLogger("screen-share-audio");
 /**
 * Hooks screen-share audio capture into the voice receiver.
 *
 * ## Background
 *
 * Discord GoLive (screen share) sends audio on **separate SSRCs** from the
 * user's microphone.  In `@discordjs/voice` v0.19, `VoiceReceiver.onUdpMessage`
 * does:
 *
 * ```js
 * const userData = this.ssrcMap.get(ssrc);
 * if (!userData) return;   // ← DROPS screen-share audio SSRC
 * ```
 *
 * `ssrcMap` is only populated from `VOICE_STATE_UPDATE` / `VOICE_SERVER_UPDATE`
 * WebSocket packets, which carry the **voice audioSSRC** only.  When a user
 * starts an audio+video screen-share, Discord sends additional RTP packets on
 * new SSRCs that are *never* registered in `ssrcMap` → they are silently
 * discarded → `receiver.speaking` never fires → screen-share audio is missing.
 *
 * ## Fix
 *
 * 1. Wrap `onUdpMessage` to inspect every incoming RTP packet's SSRC.
 * 2. If the SSRC isn't in `ssrcMap`, check whether it looks like a screen-share
 *    audio stream (OPRUS payload type 120, RTP version 2).
 * 3. Clone the owning user's VoiceUserData into `ssrcMap` under the new SSRC
 *    so the *original* (un-patched) `onUdpMessage` picks it up, decrypts it,
 *    and forwards the Opus packet to the existing subscription stream.
 * 4. Emit a synthetic `"start"` speaking event so the existing
 *    `speakingHandler` sets up the full pipeline (decoder, packet filter,
 *    segment manager, event handlers) for that userId if not already.
 */
 export function hookScreenShareAudio(
  receiver: VoiceReceiver,
  speakingHandler: (userId: string) => Promise<void>,
 ): void {
  // Cache the original (un-bound) method so we can delegate to it.
  const original = receiver.onUdpMessage;
  receiver.onUdpMessage = (msg: Buffer) => {
    // ── 1. Detect screen-share SSRCs BEFORE the original discards them ──
    if (isLikelyScreenShareAudio(msg, receiver)) {
      const ssrc = msg.readUInt32BE(8);
      const userData = getSsrcMapEntry(receiver, ssrc);
      if (!userData) {
        // SSRC not registered — try to infer owner and register it
        const owner = inferScreenShareOwner(ssrc, receiver);
        if (owner) {
          registerScreenShareSsrc(receiver, ssrc, owner);
          logger.info(
            { userId: owner.userId, ssrc, kind: "screenshare-audio" },
            "Registered screen-share audio SSRC in ssrcMap",
          );
          // Trigger the speaking handler to ensure pipeline is ready
          void speakingHandler(owner.userId).catch((err) =>
            logger.error(
              { userId: owner.userId, error: err.message },
              "Speaking handler for screen-share failed",
            ),
          );
        } else {
          logger.warn(
            { ssrc },
            "Screen-share audio SSRC found but owner unknown",
          );
        }
      }
    }
    // ── 2. Delegate to the original handler ──
    // It will now find the SSRC (we registered it above) and forward the
    // decrypted Opus packet to the subscription stream.
    original.call(receiver, msg);
  };
  // ── 3. Listen for dynamic ssrcMap updates ──────────────────────────────
  receiver.ssrcMap.on("create", (data: VoiceUserData) => {
    if (data.videoSSRC !== undefined) {
      logger.info(
        { userId: data.userId, videoSSRC: data.videoSSRC },
        "Screen-share video started",
      );
      void speakingHandler(data.userId).catch((err) =>
        logger.error(
          { userId: data.userId, error: err.message },
          "Handler for screen-share start failed",
        ),
      );
    }
  });
  receiver.ssrcMap.on(
    "update",
    (_old: VoiceUserData | undefined, neu: VoiceUserData) => {
      if (_old?.videoSSRC !== neu.videoSSRC && neu.videoSSRC !== undefined) {
        logger.info(
          { userId: neu.userId, videoSSRC: neu.videoSSRC },
          "Screen-share video SSRC appeared",
        );
        void speakingHandler(neu.userId).catch((err) =>
          logger.error(
            { userId: neu.userId, error: err.message },
            "Handler for screen-share update failed",
          ),
        );
      }
    },
  );
 }
 // ─── Helpers ───────────────────────────────────────────────────────────────
 /**
 * Check if a UDP packet looks like a screen-share audio RTP packet.
 *
 * Voice packets have: RTP version 2 (top 2 bits of byte 0), and payload type 120 (OPRUS).
 * We also require that the SSRC is NOT already in ssrcMap (that's handled
 * by the original onUdpMessage).
 */
 function isLikelyScreenShareAudio(
  msg: Buffer,
  receiver: VoiceReceiver,
 ): boolean {
  if (msg.length <= 8) return false;
  const ssrc = msg.readUInt32BE(8);
  // Already registered as a known voice SSRC?
  if (getSsrcMapEntry(receiver, ssrc)) return false;
  const rtpVersion = msg[0] >> 6;
  const payloadType = msg[1] & 127;
  // OPRUS payload type is 120 in Discord voice
  return rtpVersion === 2 && payloadType === 120;
 }
 /**
 * Safely read an entry from the SSRCMap via the public `get` API.
 */
 function getSsrcMapEntry(
  receiver: VoiceReceiver,
  ssrc: number,
 ): VoiceUserData | undefined {
  try {
    return receiver.ssrcMap.get(ssrc);
  } catch {
    return undefined;
  }
 }
 /**
 * Infer which user owns a screen-share audio SSRC by proximity to their
 * known voice audioSSRC (Discord allocates SSRCs in small increments).
 */
 function inferScreenShareOwner(
  ssrc: number,
  receiver: VoiceReceiver,
 ): { userId: string } | null {
  try {
    // Iterate known SSRCs via internal _map (the public API only gets by ssrc)
    const map = getSsrcInternalMap(receiver.ssrcMap);
    if (!map) return null;
    for (const [, data] of map.entries()) {
      if (data.audioSSRC && Math.abs(data.audioSSRC - ssrc) < 200_000) {
        return { userId: data.userId };
      }
    }
    return null;
  } catch {
    return null;
  }
 }
 /**
 * Register a new SSRC in the internal ssrcMap so the original onUdpMessage
 * picks it up.  We clone the user's existing VoiceUserData (so decryption
 * keys, userId mapping, etc. all work) under the new SSRC key.
 */
 function registerScreenShareSsrc(
  receiver: VoiceReceiver,
  ssrc: number,
  owner: { userId: string },
 ): void {
  const map = getSsrcInternalMap(receiver.ssrcMap);
  if (!map) return;
  // Find the owner's existing VoiceUserData and clone it under the new SSRC
  for (const [, data] of map.entries()) {
    if (data.userId === owner.userId) {
      map.set(ssrc, { ...data });
      return;
    }
  }
  // Fallback: register with minimal data (userId only)
  map.set(ssrc, {
    userId: owner.userId,
    audioSSRC: ssrc,
  });
 }
 /**
 * Access the private `_map` inside an SSRCMap instance.
 *
 * SSRCMap in @discordjs/voice ≤ v0.19 stores its entries in a private
 * `#map` (JS private field) or `_map` depending on the build target.
 * We access it defensively for read + write so we can register new SSRCs.
 */
 function getSsrcInternalMap(
  ssrcMap: VoiceReceiver["ssrcMap"],
 ): Map<number, VoiceUserData> | undefined {
  const asAny = ssrcMap as unknown as Record<string, unknown>;
  // v0.19 ESM build uses _map
  const m1 = asAny._map;
  if (m1 instanceof Map) return m1 as Map<number, VoiceUserData>;
  return undefined;
 }
@@ -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} />;
 }
Author	SHA1	Message	Date
mytheclipsebotreview	5658726ea5	fix(frontend): sort messages by created_at to fix WS race condition Two independent WS handlers (useMessagesWsSync for message_created/ updated/analyzed, and useMessagesStream for message_snapshot) both prepend live messages to the SWR list without enforcing order. When frames arrive out-of-order (common with batched WS delivery), the message feed gets scrambled. Fix: add sortMessages() helper that sorts newest-first by created_at (the list's stored order before .reverse() for display) and apply it in every patchLists/mutate updater: message_created, message_updated, message_analyzed, message_snapshot, and useLoadMore page appends. Function declaration is hoisted so useLoadMore (defined above the helper) can use it.	2026-08-20 18:16:43 +07:00
mytheclipsebotreview	f5d5690401	fix: double-.js extension in @/ alias resolution (fix-imports.mjs) The fix-imports.mjs script blindly appended '.js' to every @/ alias import, even when the source specifier already carried a .js extension (e.g. '@/shared/config/index.js'). This produced 'index.js.js' in the emitted dist/, causing ERR_MODULE_NOT_FOUND at startup. This was latent: only triggered once digestScheduler.ts (which uses @/shared/config/index.js with explicit extension) was built. The user-reputation removal (`2a8f6d9`) was also blocked by this bug — stale binary kept crashing with 'user_reputations' query errors because it was never redeployed. Fix: only append .js when the @/ specifier has no existing extension. Applied to both gateway and backend scripts.	2026-08-20 18:08:30 +07:00
asepharyana	0aa893ab7d	feat: add Materi section + AI agent RAG to GMW business flow Backend: - New materi module: schema (materi_documents table), repository, service - ragClient: semantic + keyword search over materi docs, plus Discord archive via Qdrant, then LLM answer generation (RAG) - Wire materiRouter into appRouter (list/detail/create/update/delete/chat) Frontend: - New types (MateriDocument, CreateMateriInput, RAG chat shapes) - API client (SSR HTTP RPCLink + browser WS RPCLink) - Routes: /materi list, /materi/[id] detail, /materi/new form, /materi/chat RAG chat UI - Sidebar nav item 'Materi' Migration: scripts/add-materi-documents.sql (CREATE TABLE IF NOT EXISTS)	2026-08-20 15:57:04 +07:00
asepharyana	6f20b0f146	docs: clarify termGlossary uses Wikipedia (not SearXNG) for definition lookups SearXNG was already replaced by Wikipedia REST/Action APIs (wikipediaClient.ts). Update comments to reflect the current implementation: term glossary now resolves definitions via Wikipedia → Redis → Postgres cache chain, with no SearXNG dependency.	2026-08-20 15:32:20 +07:00
asepharyana	80248d4b7a	feat: add 'screenshare' to MediaMode union for screen-share audio recording Prepares the media type system to distinguish screenshare audio SSRCs from mic voice SSRCs once the hookScreenShareAudio capture logic is wired in.	2026-08-20 15:29:22 +07:00
asepharyana	20e991062c	fix: screen-share audio capture — hook VoiceReceiver.onUdpMessage to discover unregistered SSRCs Discord GoLive sends screen-share audio on a separate SSRC from the user's microphone. In @discordjs/voice v0.19, VoiceReceiver.onUdpMessage silently drops packets for SSRCs not in ssrcMap (which is only populated from VOICE_STATE_UPDATE/VOICE_SERVER_UPDATE). This caused screen-share audio to never trigger receiver.speaking and never reach the speakingHandler. Fix: hookScreenShareAudio() wraps onUdpMessage to: 1. Detect incoming RTP packets with unknown SSRCs (OPRUS payload type 120) 2. Infer the owning userId by proximity to known audioSSRC 3. Clone the user's VoiceUserData into ssrcMap under the new SSRC 4. Let the original handler decrypt and forward to the subscription stream 5. Listen on ssrcMap 'create'/'update' events for video SSRC changes Also removes the broken initial approach (polling ssrcMap which never contains screen-share SSRCs).	2026-08-20 15:28:55 +07:00
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