# Keywords Reservoir DB Read Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Make `/keywords/reservoir` read its keywords from the Postgres `keywords` table (site-scoped) instead of the in-memory fixtures, so future imports (feature #2) appear there.

**Architecture:** A `server-only` query maps `keywords` rows → the UI `Keyword` type (flat `lang`/`country` → nested `locale`). A `"use server"` action wraps it so the client `Reservoir` component (which holds the active site in client state) can fetch on site change. The Research page keeps using the fixtures (unchanged).

**Tech Stack:** Next.js 16 (server actions), Drizzle/Postgres, bun test, biome.

**Spec:** `docs/superpowers/specs/2026-06-05-keywords-reservoir-db-read-design.md`

---

## File Structure

- `src/lib/keywords/queries.ts` — **create** — `getKeywordsForSite(siteId)` DB read + `toKeyword` mapping (server-only).
- `src/lib/keywords/queries.test.ts` — **create** — DB-gated test (isolation + mapping).
- `src/lib/keywords/actions.ts` — **create** — `fetchKeywordsForSiteAction(siteId)` (`"use server"`).
- `src/components/keywords/reservoir.tsx` — **modify** — fetch via the action (async, race-guarded).

Note: the fixture `getKeywordsForSite` in `src/lib/keywords/sites.ts` is **left untouched** (still used by `research/research-container.tsx` + `fixtures.test.ts`).

Verification:
- Typecheck: `npx tsc --noEmit`
- Lint: `./node_modules/.bin/biome check <files>`
- DB test: `DATABASE_URL=postgresql://humperdoo@localhost:5432/seo_insight_test DB_TESTS=1 bun test src/lib/keywords/queries.test.ts` (guarded by `assertTestDb`; NEVER against dev)
- Live: `d3k agent-browser …` (dev server on :3000)

---

## Task 1: `getKeywordsForSite` DB query + mapping + DB test

**Files:**
- Create: `src/lib/keywords/queries.ts`
- Test: `src/lib/keywords/queries.test.ts`

- [ ] **Step 1: Write `queries.ts`**

```ts
// src/lib/keywords/queries.ts — server-side reads for keywords (per-site).
import "server-only";
import { asc, eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { keywords } from "@/lib/db/domain-schema";
import type { Keyword } from "./types";

type KeywordRow = typeof keywords.$inferSelect;

/** Map a DB row to the UI Keyword (flat lang/country -> nested locale). */
function toKeyword(r: KeywordRow): Keyword {
  return {
    id: r.id,
    siteId: r.siteId,
    term: r.term,
    locale: { lang: r.lang, country: r.country },
    theme: r.theme,
    status: r.status,
    intent: r.intent,
    primarySource: r.primarySource,
    sources: r.sources,
    metrics: r.metrics,
    trend: r.trend,
    serpFeatures: r.serpFeatures ?? undefined,
  };
}

/** Keywords belonging to a site, sorted by term. */
export async function getKeywordsForSite(siteId: string): Promise<Keyword[]> {
  const rows = await db
    .select()
    .from(keywords)
    .where(eq(keywords.siteId, siteId))
    .orderBy(asc(keywords.term));
  return rows.map(toKeyword);
}
```

- [ ] **Step 2: Write the DB test**

```ts
// src/lib/keywords/queries.test.ts
// Integration test — gated on DB_TESTS=1, run against the *_test DB only.
import { afterAll, beforeEach, describe, expect, mock, test } from "bun:test";

// queries.ts imports "server-only" (throws outside Next) — stub it for bun test.
mock.module("server-only", () => ({}));

const RUN_DB = process.env.DB_TESTS === "1";

describe.skipIf(!RUN_DB)("getKeywordsForSite (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let client: typeof import("@/lib/db/client").client;
  let schema: typeof import("@/lib/db/domain-schema");
  let queries: typeof import("./queries");

  // Two sites seeded in seo_insight_test (from db:seed).
  const SITE_A = "lustre-lab";
  const SITE_B = "atelier-forge";

  beforeEach(async () => {
    ({ db, client } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    queries = await import("./queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await db.delete(schema.keywords);
  });

  afterAll(async () => {
    await client.end();
  });

  async function insertKeyword(id: string, siteId: string, term: string) {
    await db.insert(schema.keywords).values({
      id,
      siteId,
      term,
      lang: "fr",
      country: "FR",
      theme: "lab-diamonds",
      status: "idea",
      intent: ["informational"],
      primarySource: "mangools",
      sources: ["mangools"],
      metrics: { mangools: { volume: 100, kd: 10, cpc: 1.2, growthPct: 5, updatedAt: "2025-12-01" } },
      trend: [{ month: "2025-12", volume: 100 }],
    });
  }

  test("returns only the site's keywords, sorted by term", async () => {
    await insertKeyword("k-b", SITE_A, "bravo");
    await insertKeyword("k-a", SITE_A, "alpha");
    await insertKeyword("k-x", SITE_B, "foreign");
    const rows = await queries.getKeywordsForSite(SITE_A);
    expect(rows.map((k) => k.term)).toEqual(["alpha", "bravo"]);
  });

  test("maps lang/country to locale and preserves jsonb metrics/trend", async () => {
    await insertKeyword("k-a", SITE_A, "alpha");
    const [k] = await queries.getKeywordsForSite(SITE_A);
    expect(k.locale).toEqual({ lang: "fr", country: "FR" });
    expect(k.metrics.mangools?.volume).toBe(100);
    expect(k.trend).toEqual([{ month: "2025-12", volume: 100 }]);
  });
});
```

> The keyword enum literals (`theme: "lab-diamonds"`, `status: "idea"`,
> `primarySource: "mangools"`, `intent: ["informational"]`) are valid members of
> the unions in `src/lib/keywords/types.ts`. If any is rejected by the type, open
> that file and swap for a real member.

- [ ] **Step 3: Run the DB test (TEST db only)**

Run: `DATABASE_URL=postgresql://humperdoo@localhost:5432/seo_insight_test DB_TESTS=1 bun test src/lib/keywords/queries.test.ts`
Expected: 2 pass. (`assertTestDb` throws if you forget the URL override — that's the safety net.)

- [ ] **Step 4: Typecheck, lint, commit**

Run: `npx tsc --noEmit` → no errors. `./node_modules/.bin/biome check src/lib/keywords/queries.ts src/lib/keywords/queries.test.ts` → clean (write-fix import order if needed).
```bash
git add src/lib/keywords/queries.ts src/lib/keywords/queries.test.ts
git commit -m "feat(keywords): site-scoped getKeywordsForSite DB query + test"
```

---

## Task 2: `fetchKeywordsForSiteAction` server action

**Files:**
- Create: `src/lib/keywords/actions.ts`

- [ ] **Step 1: Write the action**

```ts
// src/lib/keywords/actions.ts — server actions for keywords.
"use server";
import { getKeywordsForSite } from "./queries";
import type { Keyword } from "./types";

/** Fetch a site's keywords from the DB (callable from client components). */
export async function fetchKeywordsForSiteAction(
  siteId: string,
): Promise<Keyword[]> {
  return getKeywordsForSite(siteId);
}
```
> `"use server"` modules may only export async functions; the `Keyword` import is
> a type-only import (erased), so this is valid. `getKeywordsForSite` comes from the
> `server-only` `./queries`.

- [ ] **Step 2: Typecheck, lint, commit**

Run: `npx tsc --noEmit` → no errors. `./node_modules/.bin/biome check src/lib/keywords/actions.ts` → clean.
```bash
git add src/lib/keywords/actions.ts
git commit -m "feat(keywords): fetchKeywordsForSiteAction over the DB query"
```

---

## Task 3: Wire `Reservoir` to the DB action (async, race-guarded)

**Files:**
- Modify: `src/components/keywords/reservoir.tsx`

- [ ] **Step 1: Swap the import**

In `src/components/keywords/reservoir.tsx`, replace:
```ts
import { getKeywordsForSite } from "@/lib/keywords/sites";
```
with:
```ts
import { fetchKeywordsForSiteAction } from "@/lib/keywords/actions";
```

- [ ] **Step 2: Make the load effect async + race-guarded**

Replace the existing effect:
```ts
  useEffect(() => {
    setLoading(true);
    setRowSelection({});
    const id = setTimeout(() => {
      setRows(getKeywordsForSite(activeSiteId));
      setLoading(false);
    }, 200);
    return () => clearTimeout(id);
  }, [activeSiteId]);
```
with:
```ts
  useEffect(() => {
    let cancelled = false;
    setLoading(true);
    setRowSelection({});
    fetchKeywordsForSiteAction(activeSiteId).then((data) => {
      if (cancelled) return;
      setRows(data);
      setLoading(false);
    });
    return () => {
      cancelled = true;
    };
  }, [activeSiteId]);
```
Leave everything else in the component unchanged.

- [ ] **Step 3: Typecheck & lint**

Run: `npx tsc --noEmit` → no errors. `./node_modules/.bin/biome check src/components/keywords/reservoir.tsx` → clean.

- [ ] **Step 4: Live verify (d3k)**

```bash
d3k agent-browser open http://localhost:3000/keywords/reservoir
# count rows for the active site:
d3k agent-browser eval "document.querySelectorAll('table tbody tr').length"
```
Expected: the table shows the active site's keywords from the DB (for a seeded site like Lustre Lab, the same ~rows as before the migration — the seed mirrors the fixtures). Switch the active site via the team switcher and confirm the rows change. A site with no keywords shows the empty state. `d3k errors` shows no new errors, and `tail /tmp/seo-dev.log` shows the `fetchKeywordsForSiteAction` ran.

- [ ] **Step 5: Commit**

```bash
git add src/components/keywords/reservoir.tsx
git commit -m "feat(keywords): reservoir reads keywords from the DB (per active site)"
```

---

## Final verification

- [ ] `npx tsc --noEmit` clean.
- [ ] `./node_modules/.bin/biome check src/lib/keywords src/components/keywords/reservoir.tsx` clean (ignore any PRE-EXISTING errors not in the changed files).
- [ ] `DATABASE_URL=…seo_insight_test DB_TESTS=1 bun test src/lib/keywords/queries.test.ts` → 2 pass.
- [ ] Live: reservoir shows DB keywords for the active site; switching sites changes the rows; Research page still works (unchanged, fixtures).
- [ ] `d3k errors` → no new app errors.