`tests/measure-attribution-sql.test.js`

68 lines
'use strict';

const test = require('node:test');
const assert = require('node:assert/strict');
const fs = require('node:fs');
const path = require('node:path');

const migrationPath = path.join(__dirname, '..', 'db', 'migrations', '024_measure_attribution_phase_a.sql');
const sql = fs.readFileSync(migrationPath, 'utf8');

function has(pattern, message) {
  assert.match(sql, pattern, message);
}

test('migration is additive, transactional, and contains no data mutation or feature activation', () => {
  has(/^BEGIN;/m, 'transaction begins');
  has(/^COMMIT;/m, 'transaction commits');
  assert.doesNotMatch(sql, /\b(?:INSERT\s+INTO|UPDATE|DELETE\s+FROM|TRUNCATE|DROP\s+(?:TABLE|COLUMN)|CREATE\s+TRIGGER)\b/i);
  assert.doesNotMatch(sql, /MEASURE_(?:CAPTURE|ATTRIBUTION|ANALYTICS)_V1\s*=|MEASURE_CAPTURE_V2\s*=/i);
});

test('routing attempts enforce one root, non-self successors, and exact job/installer identity', () => {
  has(/CREATE TABLE measure_job_routing_attempts/, 'routing table');
  has(/attempt_no = 1 AND supersedes_attempt_id IS NULL AND supersedes_job_id IS NULL/, 'root shape');
  has(/attempt_no > 1 AND supersedes_attempt_id IS NOT NULL/, 'successor predecessor');
  has(/supersedes_attempt_id <> id/, 'no self predecessor');
  has(/CREATE UNIQUE INDEX routing_attempt_one_root_per_job_idx[\s\S]*WHERE supersedes_attempt_id IS NULL/, 'one root index');
  has(/CREATE UNIQUE INDEX routing_attempt_one_successor_idx[\s\S]*WHERE supersedes_attempt_id IS NOT NULL/, 'one successor index');
  has(/FOREIGN KEY \(current_routing_attempt_id, id, routed_to\)[\s\S]*REFERENCES measure_job_routing_attempts \(id, measure_job_id, installer_id\)/, 'current route projection identity');
});

test('candidate facts bind booking installer to exact routing attempt', () => {
  has(/CREATE TABLE measure_booking_attribution_candidates/, 'candidate table');
  has(/FOREIGN KEY \(booking_id, installer_id\)[\s\S]*REFERENCES bookings \(id, installer_id\)/, 'booking installer FK');
  has(/FOREIGN KEY \(routing_attempt_id, measure_job_id, installer_id\)[\s\S]*REFERENCES measure_job_routing_attempts \(id, measure_job_id, installer_id\)/, 'routing identity FK');
  assert.doesNotMatch(sql, /\btouch_kind\b/i);
});

test('append-only decisions and current projection have valid root, successor, and same-booking constraints', () => {
  has(/CREATE TABLE measure_booking_primary_decisions/, 'decision table');
  has(/supersedes_decision_id IS NULL AND supersedes_booking_id IS NULL[\s\S]*decision_kind = 'select'/, 'root select only');
  has(/decision_kind <> 'clear' OR supersedes_decision_id IS NOT NULL/, 'clear has predecessor');
  has(/supersedes_decision_id <> id/, 'no self predecessor');
  has(/MATCH FULL ON DELETE RESTRICT/, 'paired nullable FK uses MATCH FULL');
  has(/CREATE UNIQUE INDEX primary_decision_one_root_per_booking_idx[\s\S]*WHERE supersedes_decision_id IS NULL/, 'one root decision');
  has(/CREATE UNIQUE INDEX primary_decision_one_successor_idx[\s\S]*WHERE supersedes_decision_id IS NOT NULL/, 'one successor decision');
  has(/CREATE TABLE measure_booking_primary_current[\s\S]*booking_id INTEGER PRIMARY KEY/, 'one current projection per booking');
});

test('event idempotency uses valid provider-scoped and internal partial indexes', () => {
  has(/CREATE UNIQUE INDEX measure_job_events_source_idempotency_idx[\s\S]*\(source_system, source_event_id\)[\s\S]*WHERE source_event_id IS NOT NULL/, 'provider index');
  has(/CREATE UNIQUE INDEX measure_job_events_internal_idempotency_idx[\s\S]*\(measure_job_id, event_type, correlation_id\)[\s\S]*WHERE source_event_id IS NULL/, 'internal index');
  has(/source_event_id IS NULL AND source_system IS NULL[\s\S]*source_event_id IS NOT NULL AND source_system IS NOT NULL/, 'source pair constraint');
});

test('known invalid or superseded SQL shapes are absent', () => {
  assert.doesNotMatch(sql, /UNIQUE\s*\([^)]*\)\s*WHERE/i, 'inline partial UNIQUE is invalid PostgreSQL');
  assert.doesNotMatch(sql, /measure_booking_attributions/i, 'old mutable attribution table');
  assert.doesNotMatch(sql, /measure_booking_one_primary_per_booking_idx/i, 'old mutable primary index');
  assert.doesNotMatch(sql, /FOREIGN KEY \(measure_job_id, measure_routed_to\)/i, 'old current-route attribution binding');
});

test('legacy unknown captures and routed rows remain compatible', () => {
  assert.doesNotMatch(sql, /room_captures\s+JSONB\s+DEFAULT/i);
  assert.match(sql, /current_routing_attempt_id IS NULL OR routed_to IS NOT NULL/i);
  assert.doesNotMatch(sql, /current_routing_attempt_id IS NULL AND routed_to IS NULL/i);
});