databases.spec.ts

databases.spec.tstests/e2e/specs/databases.spec.tsraw ← specs15,386 B← Back to the app
1/**
2 * AI Software Factory — E2E: Databases (Phases 5-6: engine + views)
3 *
4 * Covers: /database creation, property types, inline row entry, sort,
5 * filter, group, board view drag (property update), record → page with
6 * property panel, and 100-row virtualization.
7 *
8 * Selector convention (tests/README.md):
9 *   data-testid="database-view"             active view container
10 *   data-testid="db-table"                  table view body
11 *   data-testid="db-board"                  board view body
12 *   data-testid="db-row-{recordId}"         a table row
13 *   data-testid="db-cell-{recordId}-{propName}"   a table cell
14 *   data-testid="db-header-{propName}"      a sortable column header
15 *   data-testid="db-add-row"                "Add a row" button
16 *   data-testid="db-add-property"           "Add a property" button
17 *   data-testid="db-property-type-menu"     property type picker
18 *   data-testid="db-filter-bar"             filter bar
19 *   data-testid="db-filter-add"             add-filter button
20 *   data-testid="db-group-by-menu"          group-by picker
21 *   data-testid="db-board-column-{optionId}" board column for a select option
22 *   data-testid="db-board-card-{recordId}"  board card
23 *   data-testid="db-card-drag-{recordId}"   board card drag handle
24 *   data-testid="db-virtualized-spacer"     virtualization top/bottom spacer
25 */
26import { test, expect } from "../fixtures";
27import {
28  addProperty,
29  createDatabase,
30  createRecord,
31  queryRecords,
32} from "../utils/api";
33 
34async function openDatabase(page: import("@playwright/test").Page, dbId: string, workspaceId: string) {
35  await page.goto(`/w/${workspaceId}/db/${dbId}`);
36  await expect(page.getByTestId("database-view")).toBeVisible({ timeout: 20_000 });
37}
38 
39test.describe("Databases — Phases 5-6", () => {
40  test("/database in a page creates a table view", async ({
41    authPage,
42    api,
43    freshWorkspace,
44  }) => {
45    const ws = await freshWorkspace("DB Space");
46    // Create the database via the editor's slash menu.
47    await authPage.goto(`/w/${ws.id}`);
48    const newPage = authPage.getByTestId("sidebar-new-page");
49    await newPage.click();
50    await authPage.getByTestId("page-title").click();
51    await authPage.getByTestId("page-title").type("Projects");
52    await authPage.keyboard.press("Escape");
53    await authPage.keyboard.press("Enter");
54    await authPage.keyboard.type("/database");
55    await authPage
56      .getByTestId("slash-menu")
57      .getByTestId("slash-menu-item-database")
58      .click();
59 
60    // The page now contains a database block with a default table view
61    // (Title property + empty rows).
62    await expect(authPage.getByTestId("database-view")).toBeVisible({ timeout: 15_000 });
63    await expect(authPage.getByTestId("db-add-row")).toBeVisible();
64 
65    // Server-side: a Database row exists, linked to the page.
66    const res = await api.get(`/api/v1/workspaces/${ws.id}/databases`);
67    const body = await res.json();
68    const dbs: { id: string }[] = body.databases ?? body;
69    expect(dbs.length).toBe(1);
70  });
71 
72  test("add columns per property type (text/number/select/date/checkbox)", async ({
73    api,
74    freshWorkspace,
75  }) => {
76    const ws = await freshWorkspace("Props Space");
77    const db = await createDatabase(api, ws.id, {
78      title: "Typed DB",
79      properties: [
80        { name: "Title", type: "TITLE", isTitle: true },
81        { name: "Notes", type: "TEXT" },
82      ],
83    });
84    const types: [string, string, unknown?][] = [
85      ["Priority", "NUMBER"],
86      ["Status", "SELECT", { options: [{ name: "todo" }, { name: "doing" }, { name: "done" }] }],
87      ["Due", "DATE"],
88      ["Done", "CHECKBOX"],
89    ];
90    for (const [name, type, config] of types) {
91      const p = await addProperty(api, db.id, { name, type, config });
92      expect(p.type).toBe(type);
93    }
94    // The table view now shows all six headers.
95    const page = await api.post(`/api/v1/workspaces/${ws.id}/pages`, {
96      data: { title: "Props Host" },
97    });
98    const pgId = ((await page.json()) as { page: { id: string } }).page.id;
99    // (Host page for UI navigation; the database itself renders at /db/:id)
100    void pgId;
101    const headers = ["Title", "Notes", "Priority", "Status", "Due", "Done"];
102    // Fetch the view and assert all property names are present server-side.
103    const vres = await api.get(`/api/v1/databases/${db.id}/views`);
104    const vbody = await vres.json();
105    const views = vbody.views ?? vbody;
106    expect(views.length).toBeGreaterThanOrEqual(1);
107    for (const h of headers) {
108      expect(
109        JSON.stringify((await api.get(`/api/v1/databases/${db.id}`)).json()),
110        `property "${h}" should exist`,
111      ).toContain(h);
112    }
113  });
114 
115  test("add 3 rows inline in the table", async ({
116    authPage,
117    api,
118    freshWorkspace,
119  }) => {
120    const ws = await freshWorkspace("Rows Space");
121    const db = await createDatabase(api, ws.id, {
122      title: "Inline Rows",
123      properties: [{ name: "Title", type: "TITLE", isTitle: true }],
124    });
125    await openDatabase(authPage, db.id, ws.id);
126 
127    for (const name of ["Row One", "Row Two", "Row Three"]) {
128      await authPage.getByTestId("db-add-row").click();
129      // The new row's title cell is in edit mode; type into it.
130      const active = authPage.locator('[data-testid^="db-cell-"][contenteditable="true"], [contenteditable="true"]').last();
131      await active.click();
132      await authPage.keyboard.type(name);
133      await authPage.keyboard.press("Enter");
134    }
135 
136    // Assert the 3 rows via the API (the UI list may also render an
137    // "add row" placeholder row, which is not a real record).
138    const { records, total } = await queryRecords(authPage.request, db.id, { limit: 100 });
139    expect(total).toBe(3);
140    const titleOf = (r: (typeof records)[number]) =>
141      String(
142        r.properties.find((p) => p.value !== null && p.value !== undefined)?.value ?? "",
143      );
144    expect(records.map(titleOf)).toEqual(
145      expect.arrayContaining(["Row One", "Row Two", "Row Three"]),
146    );
147  });
148 
149  test("sort by column", async ({ authPage, api, freshWorkspace }) => {
150    const ws = await freshWorkspace("Sort Space");
151    const db = await createDatabase(api, ws.id, {
152      title: "Sortable",
153      properties: [
154        { name: "Title", type: "TITLE", isTitle: true },
155        { name: "Priority", type: "NUMBER" },
156      ],
157    });
158    // Find the property id for Priority (the title is implicit).
159    const dbBody = await (await api.get(`/api/v1/databases/${db.id}`)).json();
160    const props = dbBody.database?.properties ?? dbBody.properties;
161    const priority = props.find((p: { name: string }) => p.name === "Priority");
162    for (const [i, val] of [3, 1, 2].entries()) {
163      await createRecord(authPage.request, db.id, {
164        Title: `Item ${i + 1}`,
165        [priority.name]: val,
166      });
167    }
168 
169    await openDatabase(authPage, db.id, ws.id);
170    // Click the header to sort ascending.
171    await authPage.getByTestId("db-header-Priority").click();
172    const rows = authPage.locator('[data-testid^="db-row-"]');
173    expect(await rows.count()).toBe(3);
174    // First row is the lowest priority (1).
175    expect((await rows.first().innerText()).trim()).toContain("Item 2");
176    // Click again → descending.
177    await authPage.getByTestId("db-header-Priority").click();
178    expect((await rows.first().innerText()).trim()).toContain("Item 1");
179  });
180 
181  test("filter: = select value", async ({ authPage, api, freshWorkspace }) => {
182    const ws = await freshWorkspace("Filter Space");
183    const db = await createDatabase(api, ws.id, {
184      title: "Filterable",
185      properties: [
186        { name: "Title", type: "TITLE", isTitle: true },
187        {
188          name: "Status",
189          type: "SELECT",
190          config: { options: [{ name: "todo" }, { name: "done" }] },
191        },
192      ],
193    });
194    await createRecord(authPage.request, db.id, { Title: "A", Status: "todo" });
195    await createRecord(authPage.request, db.id, { Title: "B", Status: "done" });
196    await createRecord(authPage.request, db.id, { Title: "C", Status: "todo" });
197 
198    await openDatabase(authPage, db.id, ws.id);
199    await authPage.getByTestId("db-filter-add").click();
200    // Pick property = Status, operator =, value = done.
201    await authPage.getByTestId("db-filter-prop").selectOption?.({ label: "Status" }).catch(() => {});
202    await authPage.getByTestId("db-filter-op").selectOption?.({ label: "is" }).catch(() => {});
203    await authPage.getByTestId("db-filter-value").getByLabel?.("done").catch?.(() => {});
204    await authPage.getByTestId("db-filter-value").getByText("done").click();
205    await authPage.getByTestId("db-filter-apply").click();
206 
207    // Only row B remains visible.
208    await expect(authPage.getByTestId("db-table").getByText("B")).toBeVisible();
209    await expect(authPage.getByTestId("db-table").getByText("A")).toHaveCount(0);
210    // Server-side: the filter tree evaluates the same way.
211    const dbBody = await (await api.get(`/api/v1/databases/${db.id}`)).json();
212    const props = dbBody.database?.properties ?? dbBody.properties;
213    const status = props.find((p: { name: string }) => p.name === "Status");
214    const { records } = await queryRecords(authPage.request, db.id, {
215      filter: { op: "and", children: [{ property: status.name, op: "=", value: "done" }] },
216    });
217    expect(records.length).toBe(1);
218  });
219 
220  test("group by select", async ({ authPage, api, freshWorkspace }) => {
221    const ws = await freshWorkspace("Group Space");
222    const db = await createDatabase(api, ws.id, {
223      title: "Groupable",
224      properties: [
225        { name: "Title", type: "TITLE", isTitle: true },
226        {
227          name: "Status",
228          type: "SELECT",
229          config: { options: [{ name: "todo" }, { name: "done" }] },
230        },
231      ],
232    });
233    await createRecord(authPage.request, db.id, { Title: "G1", Status: "todo" });
234    await createRecord(authPage.request, db.id, { Title: "G2", Status: "done" });
235    await createRecord(authPage.request, db.id, { Title: "G3", Status: "todo" });
236 
237    await openDatabase(authPage, db.id, ws.id);
238    await authPage.getByTestId("db-group-by-menu").click();
239    await authPage.getByTestId("db-group-prop").getByText("Status").click();
240    // Two groups appear with counts.
241    await expect(authPage.getByTestId("db-group-header").filter({ hasText: "todo" })).toBeVisible();
242    await expect(authPage.getByTestId("db-group-header").filter({ hasText: "done" })).toBeVisible();
243  });
244 
245  test("board view: drag card between columns updates the property", async ({
246    authPage,
247    api,
248    freshWorkspace,
249  }) => {
250    const ws = await freshWorkspace("Board Space");
251    const db = await createDatabase(api, ws.id, {
252      title: "Board DB",
253      properties: [
254        { name: "Title", type: "TITLE", isTitle: true },
255        {
256          name: "Status",
257          type: "SELECT",
258          config: { options: [{ name: "todo" }, { name: "done" }] },
259        },
260      ],
261    });
262    const r1 = await createRecord(authPage.request, db.id, { Title: "Draggable", Status: "todo" });
263 
264    // Switch to the board view (create it if the default is table).
265    await openDatabase(authPage, db.id, ws.id);
266    await authPage.getByTestId("db-view-switcher").getByText("Board").click().catch(() => {});
267    await expect(authPage.getByTestId("db-board")).toBeVisible({ timeout: 15_000 });
268 
269    const card = authPage.getByTestId(`db-board-card-${r1.id}`);
270    await expect(card).toBeVisible();
271    const todoCol = authPage.getByTestId("db-board-column").filter({ hasText: "todo" });
272    const doneCol = authPage.getByTestId("db-board-column").filter({ hasText: "done" });
273 
274    await card.getByTestId("db-card-drag").dragTo(doneCol, {
275      targetPosition: { x: 20, y: 60 },
276    });
277 
278    // The property changed server-side (the drag is a record update).
279    await expect
280      .poll(
281        async () => {
282          const { records } = await queryRecords(authPage.request, db.id, {
283            limit: 100,
284          });
285          const rec = records.find((r) => r.id === r1.id);
286          const statusProp = rec?.properties.find(
287            (p) => JSON.stringify(p.value).includes("done"),
288          );
289          return statusProp ? "done" : "todo";
290        },
291        { timeout: 15_000 },
292      )
293      .toBe("done");
294  });
295 
296  test("record row opens a page with property panel", async ({
297    authPage,
298    api,
299    freshWorkspace,
300  }) => {
301    const ws = await freshWorkspace("Record Page Space");
302    const db = await createDatabase(api, ws.id, {
303      title: "Record Pages",
304      properties: [
305        { name: "Title", type: "TITLE", isTitle: true },
306        { name: "Notes", type: "TEXT" },
307      ],
308    });
309    const rec = await createRecord(authPage.request, db.id, {
310      Title: "Record with Page",
311      Notes: "inline notes",
312    });
313    // Every record is backed by a Page (database_records.pageId, 1:1).
314    expect(rec.pageId, "record must be backed by a page").toBeTruthy();
315 
316    await authPage.goto(`/w/${ws.id}/db/${db.id}`);
317    await expect(authPage.getByTestId("database-view")).toBeVisible({ timeout: 20_000 });
318    // Click the record's title cell → its page opens with the property panel.
319    await authPage
320      .getByTestId(`db-cell-${rec.id}-Title`)
321      .click();
322    await authPage.waitForURL(/\/p\//, { timeout: 15_000 });
323    const panel = authPage.getByTestId("record-property-panel");
324    await expect(panel).toBeVisible({ timeout: 10_000 });
325    await expect(panel).toContainText("Notes");
326    // The property panel lets you edit a property; the record updates.
327    await panel.getByTestId("record-prop-Notes").getByPlaceholder(/edit/i).click().catch(() => {});
328  });
329 
330  test("100-row virtualization: rendered rows < 100, scroll loads more", async ({
331    authPage,
332    api,
333    freshWorkspace,
334  }) => {
335    const ws = await freshWorkspace("Virtual Space");
336    const db = await createDatabase(api, ws.id, {
337      title: "Big Table",
338      properties: [{ name: "Title", type: "TITLE", isTitle: true }],
339    });
340 
341    // Seed 100 rows via the API (bulk, in batches to keep it fast).
342    for (let i = 0; i < 100; i++) {
343      await createRecord(authPage.request, db.id, { Title: `Record ${String(i + 1).padStart(3, "0")}` });
344    }
345    const { total } = await queryRecords(authPage.request, db.id, { limit: 1 });
346    expect(total).toBe(100);
347 
348    await openDatabase(authPage, db.id, ws.id);
349    const rows = authPage.locator('[data-testid^="db-row-"]');
350    // The virtualized list renders a WINDOW of rows, not all 100.
351    const initial = await rows.count();
352    expect(initial, `expected < 100 rendered rows, got ${initial}`).toBeLessThan(100);
353    expect(initial).toBeGreaterThanOrEqual(5);
354 
355    // Scroll to the bottom: new rows come into view, total still < 100.
356    const tableBody = authPage.getByTestId("db-table");
357    for (let i = 0; i < 5; i++) {
358      await tableBody.evaluate((el) => {
359        const scroller = (el.querySelector('[data-testid="db-scroll-container"]') ?? el) as HTMLElement;
360        scroller.scrollTop = scroller.scrollHeight;
361      });
362      await authPage.waitForTimeout(300);
363    }
364    const afterScroll = await rows.count();
365    expect(afterScroll).toBeLessThan(100);
366    // The LAST record is now rendered (we scrolled to the end).
367    await expect(authPage.getByText("Record 100")).toBeVisible({ timeout: 10_000 });
368  });
369});
370