← back to NationalPaperHangers Yfr2 C3
add local measure attribution phase a
42fc3d778ee3abd7c73df2dcdb328867f53defa5 · 2026-08-29 12:51:22 -0700 · Steve Abrams
Files touched
A db/migrations/024_measure_attribution_phase_a.sqlA lib/domain/measure-tier.jsA tests/measure-attribution-sql.test.jsA tests/measure-tier.test.jsA verification/e2e-proof.json
Diff
commit 42fc3d778ee3abd7c73df2dcdb328867f53defa5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Aug 29 12:51:22 2026 -0700
add local measure attribution phase a
---
db/migrations/024_measure_attribution_phase_a.sql | 223 ++++++++++++++++++++++
lib/domain/measure-tier.js | 116 +++++++++++
tests/measure-attribution-sql.test.js | 61 ++++++
tests/measure-tier.test.js | 164 ++++++++++++++++
verification/e2e-proof.json | 76 ++++++++
5 files changed, 640 insertions(+)
diff --git a/db/migrations/024_measure_attribution_phase_a.sql b/db/migrations/024_measure_attribution_phase_a.sql
new file mode 100644
index 0000000..7e77fcf
--- /dev/null
+++ b/db/migrations/024_measure_attribution_phase_a.sql
@@ -0,0 +1,223 @@
+-- 024 · Measure attribution Phase A structural contract
+--
+-- ADDITIVE / DORMANT ONLY. This migration defines storage and integrity
+-- boundaries; it does not add API routes, enable feature flags, send messages,
+-- create prices, backfill attribution, or alter legacy measure-job outcomes.
+
+BEGIN;
+
+ALTER TABLE measure_jobs
+ ADD COLUMN IF NOT EXISTS uuid UUID,
+ ADD COLUMN IF NOT EXISTS capture_idempotency_key TEXT,
+ ADD COLUMN IF NOT EXISTS captured_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS capture_source TEXT,
+ ADD COLUMN IF NOT EXISTS environment TEXT,
+ ADD COLUMN IF NOT EXISTS is_test BOOLEAN,
+ ADD COLUMN IF NOT EXISTS test_reason TEXT,
+ ADD COLUMN IF NOT EXISTS consent_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS consent_version TEXT,
+ ADD COLUMN IF NOT EXISTS consent_purposes TEXT[],
+ ADD COLUMN IF NOT EXISTS contact_preference TEXT,
+ ADD COLUMN IF NOT EXISTS measurement_method TEXT,
+ ADD COLUMN IF NOT EXISTS room_captures JSONB DEFAULT '[]'::jsonb,
+ ADD COLUMN IF NOT EXISTS surface_state TEXT,
+ ADD COLUMN IF NOT EXISTS access_constraints TEXT[],
+ ADD COLUMN IF NOT EXISTS timeline_band TEXT,
+ ADD COLUMN IF NOT EXISTS lead_state TEXT,
+ ADD COLUMN IF NOT EXISTS responded_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS declined_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS expired_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS tier_snapshot TEXT,
+ ADD COLUMN IF NOT EXISTS tier_reason_codes TEXT[],
+ ADD COLUMN IF NOT EXISTS tier_policy_version TEXT,
+ ADD COLUMN IF NOT EXISTS retention_class TEXT,
+ ADD COLUMN IF NOT EXISTS subject_erased_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS current_routing_attempt_id BIGINT;
+
+CREATE UNIQUE INDEX IF NOT EXISTS measure_jobs_uuid_uq
+ ON measure_jobs (uuid) WHERE uuid IS NOT NULL;
+CREATE UNIQUE INDEX IF NOT EXISTS measure_jobs_capture_idempotency_uq
+ ON measure_jobs (capture_idempotency_key) WHERE capture_idempotency_key IS NOT NULL;
+
+ALTER TABLE measure_jobs
+ ADD CONSTRAINT measure_jobs_capture_source_ck CHECK (
+ capture_source IS NULL OR capture_source IN ('measure_web','booking_handoff','partner','admin_test')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_environment_ck CHECK (
+ environment IS NULL OR environment IN ('production','staging','development','test')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_contact_preference_ck CHECK (
+ contact_preference IS NULL OR contact_preference IN ('email','phone','either')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_measurement_method_ck CHECK (
+ measurement_method IS NULL OR measurement_method IN ('manual','calculator','camera','installer')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_lead_state_ck CHECK (
+ lead_state IS NULL OR lead_state IN ('captured','no_installer','routed','viewed','accepted','declined','expired')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_tier_snapshot_ck CHECK (
+ tier_snapshot IS NULL OR tier_snapshot IN ('standard','spec_ready','ineligible')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_retention_class_ck CHECK (
+ retention_class IS NULL OR retention_class IN ('test','unconverted','converted','legal_hold')
+ ) NOT VALID,
+ ADD CONSTRAINT measure_jobs_room_captures_array_ck CHECK (
+ room_captures IS NULL OR jsonb_typeof(room_captures) = 'array'
+ ) NOT VALID;
+
+CREATE TABLE measure_job_routing_attempts (
+ id BIGSERIAL PRIMARY KEY,
+ measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
+ attempt_no INTEGER NOT NULL CHECK (attempt_no > 0),
+ installer_id BIGINT NOT NULL REFERENCES installers(id) ON DELETE RESTRICT,
+ state TEXT NOT NULL CHECK (state IN ('routed','viewed','accepted','declined','expired','superseded')),
+ routed_at TIMESTAMPTZ NOT NULL,
+ viewed_at TIMESTAMPTZ,
+ responded_at TIMESTAMPTZ,
+ accepted_at TIMESTAMPTZ,
+ declined_at TIMESTAMPTZ,
+ expired_at TIMESTAMPTZ,
+ superseded_at TIMESTAMPTZ,
+ supersedes_job_id BIGINT,
+ supersedes_attempt_id BIGINT,
+ correlation_id UUID NOT NULL UNIQUE,
+ created_by TEXT NOT NULL,
+ UNIQUE (measure_job_id, attempt_no),
+ UNIQUE (id, measure_job_id, installer_id),
+ UNIQUE (measure_job_id, id),
+ CHECK (
+ (attempt_no = 1 AND supersedes_attempt_id IS NULL AND supersedes_job_id IS NULL)
+ OR (attempt_no > 1 AND supersedes_attempt_id IS NOT NULL
+ AND supersedes_job_id IS NOT NULL AND supersedes_job_id = measure_job_id
+ AND supersedes_attempt_id <> id)
+ )
+);
+
+ALTER TABLE measure_job_routing_attempts
+ ADD CONSTRAINT routing_attempt_supersedes_same_job_fk
+ FOREIGN KEY (supersedes_job_id, supersedes_attempt_id)
+ REFERENCES measure_job_routing_attempts (measure_job_id, id)
+ MATCH FULL ON DELETE RESTRICT;
+
+CREATE UNIQUE INDEX routing_attempt_one_successor_idx
+ ON measure_job_routing_attempts (supersedes_attempt_id)
+ WHERE supersedes_attempt_id IS NOT NULL;
+CREATE UNIQUE INDEX routing_attempt_one_root_per_job_idx
+ ON measure_job_routing_attempts (measure_job_id)
+ WHERE supersedes_attempt_id IS NULL;
+CREATE UNIQUE INDEX routing_attempt_one_active_per_job_idx
+ ON measure_job_routing_attempts (measure_job_id)
+ WHERE state IN ('routed','viewed','accepted');
+
+ALTER TABLE measure_jobs
+ ADD CONSTRAINT measure_jobs_current_route_fk
+ FOREIGN KEY (current_routing_attempt_id, id, routed_to)
+ REFERENCES measure_job_routing_attempts (id, measure_job_id, installer_id)
+ ON DELETE RESTRICT,
+ ADD CONSTRAINT measure_jobs_current_route_pair_ck CHECK (
+ (current_routing_attempt_id IS NULL AND routed_to IS NULL)
+ OR (current_routing_attempt_id IS NOT NULL AND routed_to IS NOT NULL)
+ ) NOT VALID;
+
+ALTER TABLE bookings
+ ADD CONSTRAINT bookings_id_installer_uq UNIQUE (id, installer_id);
+
+CREATE TABLE measure_booking_attribution_candidates (
+ id BIGSERIAL PRIMARY KEY,
+ booking_id INTEGER NOT NULL,
+ measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
+ routing_attempt_id BIGINT NOT NULL,
+ installer_id BIGINT NOT NULL,
+ attribution_method TEXT NOT NULL CHECK (attribution_method IN ('signed_handoff','admin_verified')),
+ policy_version TEXT NOT NULL,
+ qualified_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ correlation_id UUID NOT NULL UNIQUE,
+ created_by TEXT NOT NULL,
+ UNIQUE (booking_id, id),
+ UNIQUE (booking_id, routing_attempt_id, attribution_method, policy_version),
+ FOREIGN KEY (booking_id, installer_id)
+ REFERENCES bookings (id, installer_id) ON DELETE RESTRICT,
+ FOREIGN KEY (routing_attempt_id, measure_job_id, installer_id)
+ REFERENCES measure_job_routing_attempts (id, measure_job_id, installer_id)
+ ON DELETE RESTRICT
+);
+
+CREATE TABLE measure_booking_primary_decisions (
+ id BIGSERIAL PRIMARY KEY,
+ booking_id INTEGER NOT NULL REFERENCES bookings(id) ON DELETE RESTRICT,
+ decision_kind TEXT NOT NULL CHECK (decision_kind IN ('select','clear')),
+ selected_booking_id INTEGER,
+ selected_candidate_id BIGINT,
+ supersedes_booking_id INTEGER,
+ supersedes_decision_id BIGINT,
+ decided_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ correlation_id UUID NOT NULL UNIQUE,
+ policy_version TEXT NOT NULL,
+ reason_code TEXT NOT NULL,
+ decided_by TEXT NOT NULL,
+ UNIQUE (booking_id, id),
+ CHECK (
+ (decision_kind = 'select' AND selected_booking_id IS NOT NULL
+ AND selected_booking_id = booking_id AND selected_candidate_id IS NOT NULL)
+ OR (decision_kind = 'clear' AND selected_booking_id IS NULL AND selected_candidate_id IS NULL)
+ ),
+ CHECK (
+ (supersedes_decision_id IS NULL AND supersedes_booking_id IS NULL
+ AND decision_kind = 'select')
+ OR (supersedes_decision_id IS NOT NULL AND supersedes_booking_id IS NOT NULL
+ AND supersedes_booking_id = booking_id AND supersedes_decision_id <> id)
+ ),
+ CHECK (decision_kind <> 'clear' OR supersedes_decision_id IS NOT NULL),
+ FOREIGN KEY (selected_booking_id, selected_candidate_id)
+ REFERENCES measure_booking_attribution_candidates (booking_id, id)
+ MATCH FULL ON DELETE RESTRICT,
+ FOREIGN KEY (supersedes_booking_id, supersedes_decision_id)
+ REFERENCES measure_booking_primary_decisions (booking_id, id)
+ MATCH FULL ON DELETE RESTRICT
+);
+
+CREATE UNIQUE INDEX primary_decision_one_root_per_booking_idx
+ ON measure_booking_primary_decisions (booking_id)
+ WHERE supersedes_decision_id IS NULL;
+CREATE UNIQUE INDEX primary_decision_one_successor_idx
+ ON measure_booking_primary_decisions (supersedes_decision_id)
+ WHERE supersedes_decision_id IS NOT NULL;
+
+CREATE TABLE measure_booking_primary_current (
+ booking_id INTEGER PRIMARY KEY REFERENCES bookings(id) ON DELETE RESTRICT,
+ decision_id BIGINT NOT NULL,
+ version BIGINT NOT NULL CHECK (version > 0),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ FOREIGN KEY (booking_id, decision_id)
+ REFERENCES measure_booking_primary_decisions (booking_id, id) ON DELETE RESTRICT
+);
+
+CREATE TABLE measure_job_events (
+ id BIGSERIAL PRIMARY KEY,
+ measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
+ event_type TEXT NOT NULL,
+ occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ actor_type TEXT NOT NULL,
+ installer_id BIGINT REFERENCES installers(id) ON DELETE RESTRICT,
+ booking_id INTEGER REFERENCES bookings(id) ON DELETE RESTRICT,
+ correlation_id UUID NOT NULL,
+ source_system TEXT,
+ source_event_id TEXT,
+ schema_version TEXT NOT NULL,
+ metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
+ CHECK (jsonb_typeof(metadata) = 'object'),
+ CHECK (
+ (source_event_id IS NULL AND source_system IS NULL)
+ OR (source_event_id IS NOT NULL AND source_system IS NOT NULL)
+ )
+);
+
+CREATE UNIQUE INDEX measure_job_events_source_idempotency_idx
+ ON measure_job_events (source_system, source_event_id)
+ WHERE source_event_id IS NOT NULL;
+CREATE UNIQUE INDEX measure_job_events_internal_idempotency_idx
+ ON measure_job_events (measure_job_id, event_type, correlation_id)
+ WHERE source_event_id IS NULL;
+
+COMMIT;
diff --git a/lib/domain/measure-tier.js b/lib/domain/measure-tier.js
new file mode 100644
index 0000000..d56b1a5
--- /dev/null
+++ b/lib/domain/measure-tier.js
@@ -0,0 +1,116 @@
+'use strict';
+
+const POLICY_VERSION = 'measure_tier_v1';
+const ELIGIBLE_STATES = new Set(['routed', 'viewed', 'accepted', 'declined', 'expired']);
+const MEASUREMENT_METHODS = new Set(['manual', 'calculator', 'camera', 'installer']);
+const CONTACT_PREFERENCES = new Set(['email', 'phone', 'either']);
+const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
+const PHONE_RE = /^\+?[1-9]\d{7,14}$/;
+const ZIP_RE = /^\d{5}$/;
+const CAPTURE_PHOTO_RE = /^\/uploads\/bookings\/[0-9a-f]{24}\.(jpg|png|webp|avif|heic|heif)$/;
+
+function text(value) {
+ return typeof value === 'string' && value.trim() ? value.trim() : null;
+}
+
+function positive(value) {
+ const number = typeof value === 'number' ? value : Number(value);
+ return Number.isFinite(number) && number > 0;
+}
+
+function validDate(value) {
+ if (!value) return false;
+ const date = value instanceof Date ? value : new Date(value);
+ return Number.isFinite(date.getTime());
+}
+
+function validEmail(value) {
+ const normalized = text(value);
+ return normalized !== null && EMAIL_RE.test(normalized);
+}
+
+function validPhone(value) {
+ const normalized = text(value);
+ return normalized !== null && PHONE_RE.test(normalized);
+}
+
+function validRoomCapture(capture) {
+ return capture !== null && typeof capture === 'object' && !Array.isArray(capture)
+ && typeof capture.photo_url === 'string' && CAPTURE_PHOTO_RE.test(capture.photo_url)
+ && positive(capture.wall_width_ft) && positive(capture.wall_height_ft);
+}
+
+/**
+ * Pure, versioned tier predicate. It reads only explicit stored-field values;
+ * it never parses notes or infers consent, test status, contact, or product data.
+ *
+ * @returns {{tier: 'ineligible'|'standard'|'spec_ready', reason_codes: string[], policy_version: string}}
+ */
+function classifyMeasureTier(job = {}) {
+ const reasons = [];
+ const fail = (condition, code) => {
+ if (!condition) reasons.push(code);
+ return condition;
+ };
+
+ const production = fail(job.environment === 'production', 'not_production');
+ const nonTest = fail(job.is_test === false, job.is_test === true ? 'test_record' : 'missing_test_provenance');
+ const captured = fail(validDate(job.captured_at), 'missing_captured_at');
+ const consentedAt = fail(validDate(job.consent_at), 'missing_consent_at');
+ const consentVersion = fail(text(job.consent_version) !== null, 'missing_consent_version');
+ const purposes = Array.isArray(job.consent_purposes) ? job.consent_purposes : [];
+ const routingConsent = fail(purposes.includes('installer_routing'), 'missing_installer_routing_consent');
+ const validZip = fail(ZIP_RE.test(text(job.zip) || ''), 'invalid_or_missing_zip');
+ const routedTo = fail(Number.isSafeInteger(Number(job.routed_to)) && Number(job.routed_to) > 0, 'missing_routed_to');
+ const routedAt = fail(validDate(job.routed_at), 'missing_routed_at');
+ const eligibleState = fail(ELIGIBLE_STATES.has(job.lead_state), 'ineligible_lead_state');
+
+ const preference = CONTACT_PREFERENCES.has(job.contact_preference) ? job.contact_preference : null;
+ if (!preference) reasons.push('missing_or_invalid_contact_preference');
+ const emailOkay = validEmail(job.customer_email);
+ const phoneOkay = validPhone(job.customer_phone);
+ const contactOkay = preference === 'email' ? emailOkay
+ : preference === 'phone' ? phoneOkay
+ : preference === 'either' ? (emailOkay || phoneOkay) : false;
+ if (!contactOkay) reasons.push('missing_valid_preferred_contact');
+
+ const hasSqft = positive(job.sqft);
+ const hasRolls = positive(job.rolls);
+ if (!hasSqft && !hasRolls) reasons.push('missing_positive_scope');
+ const measurementMethod = MEASUREMENT_METHODS.has(job.measurement_method);
+ if (!measurementMethod) reasons.push('missing_or_invalid_measurement_method');
+
+ const sharedEligible = production && nonTest && captured && consentedAt && consentVersion
+ && routingConsent && validZip && routedTo && routedAt && eligibleState && preference && contactOkay;
+ const standardEligible = sharedEligible && (hasSqft || hasRolls) && measurementMethod;
+
+ if (!standardEligible) {
+ return { tier: 'ineligible', reason_codes: reasons.sort(), policy_version: POLICY_VERSION };
+ }
+
+ if (hasSqft) reasons.push('has_sqft'); else reasons.push('spec_missing_sqft');
+ if (hasRolls) reasons.push('has_rolls'); else reasons.push('spec_missing_rolls');
+
+ const hasWallCount = positive(job.wall_count);
+ if (hasWallCount) reasons.push('has_wall_count'); else reasons.push('spec_missing_wall_count');
+ const hasProductContext = text(job.product_sku) !== null || text(job.material) !== null;
+ if (hasProductContext) reasons.push('has_product_or_material'); else reasons.push('spec_missing_product_or_material');
+ const hasTimeline = text(job.timeline_band) !== null;
+ if (hasTimeline) reasons.push('has_timeline'); else reasons.push('spec_missing_timeline');
+ const hasSurface = text(job.surface_state) !== null;
+ if (hasSurface) reasons.push('has_surface_state'); else reasons.push('spec_missing_surface_state');
+ const captures = Array.isArray(job.room_captures) ? job.room_captures : [];
+ const hasValidCapture = captures.length > 0 && captures.every(validRoomCapture);
+ if (hasValidCapture) reasons.push('has_valid_room_capture'); else reasons.push('spec_missing_valid_room_capture');
+
+ const specReady = hasSqft && hasRolls && hasWallCount && hasProductContext
+ && hasTimeline && hasSurface && hasValidCapture;
+
+ return {
+ tier: specReady ? 'spec_ready' : 'standard',
+ reason_codes: reasons.sort(),
+ policy_version: POLICY_VERSION
+ };
+}
+
+module.exports = { POLICY_VERSION, classifyMeasureTier };
diff --git a/tests/measure-attribution-sql.test.js b/tests/measure-attribution-sql.test.js
new file mode 100644
index 0000000..8ac4ec2
--- /dev/null
+++ b/tests/measure-attribution-sql.test.js
@@ -0,0 +1,61 @@
+'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');
+});
diff --git a/tests/measure-tier.test.js b/tests/measure-tier.test.js
new file mode 100644
index 0000000..ee0b798
--- /dev/null
+++ b/tests/measure-tier.test.js
@@ -0,0 +1,164 @@
+'use strict';
+
+const test = require('node:test');
+const assert = require('node:assert/strict');
+const { POLICY_VERSION, classifyMeasureTier } = require('../lib/domain/measure-tier');
+
+function base(overrides = {}) {
+ return {
+ environment: 'production',
+ is_test: false,
+ captured_at: '2026-08-01T12:00:00Z',
+ consent_at: '2026-08-01T12:01:00Z',
+ consent_version: 'measure-routing-v1',
+ consent_purposes: ['installer_routing'],
+ zip: '33138',
+ routed_to: 42,
+ routed_at: '2026-08-01T12:02:00Z',
+ lead_state: 'routed',
+ contact_preference: 'email',
+ customer_email: 'buyer@example.test',
+ customer_phone: null,
+ sqft: 240,
+ rolls: null,
+ measurement_method: 'calculator',
+ ...overrides
+ };
+}
+
+test('returns the stable policy version and Standard for minimum explicit eligibility', () => {
+ const result = classifyMeasureTier(base());
+ assert.equal(result.policy_version, POLICY_VERSION);
+ assert.equal(result.tier, 'standard');
+ assert.deepEqual(result.reason_codes, [
+ 'has_sqft',
+ 'spec_missing_product_or_material',
+ 'spec_missing_rolls',
+ 'spec_missing_surface_state',
+ 'spec_missing_timeline',
+ 'spec_missing_valid_room_capture',
+ 'spec_missing_wall_count'
+ ]);
+});
+
+test('classifies Specification-ready only when every structured predicate passes', () => {
+ const result = classifyMeasureTier(base({
+ rolls: 8,
+ wall_count: 4,
+ product_sku: 'DW-100',
+ timeline_band: 'within_90_days',
+ surface_state: 'painted_drywall',
+ room_captures: [{
+ wall_width_ft: 12,
+ wall_height_ft: 9,
+ photo_url: '/uploads/bookings/0123456789abcdef01234567.jpg'
+ }]
+ }));
+ assert.equal(result.tier, 'spec_ready');
+ assert.deepEqual(result.reason_codes, [
+ 'has_product_or_material', 'has_rolls', 'has_sqft', 'has_surface_state',
+ 'has_timeline', 'has_valid_room_capture', 'has_wall_count'
+ ]);
+});
+
+test('product_name and notes never satisfy structured Specification-ready fields', () => {
+ const result = classifyMeasureTier(base({
+ rolls: 8,
+ wall_count: 4,
+ product_name: 'Marketing name only',
+ notes: 'painted wall, install next week, photos attached',
+ room_captures: [{
+ wall_width_ft: 12,
+ wall_height_ft: 9,
+ photo_url: '/uploads/bookings/0123456789abcdef01234567.jpg'
+ }]
+ }));
+ assert.equal(result.tier, 'standard');
+ assert.ok(result.reason_codes.includes('spec_missing_product_or_material'));
+ assert.ok(result.reason_codes.includes('spec_missing_surface_state'));
+ assert.ok(result.reason_codes.includes('spec_missing_timeline'));
+});
+
+test('explicit test and non-production records are excluded', () => {
+ assert.deepEqual(classifyMeasureTier(base({ is_test: true })).tier, 'ineligible');
+ assert.ok(classifyMeasureTier(base({ is_test: true })).reason_codes.includes('test_record'));
+ assert.ok(classifyMeasureTier(base({ environment: 'staging' })).reason_codes.includes('not_production'));
+ assert.ok(classifyMeasureTier(base({ is_test: undefined })).reason_codes.includes('missing_test_provenance'));
+});
+
+test('missing consent, routing, contact, and scope are reported without inference', () => {
+ const result = classifyMeasureTier(base({
+ consent_at: null,
+ consent_version: null,
+ consent_purposes: [],
+ routed_to: null,
+ routed_at: null,
+ lead_state: 'captured',
+ contact_preference: 'email',
+ customer_email: null,
+ sqft: null,
+ rolls: 0,
+ measurement_method: null
+ }));
+ assert.equal(result.tier, 'ineligible');
+ assert.deepEqual(result.reason_codes, [
+ 'ineligible_lead_state',
+ 'missing_consent_at',
+ 'missing_consent_version',
+ 'missing_installer_routing_consent',
+ 'missing_or_invalid_measurement_method',
+ 'missing_positive_scope',
+ 'missing_routed_at',
+ 'missing_routed_to',
+ 'missing_valid_preferred_contact'
+ ]);
+});
+
+test('contact preference is enforced exactly', () => {
+ assert.equal(classifyMeasureTier(base({ contact_preference: 'phone', customer_email: 'buyer@example.test', customer_phone: null })).tier, 'ineligible');
+ assert.equal(classifyMeasureTier(base({ contact_preference: 'either', customer_email: null, customer_phone: '+13055550123' })).tier, 'standard');
+ assert.ok(classifyMeasureTier(base({ contact_preference: null })).reason_codes.includes('missing_or_invalid_contact_preference'));
+});
+
+test('all eligible lead states classify and terminal/no-route states do not', () => {
+ for (const lead_state of ['routed', 'viewed', 'accepted', 'declined', 'expired']) {
+ assert.equal(classifyMeasureTier(base({ lead_state })).tier, 'standard', lead_state);
+ }
+ for (const lead_state of ['captured', 'no_installer', 'superseded', null]) {
+ assert.equal(classifyMeasureTier(base({ lead_state })).tier, 'ineligible', String(lead_state));
+ }
+});
+
+test('malformed or partial room captures cannot qualify as Specification-ready', () => {
+ const common = {
+ rolls: 8, wall_count: 4, material: 'grasscloth', timeline_band: 'within_90_days',
+ surface_state: 'painted_drywall'
+ };
+ for (const room_captures of [
+ null,
+ [],
+ [{}],
+ [{ wall_width_ft: 12 }],
+ [{ wall_width_ft: 12, wall_height_ft: 9 }],
+ [{ wall_width_ft: 12, wall_height_ft: 9, photo_url: 'https://attacker.test/photo.jpg' }],
+ [{ wall_width_ft: 12, wall_height_ft: 0, photo_url: '/uploads/bookings/0123456789abcdef01234567.jpg' }]
+ ]) {
+ const result = classifyMeasureTier(base({ ...common, room_captures }));
+ assert.equal(result.tier, 'standard');
+ assert.ok(result.reason_codes.includes('spec_missing_valid_room_capture'));
+ }
+});
+
+test('invalid ZIP, date, method, and preferred-contact formats are ineligible', () => {
+ const cases = [
+ ['zip', '33138-1234', 'invalid_or_missing_zip'],
+ ['captured_at', 'not-a-date', 'missing_captured_at'],
+ ['measurement_method', 'guessed_from_notes', 'missing_or_invalid_measurement_method'],
+ ['customer_email', 'not-an-email', 'missing_valid_preferred_contact']
+ ];
+ for (const [field, value, reason] of cases) {
+ const result = classifyMeasureTier(base({ [field]: value }));
+ assert.equal(result.tier, 'ineligible', field);
+ assert.ok(result.reason_codes.includes(reason), field);
+ }
+});
diff --git a/verification/e2e-proof.json b/verification/e2e-proof.json
new file mode 100644
index 0000000..f83aca3
--- /dev/null
+++ b/verification/e2e-proof.json
@@ -0,0 +1,76 @@
+{
+ "intent": "Implement and verify the first local-only NPH measure-attribution Phase-A slice: pure tier predicates plus an unapplied additive structural migration.",
+ "risk_tier": "R1",
+ "environment": "isolated git worktree; no database connection, runtime service, provider, or customer surface",
+ "build_identity": {
+ "branch": "yfr2-c3-nph-attribution",
+ "parent_commit": "b672493a7a58cc4e84ddd55e41fca84a13521033",
+ "ticket": "TK-10960-nph-local-phase-a-attribution-schema-and"
+ },
+ "timestamp": "2026-08-29T19:51:00-07:00",
+ "baseline": "Worktree was clean at the requested parent. The primary NationalPaperHangers checkout was out of scope and was not accessed for writes.",
+ "commands": [
+ {
+ "command": "node --test tests/measure-tier.test.js tests/measure-attribution-sql.test.js",
+ "result": "PASS: 15 tests, 0 failures"
+ },
+ {
+ "command": "node --check lib/domain/measure-tier.js",
+ "result": "PASS"
+ },
+ {
+ "command": "node --test tests/measure-kit.test.js tests/measure-tier.test.js tests/measure-attribution-sql.test.js",
+ "result": "PASS: 29 tests, 0 failures"
+ },
+ {
+ "command": "npm test",
+ "result": "BLOCKED BY ENVIRONMENT: existing dependency-backed tests could not load supertest because this isolated worktree has no node_modules. The dependency-free existing measure-kit suite passed."
+ },
+ {
+ "command": "npm ci --offline --ignore-scripts",
+ "result": "NO INSTALL: offline npm cache lacked xtend-4.0.2; no network fallback was attempted and node_modules remains absent"
+ },
+ {
+ "command": "git diff --check",
+ "result": "PASS"
+ }
+ ],
+ "assertions": [
+ {
+ "boundary": "tier-domain",
+ "verdict": "PASS",
+ "evidence": "Versioned pure predicate returns ineligible, standard, or spec_ready from explicit fields; tests cover consent, provenance, test exclusion, routing, preferred contact, scope, structured specification completeness, malformed captures, and forbidden free-text inference."
+ },
+ {
+ "boundary": "migration-contract",
+ "verdict": "PASS",
+ "evidence": "Static verifier proves additive transaction shape, routing root/successor rules, exact booking/attempt installer FKs, append-only candidate/decision/current model, provider-scoped event idempotency, and absence of known invalid partial-UNIQUE and superseded mutable-primary shapes."
+ },
+ {
+ "boundary": "database-side-effect",
+ "verdict": "PASS",
+ "evidence": "Migration was read only as a file and never passed to psql or any database client. No DB query or apply occurred."
+ },
+ {
+ "boundary": "runtime-and-external-side-effect",
+ "verdict": "PASS",
+ "evidence": "No API route, service, feature flag, deploy, restart, email, Stripe/provider call, or customer-facing surface was exercised or changed."
+ },
+ {
+ "boundary": "broader-existing-suite",
+ "verdict": "SKIP",
+ "reason": "Three existing suites require supertest, which is not installed in the isolated worktree. Network dependency installation was outside this task's external-call gate. Existing dependency-free measure-kit tests passed 14/14. This does not skip the changed critical path, which is covered by 15/15 new tests."
+ }
+ ],
+ "negative_checks": [
+ "test/non-production records cannot qualify",
+ "missing consent/contact/routing/scope cannot be inferred",
+ "product_name and notes cannot satisfy specification fields",
+ "malformed room captures cannot produce spec_ready",
+ "inline partial UNIQUE and mutable touch_kind primary shapes are rejected",
+ "migration text contains no INSERT, UPDATE, DELETE, TRUNCATE, destructive DROP, trigger, or feature activation"
+ ],
+ "cleanup": "No database fixtures or runtime state were created. Failed offline npm installation left no node_modules directory. Source changes are retained in the isolated worktree for review and commit.",
+ "overall_verdict": "PASS_WITH_ENVIRONMENTAL_SUITE_LIMIT",
+ "residual_risk": "SQL integrity is statically verified but not parsed/executed against a disposable PostgreSQL instance in this bounded slice. Full dependency-backed existing tests should be rerun in an environment with the locked dependencies available before integration."
+}
← b672493 chore: add TK-10428 nph measure-fix deploy scripts (HTTP-01
·
back to NationalPaperHangers Yfr2 C3
·
harden measure attribution migration proof b9ca758 →