Skill: Form Submissions

# Skill: Form Submissions

## Purpose
Handle public form submission: validate answers server-side, enforce required fields per page, process file uploads, store responses as JSON, and export responses to CSV.

---

## Answer Types

```typescript
// api/src/types.ts (additions)

// Value stored for each field in the response
export type AnswerValue =
  | string           // short_text, long_text, email, date, radio, dropdown
  | string[]         // checkbox (multi-select)
  | number           // number field
  | FileAnswer       // file upload
  | null;            // field not answered (non-required)

export interface FileAnswer {
  originalName: string;  // original filename from user
  storedName: string;    // UUID filename on disk
  mimeType: string;
  sizeBytes: number;
}

// Full response record
export interface Response {
  id: number;
  form_id: number;
  answers: Record<string, AnswerValue>; // keyed by field.id
  meta: ResponseMeta;
  submitted_at: string;
}

export interface ResponseMeta {
  ip_hash: string;          // SHA256 of IP, first 16 hex chars
  user_agent_hash: string;  // SHA256 of UA string, first 16 hex chars
}
```

---

## Server-side Answer Validation

```typescript
// api/src/lib/validateAnswers.ts
import type { FieldDefinition, AnswerValue } from '../types.js';

export interface ValidationError {
  field_id: string;
  message: string;
}

export function validateAnswers(
  schema: FieldDefinition[],
  answers: Record<string, AnswerValue>,
  visibleFieldIds: Set<string>,  // fields whose conditions are met
): ValidationError[] {
  const errors: ValidationError[] = [];

  for (const field of schema) {
    if (field.type === 'heading') continue;
    if (!visibleFieldIds.has(field.id)) continue;  // skip hidden fields

    const value = answers[field.id];
    const isEmpty = value === null || value === undefined || value === '' || (Array.isArray(value) && value.length === 0);

    if (field.required && isEmpty) {
      errors.push({ field_id: field.id, message: `${field.label} is required` });
      continue;
    }

    if (!isEmpty) {
      if (field.type === 'email' && typeof value === 'string') {
        if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value)) {
          errors.push({ field_id: field.id, message: 'Please enter a valid email address' });
        }
      }
      if (field.type === 'number' && typeof value === 'number') {
        if (field.min !== undefined && value < field.min) {
          errors.push({ field_id: field.id, message: `Minimum value is ${field.min}` });
        }
        if (field.max !== undefined && value > field.max) {
          errors.push({ field_id: field.id, message: `Maximum value is ${field.max}` });
        }
      }
    }
  }

  return errors;
}
```

---

## Server-side Condition Evaluation

```typescript
// api/src/lib/conditions.ts
import type { FieldDefinition, AnswerValue } from '../types.js';

type Answers = Record<string, AnswerValue>;

function toString(v: AnswerValue): string {
  if (v === null || v === undefined) return '';
  if (Array.isArray(v)) return v.join(', ');
  return String(v);
}

export function getVisibleFieldIds(
  schema: FieldDefinition[],
  answers: Answers,
): Set<string> {
  const visible = new Set<string>();
  for (const field of schema) {
    if (!field.condition) { visible.add(field.id); continue; }
    const { field_id, operator, value: target } = field.condition;
    const answer = toString(answers[field_id] ?? null);
    let show = false;
    switch (operator) {
      case 'eq':       show = answer === target; break;
      case 'neq':      show = answer !== target; break;
      case 'contains': show = answer.toLowerCase().includes(target.toLowerCase()); break;
      case 'gt':       show = Number(answer) > Number(target); break;
      case 'lt':       show = Number(answer) < Number(target); break;
    }
    if (show) visible.add(field.id);
  }
  return visible;
}
```

---

## IP and User-Agent Hashing

```typescript
// api/src/lib/hash.ts
import { createHash } from 'node:crypto';

export function hashForStorage(value: string): string {
  return createHash('sha256').update(value).digest('hex').slice(0, 16);
}
```

---

## Submit Route Handler

```typescript
// api/src/routes/submissions.ts
import express from 'express';
import type { Request, Response } from 'express';
import { db } from '../db.js';
import { validateAnswers } from '../lib/validateAnswers.js';
import { getVisibleFieldIds } from '../lib/conditions.js';
import { hashForStorage } from '../lib/hash.js';
import type { Form, AnswerValue, FileAnswer } from '../types.js';
import multer from 'multer';
import path from 'node:path';
import { v4 as uuid } from 'uuid';

const UPLOADS_DIR = process.env.UPLOADS_DIR ?? './uploads';
const MAX_FILE_SIZE_MB = Number(process.env.MAX_FILE_SIZE_MB ?? 10);

const storage = multer.diskStorage({
  destination: UPLOADS_DIR,
  filename: (_req, _file, cb) => cb(null, uuid()),
});

const upload = multer({
  storage,
  limits: { fileSize: MAX_FILE_SIZE_MB * 1024 * 1024 },
  fileFilter: (_req, file, cb) => {
    // Accept any file; MIME type is stored for display only
    cb(null, true);
  },
});

const router = express.Router();

// GET /s/:token  -- public form info (no content field)
router.get('/:token', (req, res: Response) => {
  const form = db.prepare(`
    SELECT id, title, description, schema, pages, settings, published, closed
    FROM forms WHERE share_token = ?
  `).get(req.params.token) as Form | undefined;

  if (!form || !form.published) return res.status(404).json({ error: 'Form not found' });
  if (form.closed) return res.status(410).json({ error: 'This form is no longer accepting responses' });

  res.json({
    id: form.id,
    title: form.title,
    description: form.description,
    schema: JSON.parse(form.schema as unknown as string),
    pages: JSON.parse(form.pages as unknown as string),
    settings: JSON.parse(form.settings as unknown as string),
  });
});

// POST /s/:token  -- submit response (supports multipart for file uploads)
router.post('/:token', upload.any(), async (req: Request, res: Response, next) => {
  try {
    const form = db.prepare(`
      SELECT id, schema, pages, settings, published, closed FROM forms WHERE share_token = ?
    `).get(req.params.token) as Form | undefined;

    if (!form || !form.published) return res.status(404).json({ error: 'Form not found' });
    if (form.closed) return res.status(410).json({ error: 'Form closed' });

    const schema = JSON.parse(form.schema as unknown as string) as FieldDefinition[];

    // Parse answers from body (multipart or JSON)
    const rawAnswers: Record<string, AnswerValue> = {};
    for (const [key, value] of Object.entries(req.body as Record<string, unknown>)) {
      if (typeof value === 'string') rawAnswers[key] = value;
      else if (Array.isArray(value)) rawAnswers[key] = value as string[];
    }

    // Handle uploaded files
    for (const file of (req.files as Express.Multer.File[] | undefined) ?? []) {
      rawAnswers[file.fieldname] = {
        originalName: file.originalname,
        storedName: file.filename,
        mimeType: file.mimetype,
        sizeBytes: file.size,
      } satisfies FileAnswer;
    }

    const visibleIds = getVisibleFieldIds(schema, rawAnswers);
    const errors = validateAnswers(schema, rawAnswers, visibleIds);
    if (errors.length > 0) {
      return res.status(422).json({ errors });
    }

    // Strip hidden field answers
    const cleanAnswers: Record<string, AnswerValue> = {};
    for (const field of schema) {
      if (visibleIds.has(field.id) && field.type !== 'heading') {
        cleanAnswers[field.id] = rawAnswers[field.id] ?? null;
      }
    }

    const meta = {
      ip_hash: hashForStorage(req.ip ?? 'unknown'),
      user_agent_hash: hashForStorage(req.headers['user-agent'] ?? 'unknown'),
    };

    db.prepare('INSERT INTO responses (form_id, answers, meta) VALUES (?, ?, ?)').run(
      form.id, JSON.stringify(cleanAnswers), JSON.stringify(meta),
    );
    db.prepare('UPDATE forms SET response_count = response_count + 1 WHERE id = ?').run(form.id);

    const settings = JSON.parse(form.settings as unknown as string) as FormSettings;
    res.status(201).json({
      success: true,
      successMessage: settings.successMessage ?? 'Thank you for your response!',
      successRedirectUrl: settings.successRedirectUrl ?? null,
    });
  } catch (err) {
    next(err);
  }
});

export default router;
```

---

## CSV Export

```typescript
// api/src/routes/export.ts
import { stringify } from 'csv-stringify';
import type { Response as ExpressResponse } from 'express';
import { db } from '../db.js';
import type { FieldDefinition, AnswerValue, FileAnswer } from '../types.js';

// Strip leading formula injection characters from CSV cell values
function sanitizeCsvCell(value: string): string {
  return value.replace(/^[=+\-@]/, '');
}

function answerToString(value: AnswerValue): string {
  if (value === null || value === undefined) return '';
  if (typeof value === 'string') return value;
  if (typeof value === 'number') return String(value);
  if (Array.isArray(value)) return value.join('; ');
  if (typeof value === 'object' && 'originalName' in value) {
    return (value as FileAnswer).originalName;
  }
  return '';
}

export async function exportCsv(
  formId: number,
  userId: number,
  res: ExpressResponse,
): Promise<void> {
  const form = db.prepare('SELECT * FROM forms WHERE id = ? AND user_id = ?').get(formId, userId) as Form | undefined;
  if (!form) { res.status(404).json({ error: 'Not found' }); return; }

  const schema = JSON.parse(form.schema as unknown as string) as FieldDefinition[];
  const dataFields = schema.filter(f => f.type !== 'heading');
  const headers = ['#', 'Submitted at', ...dataFields.map(f => f.label)];

  const responses = db.prepare(
    'SELECT id, answers, submitted_at FROM responses WHERE form_id = ? ORDER BY submitted_at ASC'
  ).all(formId) as Array<{ id: number; answers: string; submitted_at: string }>;

  res.setHeader('Content-Type', 'text/csv; charset=utf-8');
  res.setHeader('Content-Disposition', `attachment; filename="responses-${formId}.csv"`);

  const stringifier = stringify({ header: true, columns: headers });
  stringifier.pipe(res);

  for (const row of responses) {
    const answers: Record<string, AnswerValue> = JSON.parse(row.answers);
    const cells: string[] = [
      String(row.id),
      row.submitted_at,
      ...dataFields.map(f => sanitizeCsvCell(answerToString(answers[f.id] ?? null))),
    ];
    stringifier.write(cells);
  }

  stringifier.end();
}
```

---

## Vitest Tests

```typescript
// api/src/lib/__tests__/validateAnswers.test.ts
import { describe, it, expect } from 'vitest';
import { validateAnswers } from '../validateAnswers.js';

const shortTextField = { id: 'f1', type: 'short_text', label: 'Name', required: true } as const;
const emailField     = { id: 'f2', type: 'email',      label: 'Email', required: true } as const;
const numberField    = { id: 'f3', type: 'number',     label: 'Age', required: false, min: 0, max: 120 } as const;

describe('validateAnswers', () => {
  it('returns error for missing required field', () => {
    const errors = validateAnswers([shortTextField], {}, new Set(['f1']));
    expect(errors).toHaveLength(1);
    expect(errors[0].field_id).toBe('f1');
  });

  it('passes when required field is filled', () => {
    const errors = validateAnswers([shortTextField], { f1: 'Alice' }, new Set(['f1']));
    expect(errors).toHaveLength(0);
  });

  it('skips hidden fields', () => {
    const errors = validateAnswers([shortTextField], {}, new Set()); // f1 is not in visibleIds
    expect(errors).toHaveLength(0);
  });

  it('validates email format', () => {
    const errors = validateAnswers([emailField], { f2: 'not-an-email' }, new Set(['f2']));
    expect(errors[0].field_id).toBe('f2');
    expect(errors[0].message).toContain('email');
  });

  it('validates number min/max', () => {
    const e1 = validateAnswers([numberField], { f3: -1 }, new Set(['f3']));
    expect(e1[0].field_id).toBe('f3');
    const e2 = validateAnswers([numberField], { f3: 150 }, new Set(['f3']));
    expect(e2[0].field_id).toBe('f3');
    const e3 = validateAnswers([numberField], { f3: 25 }, new Set(['f3']));
    expect(e3).toHaveLength(0);
  });
});

// api/src/lib/__tests__/csv.test.ts
import { describe, it, expect } from 'vitest';

function sanitizeCsvCell(value: string): string {
  return value.replace(/^[=+\-@]/, '');
}

describe('sanitizeCsvCell', () => {
  it('strips leading = (formula injection)', () => {
    expect(sanitizeCsvCell('=SUM(A1:A10)')).toBe('SUM(A1:A10)');
  });
  it('strips leading +', () => {
    expect(sanitizeCsvCell('+1234')).toBe('1234');
  });
  it('strips leading -', () => {
    expect(sanitizeCsvCell('-1')).toBe('1');
  });
  it('strips leading @', () => {
    expect(sanitizeCsvCell('@IMPORTRANGE("url")')).toBe('IMPORTRANGE("url")');
  });
  it('leaves normal strings unchanged', () => {
    expect(sanitizeCsvCell('Alice Lee')).toBe('Alice Lee');
    expect(sanitizeCsvCell('alice@example.com')).toBe('alice@example.com');
  });
});
```

---

## Key Notes

1. Multer file filter accepts all MIME types. MIME type is stored in the answer JSON for display but never used to serve the file back.
2. Files are stored in `UPLOADS_DIR` with a UUID filename only. The `originalName` is stored purely for display.
3. Serve uploaded files via a protected route: `GET /api/files/:formId/:storedName` with owner auth check.
4. Never serve uploaded files via `express.static()` on the uploads directory.
5. The hidden field answer stripping happens before storage: if a condition was not met, the answer is not stored even if the submitter sent a value for it.
6. The `response_count` cached counter is incremented in the same SQLite transaction as the response insert using `better-sqlite3`'s synchronous API.