Storing & Querying Reports

A review workflow can store each session report with its submission and promote selected analysis fields to database columns. This page shows schemas and queries for that pattern.

What to store

Two things, in the same row:

The full report ({ data, analysis }) in a JSON column — the raw events, the analysis, and the tamper-evident signature (outputSignature, signedPayload). This is what you re-render into a scorecard later and what a relying party can independently re-verify.
Summary columns derived with summarizeAnalysis() at insert time — effort ratio, paste share, corrections, and related fields. Use these columns for indexes, sorting, and thresholds. The scorecard badges use the same calculation.

import { summarizeAnalysis } from 'writetrack';

const report = await tracker.getSessionReport(); // { data, analysis }
if (!report.analysis) throw new Error('Analysis unavailable');
const summary = summarizeAnalysis(report.analysis);

await db.query(
  `INSERT INTO submissions
     (candidate_id, report, effort_ratio, pasted_pct, largest_paste_pct, corrections)
   VALUES ($1, $2, $3, $4, $5, $6)`,
  [
    candidateId,
    JSON.stringify(report),
    summary.effortRatio,
    summary.pastedPct,
    summary.largestPastePct,
    summary.corrections,
  ]
);

Promote the AnalysisSummary fields used for sorting or filtering. Keep the full report in the JSON column.

Schema

SQLite

CREATE TABLE submissions (
  id INTEGER PRIMARY KEY,
  candidate_id TEXT NOT NULL,
  report TEXT NOT NULL, -- JSON, stored as TEXT; query into it with json_extract()
  effort_ratio REAL,
  pasted_pct REAL,
  largest_paste_pct REAL,
  corrections INTEGER,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_submissions_effort_ratio ON submissions (effort_ratio);

Postgres

CREATE TABLE submissions (
  id BIGSERIAL PRIMARY KEY,
  candidate_id TEXT NOT NULL,
  report JSONB NOT NULL,
  effort_ratio NUMERIC,
  pasted_pct NUMERIC,
  largest_paste_pct NUMERIC,
  corrections INTEGER,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_submissions_effort_ratio ON submissions (effort_ratio);

MySQL / MariaDB

CREATE TABLE submissions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  candidate_id VARCHAR(255) NOT NULL,
  report JSON NOT NULL,
  effort_ratio DECIMAL(6, 4),
  pasted_pct DECIMAL(6, 2),
  largest_paste_pct DECIMAL(6, 2),
  corrections INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_submissions_effort_ratio ON submissions (effort_ratio);

Query: sort a review queue by effort ratio

The summary column supports the same indexed sort in all three engines:

SELECT candidate_id, effort_ratio, pasted_pct
FROM submissions
ORDER BY effort_ratio ASC
LIMIT 20;

Query: find submissions with a single paste over 50% of the text

largestPastePct is a summary column, so this is the same shape as the query above:

SELECT candidate_id FROM submissions WHERE largest_paste_pct > 50;

See Analysis Reference for additional fields not included in summarizeAnalysis().

Payload size

Raw keystroke events run roughly 200–230 bytes each as JSON, and a full { data, analysis } report compresses at somewhere around 9–11:1 with gzip.

An internal stress test of a simulated 3-hour session (~13,400 keystrokes) produced a ~5–6 MB raw trace and ~400 KB gzip payload. HTTP or database compression can reduce storage and transfer size.

Rendering a stored report later

createSessionReport() accepts a wrapped { data, analysis } report and preserves the supplied analysis for <wt-scorecard>.setData():

const stored = await db.submissions.findById(id); // { report: { data, analysis }, ... }
const report = await createSessionReport({
  data: stored.report.data,
  analysis: stored.report.analysis,
});

document.querySelector('#scorecard').setData(report);

When the input includes analysis, createSessionReport() preserves it as supplied. Verify reports received across a trust boundary before using their signed fields:

import { verifyAnalysisSignatureAsync } from 'writetrack/verify';

const { analysis } = stored.report;
if (
  !analysis?.outputSignature ||
  !(await verifyAnalysisSignatureAsync(analysis, analysis.outputSignature))
) {
  throw new Error('Analysis signature verification failed');
}

Use analyzeEvents() to compute a new analysis from the stored capture. Server-side analysis requires a license key.

Browser rendering

Import writetrack/viz in browser code. summarizeAnalysis() is available in browser and Node environments.