ai-database.spec.ts

ai-database.spec.tstests/e2e/specs/ai-database.spec.tsraw ← specs13,088 B← Back to the app
1/**
2 * AI Software Factory — E2E: Database AI (TASK-0124).
3 *
4 * Drives the REAL stack (Next web + Nest API + the deterministic mock
5 * provider's `mode:"database"` branch) against the acceptance criteria in
6 * docs/tasks/phase-12-ai.md. The mock's database mode is deterministic:
7 *   generate → a 4-property database (Name/State/Due/Done?) + 2 sample
8 *     records ("First item"/"Second item");
9 *   extract  → parses the pasted markdown table, maps columns by name,
10 *     unmatched columns → `ignoredColumns`;
11 *   formula  → `if(prop("Title") == "Done", 100, 0)` (number).
12 * so the E2E can assert the exact created shape.
13 *
14 * Covered (the spec's E2E AC — the three happy paths + viewer 403):
15 *   1. generate — database header AI → Generate → a NEW database appears
16 *      (via the API `GET /workspaces/:id/databases`), its sample records are
17 *      real pages, and the "Open" action lands on it (UI shows the record
18 *      rows).
19 *   2. extract — header AI → Extract from text → paste a 3-row markdown
20 *      table (one unmatched column) → the LIVE mapping preview shows the
21 *      matched + skipped columns → Apply → 3 records appear (per-type
22 *      values) and the unmatched column is skipped.
23 *   3. formula — header AI → Generate column with formula → a description →
24 *      the preview shows the validated expression + the evaluated sample
25 *      values → the FORMULA column is created.
26 *   4. viewer 403 — a GUEST (workspace VIEW) member: the UI HIDES the
27 *      generate/extract/formula menu items (only "Ask AI" remains), and the
28 *      server returns 403 FORBIDDEN for all three endpoints (the real test).
29 *
30 * Selector convention (tests/README.md): this task's own data-testids —
31 *   database-ask-ai, db-ai-menu-*, db-ai-generate(-prompt/-btn/-done),
32 *   db-ai-extract(-text/-map/-ignored/-apply/-done), db-formula
33 *   (-desc/-generate/-expression/-table/-done), db-ai-error-banner,
34 *   db-ai-loading, record-row-*.
35 */
36import type { APIRequestContext, Page } from "@playwright/test";
37import { expect, test } from "../fixtures";
38import {
39  addMemberViaSeam,
40  createWorkspace,
41  seamServiceToken,
42  write
43} from "../utils/api";
44 
45/** The extract markdown table (3 rows; "Notes" is unmatched). */
46const EXTRACT_TABLE = [
47  "| Name | Score | State | Notes |",
48  "| --- | --- | --- | --- |",
49  "| Alice | 42 | Open | first |",
50  "| Bob | 7 | Closed | second |",
51  "| Carol | 99 | Open | third |"
52].join("\n");
53 
54/** Create a database through the NORMAL page route (the spec's
55 *  `POST /workspaces/:id/databases` does not exist — a recorded conflict). */
56async function createDbViaPage(
57  api: APIRequestContext,
58  workspaceId: string,
59  title: string,
60): Promise<{ databaseId: string; pageId: string }> {
61  const body = await write<{ id: string; databaseId?: string }>(
62    api,
63    "POST",
64    `/api/v1/workspaces/${workspaceId}/pages`,
65    { title, type: "database" },
66    "createDbViaPage",
67  );
68  if (!body.databaseId) throw new Error(`createDbViaPage: no databaseId in ${JSON.stringify(body)}`);
69  return { databaseId: body.databaseId, pageId: body.id };
70}
71 
72/** The workspace's databases via the API (the sidebar's source of truth). */
73async function listDatabases(api: APIRequestContext, workspaceId: string): Promise<{ id: string; title: string }[]> {
74  const res = await api.get(`/api/v1/workspaces/${workspaceId}/databases`);
75  if (res.status() !== 200) throw new Error(`listDatabases ${res.status()}: ${await res.text()}`);
76  const body = (await res.json()) as
77    | { id: string; title: string }[]
78    | { data: { id: string; title: string }[] };
79  return Array.isArray(body) ? body : body.data;
80}
81 
82/** Create a property via the API (returns the BARE property — the e2e
83 *  `addProperty` helper wrongly expects a `{property}` wrapper). */
84async function addProp(
85  api: APIRequestContext,
86  databaseId: string,
87  property: { name: string; type: string; config?: unknown },
88): Promise<{ id: string; name: string; type: string }> {
89  return write(api, "POST", `/api/v1/databases/${databaseId}/properties`, property, "addProp");
90}
91 
92async function openAiMenu(page: Page) {
93  await page.getByTestId("database-ask-ai").click();
94}
95 
96test.describe("Database AI (TASK-0124)", () => {
97  test("generate — header AI → Generate → new database with page-backed sample records", async ({ authPage, api }) => {
98    const ws = await createWorkspace(api, `Gen ${Date.now()}`);
99    // A host database to mount the header (the AI dropdown lives on a DB page).
100    const host = await createDbViaPage(api, ws.id, "Host DB");
101    await authPage.goto(`/w/${ws.id}/databases/${host.databaseId}`);
102    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });
103 
104    const before = new Set((await listDatabases(api, ws.id)).map((d) => d.id));
105 
106    await openAiMenu(authPage);
107    await authPage.getByTestId("db-ai-menu-generate").click();
108    const dialog = authPage.getByTestId("db-ai-generate");
109    await expect(dialog).toBeVisible();
110    const prompt = "a bug tracker";
111    await authPage.getByTestId("db-ai-generate-prompt").fill(prompt);
112    await authPage.getByTestId("db-ai-generate-btn").click();
113 
114    // The success summary appears (the SSE stream finished + done frame).
115    await expect(authPage.getByTestId("db-ai-generate-done")).toBeVisible({ timeout: 30_000 });
116 
117    // The new database exists via the API (identify by id-diff — the mock's
118    // title derives from the rendered prompt, not the raw prompt).
119    const after = await listDatabases(api, ws.id);
120    const created = after.find((d) => !before.has(d.id));
121    expect(created, "a new database should exist").toBeTruthy();
122    expect(after.length).toBe(before.size + 1);
123 
124    // The 2 sample records are real pages (pageId set) with the mock titles.
125    const records = ((await (
126      await api.get(`/api/v1/databases/${created!.id}/records`)
127    ).json()) as { data: { title?: string; pageId: string | null }[] }).data;
128    expect(records.length).toBe(2);
129    expect(records.map((r) => r.title).sort()).toEqual(["First item", "Second item"]);
130    for (const r of records) expect(r.pageId).toBeTruthy();
131 
132    // The "Open" action lands on the new database (its record rows render).
133    await authPage.getByTestId("db-ai-generate-done").getByRole("button", { name: "Open" }).click();
134    await expect(authPage).toHaveURL(new RegExp(`/databases/${created!.id}`));
135    await expect(authPage.locator('[data-testid^="record-row-"]').first()).toBeVisible({ timeout: 20_000 });
136  });
137 
138  test("extract — paste markdown → live mapping preview → Apply → records appear, unmatched skipped", async ({ authPage, api }) => {
139    const ws = await createWorkspace(api, `Extract ${Date.now()}`);
140    const { databaseId } = await createDbViaPage(api, ws.id, "Extract DB");
141    // The mock maps columns BY NAME to existing properties.
142    const name = await addProp(api, databaseId, { name: "Name", type: "TEXT" });
143    const score = await addProp(api, databaseId, { name: "Score", type: "NUMBER" });
144    const state = await addProp(api, databaseId, {
145      name: "State",
146      type: "SELECT",
147      config: { options: [{ name: "Open" }, { name: "Closed" }] }
148    });
149 
150    await authPage.goto(`/w/${ws.id}/databases/${databaseId}`);
151    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });
152 
153    await openAiMenu(authPage);
154    await authPage.getByTestId("db-ai-menu-extract").click();
155    const dialog = authPage.getByTestId("db-ai-extract");
156    await expect(dialog).toBeVisible();
157    await authPage.getByTestId("db-ai-extract-text").fill(EXTRACT_TABLE);
158 
159    // Live mapping preview: Name/Score/State matched, Notes skipped.
160    const map = authPage.getByTestId("db-ai-extract-map");
161    await expect(map).toBeVisible({ timeout: 20_000 });
162    await expect(map).toContainText("→ Name");
163    await expect(map).toContainText("→ Score");
164    await expect(map).toContainText("→ State");
165    await expect(authPage.getByTestId("db-ai-extract-ignored")).toContainText("Notes");
166 
167    const before = ((await (await api.get(`/api/v1/databases/${databaseId}/records`)).json()) as { data: unknown[] }).data.length;
168 
169    await authPage.getByTestId("db-ai-extract-apply").click();
170    const done = authPage.getByTestId("db-ai-extract-done");
171    await expect(done).toBeVisible({ timeout: 30_000 });
172    await expect(done).toContainText("3 record");
173 
174    // 3 records created; per-type stored values; Notes dropped.
175    const after = ((await (
176      await api.get(`/api/v1/databases/${databaseId}/records`)
177    ).json()) as { data: { properties: Record<string, unknown> }[] }).data;
178    expect(after.length).toBe(before + 3);
179    const alice = after.find((r) => (r.properties?.[name.id] as { text?: string })?.text === "Alice")!;
180    expect(alice).toBeTruthy();
181    expect(alice.properties![score.id]).toEqual({ value: 42 });
182    // "Open" → the deterministic option id (the API-created option has no id).
183    expect(alice.properties![state.id]).toEqual({ optionId: "opt-open" });
184  });
185 
186  test("formula — description → validated expression + evaluated samples → FORMULA column", async ({ authPage, api }) => {
187    const ws = await createWorkspace(api, `Formula ${Date.now()}`);
188    const { databaseId } = await createDbViaPage(api, ws.id, "Formula DB");
189    // Distinctive record titles so the mock formula (prop("Title") == "Done")
190    // evaluates to 100 / 0 on the sample rows. The e2e `createRecord` helper
191    // doesn't set a title, so POST the record directly (CreateRecordDto.title).
192    for (const title of ["Done", "Open"]) {
193      await write(
194        api,
195        "POST",
196        `/api/v1/databases/${databaseId}/records`,
197        { title, properties: {}, links: {} },
198        "createTitledRecord",
199      );
200    }
201 
202    await authPage.goto(`/w/${ws.id}/databases/${databaseId}`);
203    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });
204 
205    await openAiMenu(authPage);
206    await authPage.getByTestId("db-ai-menu-formula").click();
207    const dialog = authPage.getByTestId("db-formula");
208    await expect(dialog).toBeVisible();
209    await authPage.getByTestId("db-formula-desc").fill("score a row 100 when it is done");
210    await authPage.getByTestId("db-formula-generate").click();
211 
212    // The validated expression + the evaluated sample table (100 / 0).
213    const done = authPage.getByTestId("db-formula-done");
214    await expect(done).toBeVisible({ timeout: 30_000 });
215    await expect(authPage.getByTestId("db-formula-expression")).toContainText('if(prop("Title") == "Done", 100, 0)');
216    const table = authPage.getByTestId("db-formula-table");
217    await expect(table).toBeVisible();
218    await expect(table).toContainText("100");
219    await expect(table).toContainText("0");
220 
221    // The FORMULA column was created (a property of type FORMULA).
222    const detail = (await (
223      await api.get(`/api/v1/databases/${databaseId}`)
224    ).json()) as { properties: { name: string; type: string }[] };
225    expect(detail.properties.some((p) => p.type === "FORMULA")).toBe(true);
226  });
227 
228  test("viewer 403 — GUEST: UI hides the write actions, server returns 403 for all three", async ({ api, secondUser }) => {
229    const ws = await createWorkspace(api, `Viewer ${Date.now()}`);
230    const { databaseId } = await createDbViaPage(api, ws.id, "Viewer DB");
231 
232    // A second, freshly-registered user (their own Bearer `api` + browser
233    // `page`) added to the workspace as a GUEST via the internal seam.
234    const { user, page, api: guestApi } = await secondUser();
235    await addMemberViaSeam(seamServiceToken(), ws.id, user.email, "GUEST");
236 
237    // Server: all three endpoints are 403 FORBIDDEN for the GUEST (the real
238    // test — server-side authorization, not the UI).
239    for (const [path, body] of [
240      [`/api/v1/workspaces/${ws.id}/ai/databases/generate`, { prompt: "a bug tracker" }],
241      [`/api/v1/workspaces/${ws.id}/ai/databases/extract`, { databaseId, text: EXTRACT_TABLE }],
242      [`/api/v1/workspaces/${ws.id}/ai/databases/formulas`, { databaseId, description: "x" }]
243    ] as const) {
244      const res = await guestApi.post(path, { data: body });
245      expect(res.status(), `${path} should be 403`).toBe(403);
246      const envelope = (await res.json()) as { error?: { code?: string } };
247      expect(envelope.error?.code).toBe("FORBIDDEN");
248    }
249 
250    // UI: the GUEST's AI dropdown hides the write actions (only "Ask AI"
251    // shows). The DB route's `readOnly` now reads the caller's role from
252    // `GET /workspaces/:id` (surfaced in TASK-0124).
253    await page.goto(`/w/${ws.id}/databases/${databaseId}`);
254    await expect(page.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });
255    await openAiMenu(page);
256    await expect(page.getByTestId("db-ai-menu-ask")).toBeVisible();
257    await expect(page.getByTestId("db-ai-menu-generate")).toHaveCount(0);
258    await expect(page.getByTestId("db-ai-menu-extract")).toHaveCount(0);
259    await expect(page.getByTestId("db-ai-menu-formula")).toHaveCount(0);
260  });
261});
262