Packet — P2.1.1 Reputation Record Schema

Task ID: c4bd3540-34a6-478a-b02b-567aa4aa2df6 Branch: feature/p2-1-1-rep-schema Step: 3 of 5 (audit ✓ → contract ✓ → packet → implement → verify)

Execution plan. Locked file order, locked test names, locked commit map. This packet gates Step 4; any change requires updating it first.

§1. File order

1. src/db/migrations/007_reputation.sql — schema first; no TS depends on it at compile time but every test does at runtime.
2. src/domains/reputation/schema.ts — types, Zod, helpers; imports bps-constants.ts.
3. src/__tests__/domains/reputation/schema.test.ts — proves the contract. New directory created by Jest at first run.

No other files touched. No changes to src/server.ts (no MCP tool registration in P2.1.1 — that’s P2.1.2+).

§2. Migration 007 contents

Plain SQL, no DML. Comments header per the 003_thought_records.sql / 004_skills.sql convention.

-- 007_reputation — λ Reputation per-domain state + append-only history (P2.1.1).
--
-- Introduces two tables:
--   reputations         — current per-(node, domain) state, PK (node_id, domain)
--   reputation_history  — append-only audit log of bps deltas, PK id AUTOINCREMENT
--
-- Five canonical domains: execution, commissioning, arbitration, governance, social.
-- Domain values are TEXT; closed-set enforcement lives in the TypeScript Zod layer.
--
-- score / scar_bps are integer basis points in [0, 10000] (1 bp = 0.01%).
-- delta is signed integer bps; negative = penalty.
--
-- Phase 0 posture: no Phase 0 tool reads or writes these tables. Schema lands
-- now so P2.1.2 (compute), P2.2.1 (decay), P2.2.2 (penalties) can build on
-- a fixed surface. See docs/3-world/social/reputation.md §Phase 0 posture.
--
-- Canonical references:
--   - docs/guides/implementation/task-prompts/p2.1-lambda-reputation.md §P2.1.1
--   - docs/guides/implementation/task-breakdown.md §P2.1.1
--   - docs/3-world/social/reputation.md
--   - docs/spec/s04-reputation.md
--   - docs/audits/p2-1-1-rep-schema-audit.md
--   - docs/contracts/p2-1-1-rep-schema-contract.md
--   - docs/packets/p2-1-1-rep-schema-packet.md

CREATE TABLE reputations (
  node_id              TEXT NOT NULL,
  domain               TEXT NOT NULL,
  score                INTEGER NOT NULL DEFAULT 0,
  scar_bps             INTEGER NOT NULL DEFAULT 0,
  ban_until_epoch      INTEGER,
  last_activity_epoch  INTEGER NOT NULL,
  PRIMARY KEY (node_id, domain),
  CHECK (score >= 0 AND score <= 10000),
  CHECK (scar_bps >= 0 AND scar_bps <= 10000)
);

CREATE TABLE reputation_history (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  node_id    TEXT NOT NULL,
  domain     TEXT NOT NULL,
  epoch      INTEGER NOT NULL,
  delta      INTEGER NOT NULL,
  reason     TEXT NOT NULL,
  event_id   TEXT NOT NULL
);

CREATE INDEX idx_reputations_lookup       ON reputations(node_id, domain);
CREATE INDEX idx_reputations_leaderboard  ON reputations(domain, score DESC);
CREATE INDEX idx_history_node             ON reputation_history(node_id, domain, epoch DESC);

After this migration applies, PRAGMA user_version advances to 7.

§3. src/domains/reputation/schema.ts structure

Module header:

• Comment block (file purpose + invariants + canonical refs).
• Imports: node:better-sqlite3 types, zod, BPS_MIN + BPS_MAX from ../rules/bps-constants.js (NodeNext ESM .js suffix on TS imports).

§A — Constants:

• DOMAINS readonly tuple. as const literal of the 5 string members.
• SCORE_MIN = Number(BPS_MIN) (= 0). SCORE_MAX = Number(BPS_MAX) (= 10000). Imported once; never duplicate the 10000 literal in TS.

§B — Types:

• export type Domain = (typeof DOMAINS)[number]
• export interface ReputationRow { ... } — 6 fields, integer bps semantics.
• export interface ReputationHistoryRow { ... } — 7 fields including AUTOINCREMENT id.

§C — Zod validators:

• DomainSchema = z.enum(DOMAINS). Closed set enforcement.
• ReputationRowSchema = z.object({ ... }) mirroring the interface. Score and scar_bps both .int().min(SCORE_MIN).max(SCORE_MAX). ban_until_epoch is .nullable().
• ReputationHistoryRowSchema = z.object({ ... }) for the full row. delta is .int() with no min/max (signed; penalty deltas are negative).
• A second HistoryInsertSchema = ReputationHistoryRowSchema.omit({ id: true }) used by insertHistoryEvent (since id is generated by AUTOINCREMENT).

§D — Functions:

• insertHistoryEvent(db, row) — Zod-validates, prepares INSERT, runs it, returns { id: lastInsertRowid as number }.
• selectReputation(db, node_id, domain?) — overloaded: with domain returns ReputationRow | null; without returns ReputationRow[]. Two prepared statements cached at module top so they survive across calls.
• selectHistory(db, node_id, domain, opts?) — prepared statement built per call because opts.before_epoch may or may not be present. Simpler than caching multiple statement variants.

No mutator exports. No updateReputation. No deleteReputation. No deleteHistory. AX-09 hard rule.

§4. schema.test.ts test plan

Sections + test names (Jest):

describe('DomainSchema', () => {
  test('rejects sixth domain "foo"', () => { ... });        // T1
  test('accepts all five canonical domains', () => { ... }); // T2
});

describe('ReputationRowSchema', () => {
  test('rejects score = -1', () => { ... });                // T3
  test('rejects score = 10001', () => { ... });             // T4
  test('rejects score = 100.5 (non-integer)', () => { ... }); // T5
  test('accepts a canonical row at score = 5000', () => { ... }); // T6
});

describe('ReputationHistoryRowSchema', () => {
  test('accepts a negative delta (signed bps)', () => { ... }); // T7
});

describe('migration 007', () => {
  test('applies cleanly to a fresh in-memory DB', () => { ... });  // T8
  test('is idempotent across two initDb calls', () => { ... });    // T9
});

describe('insertHistoryEvent + selectHistory', () => {
  test('append round-trip', () => { ... });                  // T10
});

describe('selectReputation', () => {
  test('returns null for a missing (node, domain) row', () => { ... });  // T11
  test('returns [] for a missing node across all domains', () => { ... });// T12
});

describe('append-only invariant (AX-01, AX-09)', () => {
  test('source contains no UPDATE/DELETE SQL', () => { ... });  // T13
  test('module exports contain no update/delete names', () => { ... }); // T14
});

§4.1 Migration smoke test approach

Cannot easily reuse initDb() against an in-memory DB because initDb constructs migrationsRoot() relative to the module file location and hard-codes file-on-disk paths. The clean path for tests:

Option A (used): Read 007_reputation.sql from disk + apply via db.exec(sql) on an in-memory better-sqlite3 instance. Idempotency tested by re-applying the file body wrapped in BEGIN; ... COMMIT; and catching SQLITE_ERROR: table reputations already exists — that confirms the schema is well-formed and the migration runner’s user_version gate is what makes it idempotent in production. Documented in test comment.

Option B (rejected): Use a tmp dir + initDb(tmpPath). Heavier; would need to add events_schema_version (?) coordination. Not necessary because the migration runner is already tested by src/__tests__/db/index.test.ts in P0.2.2.

Going with Option A but with the idempotency claim re-phrased: we verify that calling initDb twice does NOT error (the runner’s user_version short-circuit handles it). We do this with a tmpdir-backed real DB file.

Updated approach:

• T8 — use initDb(tmpDbPath); assert tables exist via sqlite_master.
• T9 — call initDb(tmpDbPath) twice in sequence; second call must not throw and must leave user_version = 7.

Both T8 + T9 cleanup with closeDb() + fs.rmSync in afterEach.

§4.2 Append-only grep test (T13)

import { readFileSync, readdirSync, statSync } from 'node:fs';
import { join } from 'node:path';

function walk(dir: string): string[] { /* recursive .ts collector */ }

test('source contains no UPDATE/DELETE SQL', () => {
  const repoDomainDir = join(process.cwd(), 'src', 'domains', 'reputation');
  const files = walk(repoDomainDir).filter(f => f.endsWith('.ts'));
  for (const f of files) {
    const src = readFileSync(f, 'utf-8');
    expect(src).not.toMatch(/UPDATE\s+reputation/i);
    expect(src).not.toMatch(/DELETE\s+FROM\s+reputation/i);
  }
});

Static analysis suffices; no need to parse SQL. False positives would only fire if a future task purposely adds an UPDATE — at which point the contract discussion is the right place.

§4.3 Export-name guard (T14)

import * as ReputationModule from '../../../domains/reputation/schema.js';

test('module exports contain no update/delete names', () => {
  const names = Object.keys(ReputationModule);
  expect(names).not.toContain('updateReputation');
  expect(names).not.toContain('deleteReputation');
  expect(names).not.toContain('deleteHistory');
  expect(names).not.toContain('truncateReputation');
});

§5. Commit map

Step	Files	Commit message
1 ✓	docs/audits/p2-1-1-rep-schema-audit.md	audit(p2-1-1-rep-schema): inventory surface
2 ✓	docs/contracts/p2-1-1-rep-schema-contract.md	contract(p2-1-1-rep-schema): behavioral contract
3 (this)	docs/packets/p2-1-1-rep-schema-packet.md	packet(p2-1-1-rep-schema): execution plan
4	src/db/migrations/007_reputation.sql, src/domains/reputation/schema.ts, src/__tests__/domains/reputation/schema.test.ts	feat(p2-1-1-rep-schema): 5-domain reputation schema + append-only history
5	docs/verification/p2-1-1-rep-schema-verification.md	verify(p2-1-1-rep-schema): test evidence

§6. Gate

After step 4, run in the worktree:

npm run build
npm run lint
npm test

All three must pass. If npm test regresses beyond P2.1.1’s new tests (expected delta: +14 tests for T1–T14, possibly bundled into fewer cases), investigate before opening the PR.

Baseline test count per memory header: 2421.

§7. Risks restated from audit

1. Float-INTEGER silent acceptance — mitigated by Zod .int() (TS layer).
2. Migration collision — 007 is free; verified.
3. node_id FK absence — by design (Phase 2 ξ identity table not shipped).
4. AUTOINCREMENT vs ROWID — AUTOINCREMENT chosen for monotonic audit cursor.
5. Bigint vs number — TS layer stays in number; bigint is compute-layer only.

Packet locked. Step 4 follows.