csv-import.integration.test.ts

csv-import.integration.test.tstests/integration/csv-import.integration.test.tsraw ← integration17,574 B← Back to the app
1/**
2 * T-165 — CSV import (`POST /api/v1/databases/:id/import/csv`).
3 *
4 * Covers: the dry-run mapping contract (header → property, title column,
5 * unmapped read-only properties, sample rows, zero writes), the import
6 * (records + page titles + per-type values, audit row, `record.created`
7 * events), per-row error isolation (a bad cell fails that row only),
8 * explicit `mappings` overrides + their 400s, the 2 MB body cap (413),
9 * the 10k-row cap (400, not truncation), and authorization (VIEW member
10 * 403, non-member 403, unknown id 404 no-leak).
11 */
12import { afterAll, beforeAll, describe, expect, it } from "vitest";
13import { createTestApp, type TestAppHandle } from "./helpers/api";
14import {
15  addMemberViaSeam,
16  authHeaders,
17  countAuditEvents,
18  createWorkspace,
19  createUser
20} from "./helpers/seed";
21 
22let app: TestAppHandle;
23 
24beforeAll(async () => {
25  app = await createTestApp();
26}, 180_000);
27afterAll(async () => {
28  await app?.close();
29});
30 
31const json = async (res: Response): Promise<Record<string, unknown>> =>
32  (await res.json().catch(() => ({}))) as Record<string, unknown>;
33 
34/**
35 * A database with Title + Status (the default seed) plus `extraProperties`
36 * added through the public property endpoint. Databases are created THROUGH
37 * THE PAGES SURFACE (`POST /workspaces/:id/pages` with `type: "database"` —
38 * there is no `POST /databases` route in the API). Returns a name → id map.
39 */
40async function setupDb(
41  extraProperties: { name: string; type: string; config?: unknown }[] = [],
42): Promise<{
43  headers: Record<string, string>;
44  workspaceId: string;
45  databaseId: string;
46  props: Record<string, string>;
47}> {
48  const user = await createUser(app.request, { name: "CSV Owner" });
49  const headers = authHeaders(user.accessToken!);
50  const { workspaceId } = await createWorkspace(app.request, headers, "CSV WS");
51  const res = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
52    headers,
53    body: { title: "Imported", type: "database" },
54  });
55  expect([200, 201]).toContain(res.status);
56  const { databaseId } = (await json(res)) as { databaseId: string };
57  for (const p of extraProperties) {
58    const propRes = await app.request("POST", `/api/v1/databases/${databaseId}/properties`, {
59      headers,
60      body: { name: p.name, type: p.type, config: p.config ?? {} },
61    });
62    expect([200, 201]).toContain(propRes.status);
63  }
64  const rows = await app.prisma.databaseProperty.findMany({ where: { databaseId, deletedAt: null } });
65  const props: Record<string, string> = {};
66  for (const r of rows) props[r.name] = r.id;
67  return { headers, workspaceId, databaseId, props };
68}
69 
70async function importCsv(
71  databaseId: string,
72  body: Record<string, unknown>,
73  headers: Record<string, string>,
74): Promise<Response> {
75  return app.request("POST", `/api/v1/databases/${databaseId}/import/csv`, { headers, body });
76}
77 
78/** The test-only debug events endpoint (NODE_ENV=test + per-run token). */
79async function debugEvents(name: string): Promise<{ event: string; payload: Record<string, unknown> }[]> {
80  const r = await app.request("GET", `/api/v1/debug/events?name=${name}&limit=200`, {
81    headers: { "X-Debug-Token": app.debugToken ?? "" },
82  });
83  if (r.status !== 200) throw new Error(`debug events endpoint failed (${r.status}): ${await r.text()}`);
84  return ((await json(r)) as { items: { event: string; payload: Record<string, unknown> }[] }).items;
85}
86 
87/** The records of a database via the public list endpoint. */
88async function listRecords(
89  databaseId: string,
90  headers: Record<string, string>,
91): Promise<Record<string, unknown>[]> {
92  const r = await app.request("GET", `/api/v1/databases/${databaseId}/records`, { headers });
93  if (r.status !== 200) throw new Error(`list records failed (${r.status}): ${await r.text()}`);
94  return ((await json(r)) as { data: Record<string, unknown>[] }).data;
95}
96 
97describe("T-165 CSV import — happy path (dry run → import)", () => {
98  it("dry run maps headers to properties, reports sample rows, writes nothing; import lands records", async () => {
99    const { headers, databaseId, props } = await setupDb([
100      { name: "Note", type: "TEXT" },
101      { name: "Score", type: "NUMBER" },
102      { name: "Due", type: "DATE" },
103      { name: "Done", type: "CHECKBOX" }
104    ]);
105    const csv = [
106      "Name,Status,Note,Score,Due,Done",
107      "Alpha,To Do,first,1,2026-01-05,yes",
108      "Beta,In Progress,second,2.5,2026-02-10,false"
109    ].join("\n");
110 
111    // ---- dry run (the default) ----
112    const dry = await importCsv(databaseId, { csv, titleHeader: "Name" }, headers);
113    expect(dry.status).toBe(200);
114    const d = await json(dry);
115    expect(d.dryRun).toBe(true);
116    expect(d.databaseId).toBe(databaseId);
117    expect(d.rowCount).toBe(2);
118    const cols = (d.columns as { index: number; header: string; propertyId: string | null; reason: string }[]).sort(
119      (a, b) => a.index - b.index,
120    );
121    expect(cols).toEqual([
122      { index: 0, header: "Name", propertyId: props.Title, reason: "title" },
123      { index: 1, header: "Status", propertyId: props.Status, reason: "" },
124      { index: 2, header: "Note", propertyId: props.Note, reason: "" },
125      { index: 3, header: "Score", propertyId: props.Score, reason: "" },
126      { index: 4, header: "Due", propertyId: props.Due, reason: "" },
127      { index: 5, header: "Done", propertyId: props.Done, reason: "" }
128    ]);
129    // Everything non-title is mapped → nothing left unmapped.
130    expect(d.unmappedProperties).toEqual([]);
131    expect(d.sampleRows).toEqual([
132      { Name: "Alpha", Status: "To Do", Note: "first", Score: "1", Due: "2026-01-05", Done: "yes" },
133      { Name: "Beta", Status: "In Progress", Note: "second", Score: "2.5", Due: "2026-02-10", Done: "false" }
134    ]);
135    // The dry run wrote nothing.
136    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
137 
138    // ---- the real import ----
139    const imp = await importCsv(databaseId, { csv, titleHeader: "Name", dryRun: false }, headers);
140    expect(imp.status).toBe(201);
141    const i = await json(imp);
142    expect(i).toMatchObject({ dryRun: false, databaseId, imported: 2, failed: 0, rows: 2, errors: [] });
143 
144    // Records + page titles + per-type values.
145    const records = await listRecords(databaseId, headers);
146    expect(records.map((r) => r.title)).toEqual(["Alpha", "Beta"]);
147    const [a, b] = records as Record<string, unknown>[];
148    expect(a.properties[props.Status]).toEqual({ optionId: "opt-to-do" });
149    expect(a.properties[props.Note]).toEqual({ text: "first", marks: [] });
150    expect(a.properties[props.Score]).toEqual({ value: 1 });
151    expect(a.properties[props.Due]).toEqual({ start: "2026-01-05T00:00:00.000Z", end: null });
152    expect(a.properties[props.Done]).toEqual({ value: true });
153    expect(b.properties[props.Status]).toEqual({ optionId: "opt-in-progress" });
154    expect(b.properties[props.Score]).toEqual({ value: 2.5 });
155    expect(b.properties[props.Done]).toEqual({ value: false });
156    // Records are page-backed (the record-a-page invariant).
157    const stored = await app.prisma.databaseRecord.findMany({ where: { databaseId }, select: { pageId: true } });
158    for (const s of stored) expect(s.pageId).not.toBeNull();
159 
160    // Audit + stream events.
161    expect(
162      await app.prisma.auditLog.findFirst({
163        where: { action: "database.records_imported", resourceId: databaseId }
164      }),
165    ).not.toBeNull();
166    const events = (await debugEvents("record.created")).filter(
167      (e) => (e.payload as Record<string, unknown>).databaseId === databaseId,
168    );
169    expect(events.length).toBe(2);
170  });
171});
172 
173describe("T-165 CSV import — unmapped/read-only reporting", () => {
174  it("reports read-only, relation-id and unmatched columns with reasons in the dry run", async () => {
175    const { headers, databaseId, props } = await setupDb([
176      { name: "Created", type: "CREATED_TIME" },
177      { name: "Owner", type: "PERSON" }
178    ]);
179    const csv = ["Title,UnknownCol,Note", "A,x,hello", "B,y,world"].join("\n");
180    const dry = await importCsv(databaseId, { csv }, headers);
181    expect(dry.status).toBe(200);
182    const d = await json(dry);
183    const cols = (d.columns as { index: number; header: string; propertyId: string | null; reason: string }[]).sort(
184      (x, y) => x.index - y.index,
185    );
186    expect(cols).toEqual([
187      { index: 0, header: "Title", propertyId: props.Title, reason: "title" },
188      { index: 1, header: "UnknownCol", propertyId: null, reason: "no_matching_property" },
189      { index: 2, header: "Note", propertyId: null, reason: "no_matching_property" }
190    ]);
191    // Read-only + id-only properties are reported with a reason, never imported.
192    const unmapped = (
193      (d.unmappedProperties as { id: string; name: string; type: string; reason: string }[])
194        .map((p) => ({ name: p.name, reason: p.reason }))
195        .sort((x, y) => x.name.localeCompare(y.name))
196    );
197    expect(unmapped).toEqual([
198      { name: "Created", reason: "read_only" },
199      { name: "Owner", reason: "record_ids_only" },
200      { name: "Status", reason: "not_importable" }
201    ]);
202    // The dry run is a pure preview — nothing is written.
203    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
204    // A title-only import is a valid minimal import (the title is written
205    // through the page title): it succeeds with one record and no property rows.
206    const imp = await importCsv(databaseId, { csv, dryRun: false }, headers);
207    expect(imp.status).toBe(201);
208    const i = await json(imp);
209    expect(i.imported).toBe(2);
210    const records = await listRecords(databaseId, headers);
211    expect(records.map((r) => r.title).sort()).toEqual(["A", "B"]);
212    // No importable columns AT ALL (no title column, no property match) → 400.
213    const nothing = await importCsv(databaseId, { csv: "Foo,Bar\n1,2", dryRun: false }, headers);
214    expect(nothing.status).toBe(400);
215    expect(((await json(nothing)).error as { details: { issue: string }[] }).details[0]?.issue).toBe(
216      "no_importable_columns",
217    );
218  });
219});
220 
221describe("T-165 CSV import — per-row error isolation", () => {
222  it("skips rows with bad cells (row/column in errors) while good rows still import in the same call", async () => {
223    const { headers, databaseId, props } = await setupDb([
224      { name: "Score", type: "NUMBER" },
225      { name: "Due", type: "DATE" }
226    ]);
227    const csv = [
228      "Title,Score,Status,Due",
229      "Good,1,To Do,2026-01-01",
230      "BadNumber,xyz,To Do,2026-01-02",
231      "BadOption,2,DoesNotExist,2026-01-03",
232      "BadDate,3,Done,not-a-date"
233    ].join("\n");
234 
235    const imp = await importCsv(databaseId, { csv, dryRun: false }, headers);
236    expect(imp.status).toBe(201);
237    const i = await json(imp);
238    expect(i.imported).toBe(1);
239    expect(i.failed).toBe(3);
240    const errors = (i.errors as { row: number; column: string; error: string }[]).sort(
241      (a, b) => a.row - b.row,
242    );
243    expect(errors.map((e) => [e.row, e.column])).toEqual([
244      [3, "Score"],
245      [4, "Status"],
246      [5, "Due"]
247    ]);
248    for (const e of errors) expect(e.error.length).toBeGreaterThan(0);
249    expect(errors[0].error).toContain("xyz");
250    expect(errors[1].error).toContain("DoesNotExist");
251    expect(errors[2].error).toContain("not-a-date");
252 
253    const records = await listRecords(databaseId, headers);
254    expect(records.map((r) => r.title)).toEqual(["Good"]);
255    expect(records[0].properties[props.Score]).toEqual({ value: 1 });
256 
257    // The audit row records both the imported and the failed counts.
258    const audit = await app.prisma.auditLog.findFirst({
259      where: { action: "database.records_imported", resourceId: databaseId }
260    });
261    expect(audit?.metadata).toMatchObject({ imported: 1, failed: 3, rows: 4 });
262    expect(
263      await countAuditEvents(app.prisma, { action: "database.records_imported", actorId: audit?.actorId ?? "" }),
264    ).toBeGreaterThanOrEqual(1);
265  });
266});
267 
268describe("T-165 CSV import — explicit mappings", () => {
269  it("maps a renamed header to a writable property", async () => {
270    const { headers, databaseId, props } = await setupDb([{ name: "Note", type: "TEXT" }]);
271    const csv = ["Renamed,Title", "value-a,Alpha"].join("\n");
272    const imp = await importCsv(
273      databaseId,
274      { csv, dryRun: false, mappings: { Renamed: props.Note } },
275      headers,
276    );
277    expect(imp.status).toBe(201);
278    const i = await json(imp);
279    expect(i.imported).toBe(1);
280    const records = await listRecords(databaseId, headers);
281    expect(records[0].title).toBe("Alpha");
282    expect(records[0].properties[props.Note]).toEqual({ text: "value-a", marks: [] });
283  });
284 
285  it("rejects mappings that target the title, read-only, relation-id or unknown properties/headers", async () => {
286    const { headers, databaseId, props } = await setupDb([
287      { name: "Note", type: "TEXT" },
288      { name: "Created", type: "CREATED_TIME" },
289      { name: "Owner", type: "PERSON" }
290    ]);
291    const csv = ["Title,Extra", "Alpha,x"].join("\n");
292    const cases: [string, Record<string, unknown>, string][] = [
293      ["unknown header in mappings", { csv, mappings: { Nope: props.Note } }, "unknown_header"],
294      ["unknown property id", { csv, mappings: { Extra: "dbp_does_not_exist" } }, "unknown_property"],
295      ["title via mappings", { csv, mappings: { Title: props.Title } }, "title_via_title_header"],
296      ["read-only property", { csv, mappings: { Extra: props.Created } }, "read_only_property"],
297      ["record-id-only property", { csv, mappings: { Extra: props.Owner } }, "record_ids_only"]
298    ];
299    for (const [label, body, issue] of cases) {
300      const res = await importCsv(databaseId, { ...body, dryRun: false }, headers);
301      expect(res.status, label).toBe(400);
302      const details = ((await json(res)).error as { details: { issue: string }[] }).details;
303      expect(details.some((d) => d.issue === issue), label).toBe(true);
304    }
305    // Unknown titleHeader is a 400 as well.
306    const badTitle = await importCsv(databaseId, { csv, titleHeader: "NotACol", dryRun: false }, headers);
307    expect(badTitle.status).toBe(400);
308    expect(
309      ((await json(badTitle)).error as { details: { issue: string; field: string }[] }).details.some(
310        (d) => d.issue === "unknown_header" && d.field === "titleHeader",
311      ),
312    ).toBe(true);
313    // None of the rejections wrote anything.
314    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
315  });
316});
317 
318describe("T-165 CSV import — caps", () => {
319  it("rejects a body over the 2 MB ceiling with a 413 envelope", async () => {
320    const { headers, databaseId } = await setupDb();
321    // ~2.2 MB of rows (20.5k × ~107 chars) — comfortably over 2 MB.
322    const rows: string[] = [];
323    for (let i = 0; i < 20_500; i += 1) rows.push(`Row${i},"${"x".repeat(96)}`);
324    const res = await importCsv(databaseId, { csv: ["Title,Note", ...rows].join("\n") }, headers);
325    expect(res.status).toBe(413);
326    const e = (await json(res)).error as { code: string };
327    expect(e.code).toBe("PAYLOAD_TOO_LARGE");
328    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
329  });
330 
331  it("rejects more than 10k data rows with a 400 too_many_rows (no truncation)", async () => {
332    const { headers, databaseId } = await setupDb();
333    const rows: string[] = [];
334    for (let i = 0; i < 10_001; i += 1) rows.push(`Row${i}`);
335    const res = await importCsv(databaseId, { csv: ["Title", ...rows].join("\n") }, headers);
336    expect(res.status).toBe(400);
337    const e = (await json(res)).error as { code: string; details: { issue: string; received: number; limit: number }[] };
338    expect(e.code).toBe("VALIDATION_ERROR");
339    expect(e.details[0]).toMatchObject({ issue: "too_many_rows", received: 10_001, limit: 10_000 });
340    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
341  });
342});
343 
344describe("T-165 CSV import — validation + authorization", () => {
345  it("rejects an empty csv at the DTO boundary", async () => {
346    const { headers, databaseId } = await setupDb();
347    const res = await importCsv(databaseId, { csv: "" }, headers);
348    expect(res.status).toBe(400);
349    expect(((await json(res)).error as { code: string }).code).toBe("VALIDATION_ERROR");
350  });
351 
352  it("a VIEW member (GUEST) gets 403; a non-member gets 403; an unknown id gets 404", async () => {
353    const { headers, databaseId, workspaceId } = await setupDb();
354    // GUEST member → resolves VIEW < EDIT → 403.
355    const guest = await createUser(app.request);
356    await addMemberViaSeam(app.request, app.internalServiceToken, workspaceId, guest.email, "GUEST");
357    const guestRes = await importCsv(
358      databaseId,
359      { csv: "Title\nA" },
360      authHeaders(guest.accessToken!),
361    );
362    expect(guestRes.status).toBe(403);
363    expect(((await json(guestRes)).error as { code: string }).code).toBe("FORBIDDEN");
364 
365    // Non-member (no leak of the id: the module's house behavior is 403 for
366    // a known-but-inaccessible database — same as every other db endpoint).
367    const outsider = await createUser(app.request);
368    const outRes = await importCsv(
369      databaseId,
370      { csv: "Title\nA" },
371      authHeaders(outsider.accessToken!),
372    );
373    expect(outRes.status).toBe(403);
374 
375    // Unknown id → 404.
376    const nfRes = await importCsv("dbp_does_not_exist", { csv: "Title\nA" }, headers);
377    expect(nfRes.status).toBe(404);
378    expect(((await json(nfRes)).error as { code: string }).code).toBe("NOT_FOUND");
379  });
380});
381