submissions.service.ts

submissions.service.tsapps/api/src/modules/forms/submissions.service.tsraw ← forms15,624 B← Back to the app
1/**
2 * TASK-0109 — form responses: list (joined view model), delete (cascade),
3 * export stub.
4 *
5 * The responses are WORKSPACE data (answers can carry PII; IPs are PII):
6 *   - list requires `VIEW` on the form's database;
7 *   - the IP is MASKED by default (`1.2.***.***` — first two octets); the
8 *     full IP is returned ONLY to workspace Owner/Admin (the ROLE, not
9 *     merely a permission level — privacy-by-default, spec §1);
10 *   - delete requires `EDIT` on the database (Owner/Admin pass via the role
11 *     baseline) and a `confirm=true` query flag (422 CONFIRM_REQUIRED
12 *     without it — accidental programmatic deletes are made explicit).
13 *
14 * Delete cascade (spec §3, one transaction): the created record (+ its
15 * backing page + blocks — Phase-5 `deleteRecord` semantics), the submission
16 * row, and every FILE linked via `metadata.formId` = this form +
17 * `metadata.submissionId` = this submission (their objects via the Phase-9
18 * purge path: object + version objects + thumbs + the workspace byte
19 * counter). Then the audit rows: `record.deleted` per deleted record +
20 * `form.submission_deleted` (metadata: submissionId, recordId, submitter).
21 *
22 * Export (spec §5): `GET /forms/:formId/export` → 501 NOT_IMPLEMENTED with
23 * `implementedIn: "phase-13"`; the future CSV contract is documented in
24 * `docs/api/forms.md` so Phase 13 implements against it (the UI button is
25 * disabled with the same roadmap note).
26 */
27import { Inject, Injectable } from "@nestjs/common";
28import type { Request } from "express";
29import { PrismaService } from "../../common/prisma.service.js";
30import { AuditService } from "../../common/audit.service.js";
31import { logger } from "../../common/logger.js";
32import { ApiError, type ErrorDetail } from "../../common/errors.js";
33import { FORM_ERROR_CODES } from "@factory/shared";
34import { actorIsAdminKey, assertCan } from "../../lib/authorize.js";
35import { deletePrefix, makeFilesClient, makeThumbsClient } from "../files/s3.js";
36import { adjustWorkspaceStorageBytes } from "../files/quota.js";
37 
38export interface SubmissionsQuery {
39  cursor?: string;
40  limit?: number;
41  q?: string;
42  from?: string;
43  to?: string;
44  anonymousOnly?: boolean;
45}
46 
47export interface SubmitterView {
48  id: string;
49  name: string;
50  avatarUrl: string | null;
51  email: string | null;
52  anonymous: false;
53}
54 
55export interface SubmissionView {
56  id: string;
57  formId: string;
58  recordId: string | null;
59  /** The raw answers (questionId → value) — the audit trail, unmasked. */
60  answers: Record<string, unknown>;
61  /** Question labels joined server-side (questionId → { label, type }). */
62  questionLabels: Record<string, { label: string; type: string }>;
63  submittedBy: SubmitterView | null;
64  /** Masked by default (`1.2.***.***`); full only for workspace Owner/Admin. */
65  ip: string;
66  ipMasked: boolean;
67  createdAt: Date;
68}
69 
70/** Mask an IPv4 to its first two octets (`1.2.***.***`). Non-IPv4 → "***". */
71export function maskIp(ip: string | null | undefined): string {
72  if (!ip) return "***";
73  const v4 = ip.replace(/^::ffff:/, "");
74  const parts = v4.split(".");
75  if (parts.length === 4 && parts.every((p) => /^\d{1,3}$/.test(p))) {
76    return `${parts[0]}.${parts[1]}.***.***`;
77  }
78  return "***";
79}
80 
81/** The workspace Owner/Admin role check (IP unmask + delete fast-path). */
82export async function isWorkspaceAdmin(
83  prisma: PrismaService,
84  workspaceId: string,
85  userId: string
86): Promise<boolean> {
87  const membership = await prisma.workspaceMember.findUnique({
88    where: { workspaceId_userId: { workspaceId, userId } },
89    select: { role: true }
90  });
91  return membership?.role === "OWNER" || membership?.role === "ADMIN";
92}
93 
94@Injectable()
95export class SubmissionsService {
96  constructor(
97    @Inject(PrismaService) private readonly prisma: PrismaService,
98    @Inject(AuditService) private readonly audit: AuditService
99  ) {}
100 
101  /** `GET /forms/:formId/submissions` — cursor list of the joined view. */
102  async list(
103    formId: string,
104    userId: string,
105    query: SubmissionsQuery
106  ): Promise<{ items: SubmissionView[]; nextCursor: string | null }> {
107    const form = await this.prisma.form.findUnique({ where: { id: formId } });
108    if (!form) throw ApiError.notFound("Form not found.");
109    await assertCan(this.prisma, { workspaceId: form.workspaceId, userId, resource: { databaseId: form.databaseId } }, "VIEW");
110 
111    const limit = Math.min(Math.max(Number(query.limit ?? 50) || 50, 1), 100);
112 
113    // 1. submissions (filtered + cursor + a one-row over-fetch for nextCursor).
114    const where: Record<string, unknown> = { formId };
115    if (query.anonymousOnly) where.submittedBy = null;
116    if (query.from || query.to) {
117      const createdAt: Record<string, Date> = {};
118      if (query.from) createdAt.gte = new Date(query.from);
119      if (query.to) createdAt.lte = new Date(query.to);
120      where.createdAt = createdAt;
121    }
122    const rows = await this.prisma.formSubmission.findMany({
123      where,
124      orderBy: { createdAt: "desc" },
125      ...(query.cursor ? { cursor: { id: query.cursor }, skip: 1 } : {}),
126      take: limit + 1
127    });
128 
129    const hasMore = rows.length > limit;
130    const page = hasMore ? rows.slice(0, limit) : rows;
131    const nextCursor = hasMore ? (page[page.length - 1]?.id ?? null) : null;
132    if (page.length === 0) return { items: [], nextCursor: null };
133 
134    // 2. join data (batched — no N+1: questions + submitters = 2 queries
135    //    regardless of page size). A submitter whose USER ROW is gone is
136    //    still "Former member" (the id survives in the submission row).
137    const submitterIds = [...new Set(page.map((s) => s.submittedBy).filter((v): v is string => Boolean(v)))];
138    const [questions, userRows] = await Promise.all([
139      this.prisma.formQuestion.findMany({
140        where: { formId },
141        select: { id: true, label: true, type: true }
142      }),
143      this.prisma.user.findMany({
144        where: { id: { in: submitterIds } },
145        select: { id: true, name: true, displayName: true, avatarUrl: true, email: true }
146      })
147    ]);
148    const labelByQ = new Map(questions.map((q) => [q.id, { label: q.label, type: q.type }]));
149    const userById = new Map(userRows.map((u) => [u.id, u]));
150    // TASK-0127: an ADMIN-scoped API key of the workspace sees the admin
151    // view (full IPs); a READ/WRITE key does not. Never the creator's role.
152    const admin =
153      actorIsAdminKey(form.workspaceId) || (await isWorkspaceAdmin(this.prisma, form.workspaceId, userId));
154 
155    // 3. build views, then apply the `q=` search (matches answer text,
156    //    submitter name/email, or the IP — masked or full as the role sees).
157    let items = page.map((s) => this.buildView(s, labelByQ, userById, admin));
158    const needle = query.q?.trim().toLowerCase();
159    if (needle) {
160      items = items.filter((v) => {
161        const haystacks = [
162          JSON.stringify(v.answers),
163          v.submittedBy?.name ?? "",
164          v.submittedBy?.email ?? "",
165          v.ip
166        ].join("\n");
167        return haystacks.toLowerCase().includes(needle);
168      });
169    }
170 
171    return { items, nextCursor };
172  }
173 
174  /** Build one SubmissionView row (the spec §6 view model). */
175  private buildView(
176    s: {
177      id: string;
178      formId: string;
179      recordId: string | null;
180      answers: unknown;
181      submittedBy: string | null;
182      ipAddress: string | null;
183      createdAt: Date;
184    },
185    labelByQ: Map<string, { label: string; type: string }>,
186    userById: Map<string, { id: string; name: string | null; displayName: string | null; avatarUrl: string | null; email: string | null }>,
187    admin: boolean
188  ): SubmissionView {
189    const answers = (s.answers ?? {}) as Record<string, unknown>;
190    const questionLabels: Record<string, { label: string; type: string }> = {};
191    for (const qid of Object.keys(answers)) {
192      questionLabels[qid] = labelByQ.get(qid) ?? { label: "Deleted question", type: "UNKNOWN" };
193    }
194 
195    let submittedBy: SubmitterView | null = null;
196    if (s.submittedBy) {
197      const u = userById.get(s.submittedBy);
198      submittedBy = u
199        ? {
200            id: u.id,
201            name: u.displayName ?? u.name ?? "Member",
202            avatarUrl: u.avatarUrl,
203            email: u.email,
204            anonymous: false
205          }
206        : { id: s.submittedBy, name: "Former member", avatarUrl: null, email: null, anonymous: false };
207    }
208 
209    const ip = s.ipAddress ?? "";
210    return {
211      id: s.id,
212      formId: s.formId,
213      recordId: s.recordId,
214      answers,
215      questionLabels,
216      submittedBy,
217      ip: admin ? ip : maskIp(ip),
218      ipMasked: !admin,
219      createdAt: s.createdAt
220    };
221  }
222 
223  /**
224   * `DELETE /forms/:formId/submissions/:submissionId?confirm=true` — the
225   * cascade (spec §3). One transaction for the DB side; the object-storage
226   * purge runs against the collected keys (best-effort per key — a storage
227   * blip must not wedge the cascade; the orphan sweeper reaps leftovers).
228   */
229  async delete(formId: string, submissionId: string, userId: string, confirm: boolean, req: Request) {
230    if (confirm !== true) {
231      throw new ApiError("UNPROCESSABLE_ENTITY", 422, "Deletion requires the confirm=true flag.", [
232        { reason: FORM_ERROR_CODES.CONFIRM_REQUIRED }
233      ]);
234    }
235 
236    const form = await this.prisma.form.findUnique({ where: { id: formId } });
237    if (!form) throw ApiError.notFound("Form not found.");
238    await assertCan(this.prisma, { workspaceId: form.workspaceId, userId, resource: { databaseId: form.databaseId } }, "EDIT");
239 
240    const submission = await this.prisma.formSubmission.findFirst({
241      where: { id: submissionId, formId }
242    });
243    if (!submission) throw ApiError.notFound("Submission not found.");
244 
245    // Plan: which resources does this submission own?
246    const record = submission.recordId
247      ? await this.prisma.databaseRecord.findUnique({
248          where: { id: submission.recordId },
249          select: { id: true, pageId: true, workspaceId: true, databaseId: true }
250        })
251      : null;
252 
253    // Linked files: metadata.formId = this form AND metadata.submissionId =
254    // this submission (stamped by the submit pipeline; a forged metadata is
255    // impossible — the submit is the only writer of submissionId). The
256    // STORAGE PLAN (object keys + sizes) is captured BEFORE the transaction
257    // deletes the rows — the post-commit purge needs them.
258    const files = await this.prisma.file.findMany({
259      where: {
260        workspaceId: form.workspaceId,
261        deletedAt: null,
262        metadata: { path: ["formId"], equals: form.id },
263        AND: [{ metadata: { path: ["submissionId"], equals: submission.id } }]
264      },
265      select: { id: true, key: true, size: true }
266    });
267    const purgedFileIds = files.map((f) => f.id);
268    const purgedVersionKeys: string[] = [];
269    if (purgedFileIds.length > 0) {
270      const versionRows = await this.prisma.fileVersion.findMany({
271        where: { fileId: { in: purgedFileIds } },
272        select: { key: true }
273      });
274      purgedVersionKeys.push(...versionRows.map((v) => v.key));
275    }
276    const purgedBytes = BigInt(files.reduce((sum, f) => sum + f.size, 0));
277 
278    await this.prisma.$transaction(async (tx) => {
279      // 1. the record + its backing page + blocks (Phase-5 deleteRecord
280      //    semantics — explicit 1:1 page removal; properties/links cascade).
281      if (record) {
282        if (record.pageId) {
283          await tx.block.deleteMany({ where: { pageId: record.pageId } });
284          await tx.page.delete({ where: { id: record.pageId } });
285        }
286        await tx.databaseRecord.delete({ where: { id: record.id } });
287      }
288 
289      // 2. the linked files' rows (Phase-9 purge; the OBJECTS are deleted
290      //    after commit — storage calls must not sit in the write window).
291      if (purgedFileIds.length > 0) {
292        await tx.fileVersion.deleteMany({ where: { fileId: { in: purgedFileIds } } });
293        await tx.file.deleteMany({ where: { id: { in: purgedFileIds } } });
294      }
295 
296      // 3. the submission row itself (the last one — the cascade's root).
297      await tx.formSubmission.delete({ where: { id: submission.id } });
298 
299      // 4. the workspace byte counter (every purged file's bytes leave the
300      //    stored total — the Phase-9 purge invariant).
301      if (purgedBytes > 0n) {
302        await adjustWorkspaceStorageBytes(tx, form.workspaceId, -purgedBytes);
303      }
304    });
305 
306    // Object-storage purge (post-commit, best-effort per key — a storage
307    // blip must not wedge the cascade; the orphan sweeper reaps leftovers).
308    if (purgedFileIds.length > 0) {
309      await this.purgeStorage(form.workspaceId, {
310        fileIds: purgedFileIds,
311        fileKeys: files.map((f) => f.key),
312        versionKeys: purgedVersionKeys
313      });
314    }
315 
316    // 4. audit (spec §3: record.deleted + form.submission_deleted).
317    if (record) {
318      await this.audit.audit(req, req.auth, {
319        action: "record.deleted",
320        resourceType: "record",
321        resourceId: record.id,
322        workspaceId: record.workspaceId,
323        metadata: { databaseId: record.databaseId, via: "form_submission_deleted", submissionId: submission.id }
324      });
325    }
326    await this.audit.audit(req, req.auth, {
327      action: "form.submission_deleted",
328      resourceType: "form_submission",
329      resourceId: submission.id,
330      workspaceId: form.workspaceId,
331      metadata: {
332        formId: form.id,
333        recordId: submission.recordId,
334        submitter: submission.submittedBy ?? "anonymous",
335        filesPurged: purgedFileIds.length
336      }
337    });
338 
339    return {
340      deleted: true,
341      recordDeleted: record !== null,
342      filesPurged: purgedFileIds.length
343    };
344  }
345 
346  /** Delete the file objects + version objects + thumbnails for purged rows. */
347  private async purgeStorage(
348    workspaceId: string,
349    plan: { fileIds: string[]; fileKeys: string[]; versionKeys: string[] }
350  ): Promise<void> {
351    const s3 = makeFilesClient();
352    const s3Thumbs = makeThumbsClient();
353    for (const key of plan.fileKeys) {
354      await s3.deleteObject(key).catch(() => undefined);
355    }
356    for (const key of plan.versionKeys) {
357      await s3.deleteObject(key).catch(() => undefined);
358    }
359    // The WHOLE thumbnail set per file (160/480/1280 artifacts live under
360    // `thumbs/{ws}/{fileId}/{size}/…` — deleting only one would orphan rest).
361    for (const id of plan.fileIds) {
362      await deletePrefix(s3Thumbs, `thumbs/${workspaceId}/${id}/`).catch(() => 0);
363    }
364    logger.info(
365      `form submission cascade purged storage (ws ${workspaceId}): ${plan.fileIds.length} file(s), ` +
366        `${plan.fileKeys.length + plan.versionKeys.length} object(s)`
367    );
368  }
369 
370  /** `GET /forms/:formId/export` — the Phase-13 stub (spec §5). */
371  async export(formId: string, userId: string, format: string) {
372    const form = await this.prisma.form.findUnique({ where: { id: formId } });
373    if (!form) throw ApiError.notFound("Form not found.");
374    await assertCan(this.prisma, { workspaceId: form.workspaceId, userId, resource: { databaseId: form.databaseId } }, "VIEW");
375    void format;
376    const details: ErrorDetail[] = [
377      { reason: FORM_ERROR_CODES.NOT_IMPLEMENTED_YET, implementedIn: "phase-13" },
378      {
379        contract:
380          "CSV columns: submission number, received (ISO 8601), submitter (email-or-id-or-anonymous), one column per question (\"label (type)\"), record link. JSON: { items: [SubmissionView] }. See docs/api/forms.md."
381      }
382    ];
383    throw new ApiError("NOT_IMPLEMENTED", 501, "CSV export is not implemented yet.", details);
384  }
385}
386