feat: Store scan results in D1 for retrieval

[nmogil]

Summary

Add a persistence layer so scan results can be stored in D1 and retrieved later. This is the foundation for shareable links, export, and scan history.

Motivation

Scan results currently exist only in-memory during the request lifecycle (worker/src/index.ts:88-89). Once the response is sent, the data is gone. Storing results enables shareable links (#2), PDF/JSON export, and future scan history features.

Implementation Steps

1. Create D1 migration for scan_results table

File: worker/migrations/0002_scan_results.sql (new)

CREATE TABLE scan_results (
  id TEXT PRIMARY KEY,              -- crypto.randomUUID(), same as scanId
  share_token TEXT UNIQUE NOT NULL, -- 32-byte base62 token for URL
  result_json TEXT NOT NULL,        -- JSON-serialized ScanResponse
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT NOT NULL,         -- created_at + 48 hours
  caller_type TEXT NOT NULL         -- 'web' or 'api'
);

CREATE INDEX idx_scan_results_share_token ON scan_results(share_token);
CREATE INDEX idx_scan_results_expires_at ON scan_results(expires_at);

Run with: npx wrangler d1 migrations apply a2p-check-db

2. Add share token generator utility

File: worker/src/utils/shareToken.ts (new)

/**
 * Generates a cryptographically random base62 token (32 bytes → ~43 chars).
 * Collision probability is negligible at 192 bits of entropy.
 */
export function generateShareToken(): string {
  const bytes = new Uint8Array(32);
  crypto.getRandomValues(bytes);
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  return Array.from(bytes, (b) => chars[b % 62]).join('');
}

3. Add storage service

File: worker/src/services/scanStorage.ts (new)

import { Env, ScanResponse } from '../types';
import { generateShareToken } from '../utils/shareToken';

const TTL_HOURS = 48;

export async function storeScanResult(
  scanResponse: ScanResponse,
  env: Env,
  callerType: 'web' | 'api'
): Promise<string> {
  const shareToken = generateShareToken();
  const expiresAt = new Date(Date.now() + TTL_HOURS * 60 * 60 * 1000).toISOString();

  await env.DB.prepare(
    `INSERT INTO scan_results (id, share_token, result_json, expires_at, caller_type)
     VALUES (?, ?, ?, ?, ?)`
  ).bind(
    scanResponse.scanId,
    shareToken,
    JSON.stringify(scanResponse),
    expiresAt,
    callerType
  ).run();

  return shareToken;
}

export async function getScanByToken(
  token: string,
  env: Env
): Promise<ScanResponse | null> {
  const row = await env.DB.prepare(
    `SELECT result_json FROM scan_results
     WHERE share_token = ? AND expires_at > datetime('now')`
  ).bind(token).first<{ result_json: string }>();

  if (!row) return null;
  return JSON.parse(row.result_json) as ScanResponse;
}

4. Integrate storage into scan handler

File: worker/src/index.ts — modify handleScan() (line 88)

After const result = await orchestrateScan(...) on line 88, add:

const shareToken = await storeScanResult(result, env, caller.type);
return json({ ...result, shareToken });

This replaces the current return json(result) on line 89.

5. Add shareToken to ScanResponse type

File: worker/src/types.ts — add to ScanResponse interface (line 69)

export interface ScanResponse {
  // ... existing fields ...
  shareToken?: string;  // Only present in API response, not in stored JSON
}

File: web/lib/types.ts — mirror the same change

6. Add GET endpoint for retrieving shared results

File: worker/src/index.ts — add route after line 28

router.get('/api/v1/scan/:token', async (request, env) => {
  const { token } = request.params;
  if (!token || token.length < 30) {
    return error(400, { error: { code: 'VALIDATION_ERROR', message: 'Invalid share token' } });
  }
  const result = await getScanByToken(token, env);
  if (!result) {
    return error(404, { error: { code: 'NOT_FOUND', message: 'Scan not found or expired' } });
  }
  return json(result);
});

7. Add scheduled cleanup of expired results

File: worker/src/index.ts — add scheduled handler to the export (line 117)

export default {
  async fetch(request: Request, env: Env): Promise<Response> { ... },
  async scheduled(event: ScheduledEvent, env: Env, ctx: ExecutionContext): Promise<void> {
    await env.DB.prepare(
      `DELETE FROM scan_results WHERE expires_at < datetime('now')`
    ).run();
  },
} satisfies ExportedHandler<Env>;

File: worker/wrangler.toml — add cron trigger

[triggers]
crons = ["0 */6 * * *"]  # Every 6 hours

Testing

Unit tests

File: worker/test/services/scanStorage.test.ts (new)

1. generateShareToken() returns a string of length 32+ with only alphanumeric chars
2. generateShareToken() returns unique values across 1000 calls
3. storeScanResult() inserts a row and returns a token (mock D1)
4. getScanByToken() returns null for non-existent token
5. getScanByToken() returns null for expired token
6. getScanByToken() returns parsed ScanResponse for valid token

Integration tests

# Start dev server
cd worker && npm run dev

# Store a scan (run a normal scan, note the shareToken in response)
curl -X POST http://localhost:8787/api/v1/scan/quick \
  -H 'Content-Type: application/json' \
  -d '{"useCaseType":"MARKETING","campaignDescription":"Test campaign for marketing messages","sampleMessages":["Hello! Check our deals. Reply STOP to opt out","Sale today! Reply STOP to unsubscribe"],"messageFlow":"Users opt in via web form"}'

# Retrieve by token (use shareToken from above response)
curl http://localhost:8787/api/v1/scan/<shareToken>

# Verify 404 for invalid token
curl http://localhost:8787/api/v1/scan/nonexistenttoken123456789012

Acceptance Criteria

• Scan results stored in D1 with 48-hour TTL
• Share tokens are 32+ chars, base62, cryptographically random
• GET /api/v1/scan/:token returns stored result or 404
• Expired results return 404
• Scheduled cleanup removes expired rows every 6 hours
• No PII stored beyond what's in the scan request/response