databases.integration.test.ts

databases.integration.test.tstests/integration/databases.integration.test.tsraw ← integration16,703 B← Back to the app
1/**
2 * AI Software Factory — Integration: Databases (Phases 5-6)
3 *
4 * Record CRUD, the nested filter tree (AND/OR/NOT + property ops), sort,
5 * group, view defaults, and a 10k-record query performance floor (soft —
6 * the hard budget is enforced by the k6 suite in tests/performance).
7 */
8import { afterAll, beforeAll, describe, expect, it } from "vitest";
9import { createTestApp, type TestAppHandle } from "./helpers/api";
10import { authHeaders, createWorkspace, createUser, seedRecords } from "./helpers/seed";
11 
12let app: TestAppHandle;
13 
14beforeAll(async () => {
15  app = await createTestApp();
16}, 180_000);
17afterAll(async () => {
18  await app?.close();
19});
20 
21const json = async (res: Response): Promise<Record<string, unknown>> =>
22  (await res.json().catch(() => ({}))) as Record<string, unknown>;
23 
24async function setupDb(
25  extraProperties: { name: string; type: string; config?: unknown }[] = [],
26): Promise<{ headers: Record<string, string>; workspaceId: string; databaseId: string }> {
27  const user = await createUser(app.request, { name: "DB Owner" });
28  const { workspaceId } = await createWorkspace(
29    app.request,
30    authHeaders(user.accessToken!),
31    "DB WS",
32  );
33  // Databases are PAGES — there is no `POST /workspaces/:id/databases`
34  // endpoint. The canonical setup (every other integration suite) is
35  // `POST /pages { type: "database" }` → `{ id, databaseId }`, then add
36  // custom properties via `POST /databases/:id/properties`.
37  const res = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
38    headers: authHeaders(user.accessToken!),
39    body: { title: "Test DB", type: "database" },
40  });
41  expect([200, 201]).toContain(res.status);
42  const { databaseId } = (await json(res)) as { databaseId: string };
43  for (const p of extraProperties) {
44    const propRes = await app.request("POST", `/api/v1/databases/${databaseId}/properties`, {
45      headers: authHeaders(user.accessToken!),
46      body: { name: p.name, type: p.type, config: p.config ?? {} },
47    });
48    if (propRes.status === 409) {
49      // A fresh database seeds `Title` + `Status` (To Do/In Progress/Done);
50      // a duplicate name 409s. Reuse the seeded property — set its config to
51      // the options this test's records/filters rely on (Status → todo/done).
52      const prop = await app.prisma.databaseProperty.findUniqueOrThrow({
53        where: { databaseId_name: { databaseId, name: p.name } },
54      });
55      const patchRes = await app.request(
56        "PATCH",
57        `/api/v1/databases/${databaseId}/properties/${prop.id}`,
58        { headers: authHeaders(user.accessToken!), body: { config: p.config ?? {} } },
59      );
60      expect([200]).toContain(patchRes.status);
61    } else {
62      expect([200, 201]).toContain(propRes.status);
63    }
64  }
65  return {
66    headers: authHeaders(user.accessToken!),
67    workspaceId,
68    databaseId,
69  };
70}
71 
72/**
73 * POST /databases/:id/records — `properties` is propertyId-keyed and the
74 * title goes through the separate `title` field (the API rejects name-keyed
75 * writes: "Unknown property"). Call sites stay readable by passing
76 * name-keyed values; this helper resolves name → propertyId (title name →
77 * the `title` field).
78 */
79async function addRecord(
80  headers: Record<string, string>,
81  databaseId: string,
82  byName: Record<string, unknown>,
83): Promise<string> {
84  const props = await app.prisma.databaseProperty.findMany({
85    where: { databaseId, deletedAt: null },
86    select: { id: true, name: true, type: true, isTitle: true },
87  });
88  const byId = new Map(props.map((p) => [p.name, p]));
89  const properties: Record<string, unknown> = {};
90  let title: string | undefined;
91  for (const [name, value] of Object.entries(byName)) {
92    const prop = byId.get(name);
93    if (!prop) throw new Error(`addRecord: unknown property "${name}"`);
94    if (prop.isTitle || prop.type === "TITLE") title = String(value);
95    else properties[prop.id] = value;
96  }
97  const res = await app.request("POST", `/api/v1/databases/${databaseId}/records`, {
98    headers,
99    body: { ...(title !== undefined ? { title } : {}), properties },
100  });
101  expect([200, 201]).toContain(res.status);
102  return ((await json(res)).id as string);
103}
104 
105/**
106 * The query DSL endpoint (docs §6): `POST /databases/:id/query` →
107 * `{ data: { records, totalCount, nextCursor } }` (records branch) or
108 * `{ data: { groups, totalCount, nextCursor } }` (group branch). `filter`
109 * is a native tree — branches `{ op: "and"|"or", conditions: [] }` /
110 * `{ op: "not", conditions: [one] }`, leaves `{ propertyId, op, value }`
111 * with ops `eq/neq/contains/gt/gte/lt/lte/...` (NOT the engine's
112 * `=`/`>`/`<` or the web's `children` key — those are rejected).
113 * Cursor-paginated (no offset); the read is a bounded fetch (take ≤ 5000).
114 */
115async function query(
116  headers: Record<string, string>,
117  databaseId: string,
118  body: {
119    filter?: unknown;
120    sort?: { propertyId: string; direction?: "asc" | "desc" }[];
121    group?: { propertyId: string; direction?: "asc" | "desc" }[];
122    pageSize?: number;
123    cursor?: string | null;
124    includeArchived?: boolean;
125  } = {},
126): Promise<{
127  records: { id: string; title?: string; properties: Record<string, unknown> }[];
128  groups: { key: { propertyId: string; value: unknown; label: string }; count: number }[];
129  total: number;
130  nextCursor: string | null;
131}> {
132  const res = await app.request("POST", `/api/v1/databases/${databaseId}/query`, {
133    headers,
134    body,
135  });
136  expect(res.status).toBe(200);
137  const data = (await json(res)).data as {
138    records?: { id: string; title?: string; properties: Record<string, unknown> }[];
139    groups?: { key: { propertyId: string; value: unknown; label: string }; count: number }[];
140    totalCount?: number;
141    nextCursor?: string | null;
142  };
143  return {
144    records: data.records ?? [],
145    groups: data.groups ?? [],
146    total: data.totalCount ?? 0,
147    nextCursor: data.nextCursor ?? null,
148  };
149}
150 
151describe("Databases integration", () => {
152  it("create: title property always present, default TABLE view exists", async () => {
153    const { databaseId } = await setupDb();
154    const db = await app.prisma.database.findUniqueOrThrow({
155      where: { id: databaseId },
156      include: { properties: true, views: true },
157    });
158    const titleProp = db.properties.find((p) => p.type === "TITLE");
159    expect(titleProp, "a TITLE property must always exist").toBeTruthy();
160    expect(db.views.some((v) => v.type === "TABLE")).toBe(true);
161  });
162 
163  it("records CRUD: create / read / update / delete", async () => {
164    const { headers, databaseId } = await setupDb([
165      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
166    ]);
167    const propId = (await app.prisma.databaseProperty.findUniqueOrThrow({
168      where: { databaseId_name: { databaseId, name: "Status" } },
169    })).id;
170 
171    const id = await addRecord(headers, databaseId, { Title: "Task 1", Status: "todo" });
172    const rec = await app.prisma.databaseRecord.findUniqueOrThrow({
173      where: { id },
174      include: { properties: true },
175    });
176    const statusVal = rec.properties.find((p) => p.propertyId === propId)?.value;
177    expect(statusVal).toBeTruthy();
178 
179    // Update (propertyId-keyed; the value is canonicalized name → option id
180    // the same way create is).
181    const up = await app.request("PATCH", `/api/v1/records/${id}`, {
182      headers,
183      body: { properties: { [propId]: "done" } },
184    });
185    expect([200]).toContain(up.status);
186    const after = await app.prisma.databaseRecord.findUniqueOrThrow({
187      where: { id },
188      include: { properties: true },
189    });
190    const doneVal = after.properties.find((p) => p.propertyId === propId)?.value;
191    expect(JSON.stringify(doneVal)).not.toBe(JSON.stringify(statusVal));
192    expect(after.version).toBeGreaterThan(rec.version);
193 
194    // Hard delete (the API implements DELETE /records/:id as a hard delete —
195    // the spec's `POST .../records/:rid/archive` soft-archive endpoint does
196    // not exist; recorded rule-29 conflict). The row, its 1:1 page, blocks,
197    // properties and links are all removed.
198    const del = await app.request("DELETE", `/api/v1/records/${id}`, {
199      headers,
200    });
201    expect([200, 204]).toContain(del.status);
202    expect(await app.prisma.databaseRecord.findUnique({ where: { id } })).toBeNull();
203    const pageGone = await app.prisma.page.findUnique({
204      where: { id: rec.pageId },
205      select: { id: true },
206    });
207    expect(pageGone).toBeNull();
208 
209    // Gone from queries.
210    const { total } = await query(headers, databaseId, {});
211    expect(total).toBe(0);
212  });
213 
214  it("record update: unknown property → 400", async () => {
215    const { headers, databaseId } = await setupDb();
216    const id = await addRecord(headers, databaseId, { Title: "X" });
217    const res = await app.request("PATCH", `/api/v1/records/${id}`, {
218      headers,
219      body: { properties: { NoSuchProperty: 1 } },
220    });
221    expect(res.status).toBe(400);
222  });
223 
224  it("filter tree: AND of (Title contains) OR (Status = done)", async () => {
225    const { headers, databaseId } = await setupDb([
226      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
227    ]);
228    const titleProp = await app.prisma.databaseProperty.findUniqueOrThrow({
229      where: { databaseId_name: { databaseId, name: "Title" } },
230    });
231    const statusProp = await app.prisma.databaseProperty.findUniqueOrThrow({
232      where: { databaseId_name: { databaseId, name: "Status" } },
233    });
234    await addRecord(headers, databaseId, { Title: "alpha apple", Status: "todo" });
235    await addRecord(headers, databaseId, { Title: "beta banana", Status: "done" });
236    await addRecord(headers, databaseId, { Title: "gamma cherry", Status: "todo" });
237 
238    // (Title contains "apple") OR (Status = done)
239    const filter = {
240      op: "or",
241      conditions: [
242        { propertyId: titleProp.id, op: "contains", value: "apple" },
243        { propertyId: statusProp.id, op: "eq", value: "done" },
244      ],
245    };
246    const { records, total } = await query(headers, databaseId, { filter });
247    expect(total).toBe(2);
248    const ids = new Set(records.map((r) => r.id));
249    expect(ids.size).toBe(2);
250  });
251 
252  it("filter tree: NOT + nested AND", async () => {
253    const { headers, databaseId } = await setupDb([
254      { name: "N", type: "NUMBER" },
255    ]);
256    const nProp = await app.prisma.databaseProperty.findUniqueOrThrow({
257      where: { databaseId_name: { databaseId, name: "N" } },
258    });
259    await addRecord(headers, databaseId, { Title: "a", N: 1 });
260    await addRecord(headers, databaseId, { Title: "b", N: 5 });
261    await addRecord(headers, databaseId, { Title: "c", N: 9 });
262 
263    // NOT (N eq 5) → {1, 9}
264    const filter = { op: "not", conditions: [{ propertyId: nProp.id, op: "eq", value: 5 }] };
265    const { total } = await query(headers, databaseId, { filter });
266    expect(total).toBe(2);
267 
268    // AND (N gt 0) (N lt 10) → all 3
269    const andFilter = {
270      op: "and",
271      conditions: [
272        { propertyId: nProp.id, op: "gt", value: 0 },
273        { propertyId: nProp.id, op: "lt", value: 10 },
274      ],
275    };
276    const { total: t2 } = await query(headers, databaseId, { filter: andFilter });
277    expect(t2).toBe(3);
278  });
279 
280  it("sort: title asc then desc; multi-key sort stable", async () => {
281    const { headers, databaseId } = await setupDb([
282      { name: "N", type: "NUMBER" },
283    ]);
284    const titleProp = await app.prisma.databaseProperty.findUniqueOrThrow({
285      where: { databaseId_name: { databaseId, name: "Title" } },
286    });
287    const nProp = await app.prisma.databaseProperty.findUniqueOrThrow({
288      where: { databaseId_name: { databaseId, name: "N" } },
289    });
290    for (const [t, n] of [
291      ["zeta", 1],
292      ["alpha", 2],
293      ["mid", 1],
294    ] as const) {
295      await addRecord(headers, databaseId, { Title: t, N: n });
296    }
297 
298    const asc = await query(headers, databaseId, {
299      sort: [{ propertyId: titleProp.id, direction: "asc" }],
300    });
301    expect(asc.records.length).toBe(3);
302 
303    const desc = await query(headers, databaseId, {
304      sort: [{ propertyId: titleProp.id, direction: "desc" }],
305    });
306    expect(desc.records.length).toBe(3);
307    expect(
308      JSON.stringify(desc.records.map((r) => r.id)),
309    ).not.toBe(JSON.stringify(asc.records.map((r) => r.id)));
310 
311    // Multi-key: N asc, then Title asc.
312    const multi = await query(headers, databaseId, {
313      sort: [
314        { propertyId: nProp.id, direction: "asc" },
315        { propertyId: titleProp.id, direction: "asc" },
316      ],
317    });
318    expect(multi.records.length).toBe(3);
319  });
320 
321  it("group: group by SELECT property returns groups with counts", async () => {
322    const { headers, databaseId } = await setupDb([
323      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
324    ]);
325    const statusProp = await app.prisma.databaseProperty.findUniqueOrThrow({
326      where: { databaseId_name: { databaseId, name: "Status" } },
327    });
328    await addRecord(headers, databaseId, { Title: "a", Status: "todo" });
329    await addRecord(headers, databaseId, { Title: "b", Status: "todo" });
330    await addRecord(headers, databaseId, { Title: "c", Status: "done" });
331 
332    const { groups, total } = await query(headers, databaseId, {
333      group: [{ propertyId: statusProp.id, direction: "asc" }],
334    });
335    // Two groups (todo: 2, done: 1); totalCount is the row count, not the
336    // group count.
337    expect(total).toBe(3);
338    const counts = new Map(groups.map((g) => [g.key.label, g.count]));
339    expect(counts.get("todo")).toBe(2);
340    expect(counts.get("done")).toBe(1);
341  });
342 
343  it("pagination: cursor + pageSize slice the result set", async () => {
344    const { headers, databaseId } = await setupDb();
345    for (let i = 0; i < 10; i++) {
346      await addRecord(headers, databaseId, { Title: `row-${i}` });
347    }
348    const page1 = await query(headers, databaseId, { pageSize: 4 });
349    expect(page1.nextCursor).not.toBeNull();
350    const page2 = await query(headers, databaseId, { pageSize: 4, cursor: page1.nextCursor });
351    expect(page1.total).toBe(10);
352    expect(page1.records.length).toBe(4);
353    expect(page2.records.length).toBe(4);
354    const ids1 = new Set(page1.records.map((r) => r.id));
355    const ids2 = new Set(page2.records.map((r) => r.id));
356    for (const id of ids2) expect(ids1.has(id)).toBe(false); // no overlap
357  });
358 
359  it("10k records: seeded set queries back within the documented bound (soft perf floor)", async () => {
360    const { headers, workspaceId, databaseId } = await setupDb();
361    const seeded = await seedRecords(app.prisma, databaseId, workspaceId, 10_000);
362    expect(seeded).toBe(10_000);
363 
364    // `POST /databases/:id/query` is a BOUNDED read (take ≤ 5000, per the
365    // TASK-0135 comment): over a 10k set the fetch caps at 5000, so `totalCount`
366    // reports the cap (5000), not the full 10_000. This is the documented
367    // behavior the hard budget enforces in tests/performance — pin it.
368    const started = Date.now();
369    const res = await query(headers, databaseId, { pageSize: 50 });
370    const ms = Date.now() - started;
371    console.log(`[perf] 10k-record page query = ${ms}ms (soft floor 2000ms)`);
372    expect(res.total).toBe(5_000);
373    expect(res.records.length).toBe(50);
374    expect(res.nextCursor).not.toBeNull();
375    expect(ms, `10k page query took ${ms}ms`).toBeLessThan(2_000);
376  }, 120_000);
377 
378  it("authorization: outsider cannot read or write another workspace's database", async () => {
379    const owner = await createUser(app.request, { name: "DBOwner" });
380    const { workspaceId } = await createWorkspace(
381      app.request,
382      authHeaders(owner.accessToken!),
383      "DBX WS",
384    );
385    const dbRes = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
386      headers: authHeaders(owner.accessToken!),
387      body: { title: "X DB", type: "database" },
388    });
389    expect([200, 201]).toContain(dbRes.status);
390    const { databaseId } = (await json(dbRes)) as { databaseId: string };
391 
392    const outsider = await createUser(app.request, { name: "DBX" });
393    const read = await app.request("GET", `/api/v1/databases/${databaseId}/records`, {
394      headers: authHeaders(outsider.accessToken!),
395    });
396    expect([403, 404]).toContain(read.status);
397    // Valid-shaped body (title field) so the 403/404 is the authorization
398    // decision, not a 400 validation error masking it.
399    const write = await app.request("POST", `/api/v1/databases/${databaseId}/records`, {
400      headers: authHeaders(outsider.accessToken!),
401      body: { title: "intrusion" },
402    });
403    expect([403, 404]).toContain(write.status);
404  });
405});
406