ADR-211: Validation Interview Tracking System

Status: Accepted Date: 2026-02-18 Deciders: Hal Casteel, CODITECT Engineering Track: N (GTM Launch)

Context

CODITECT's venture projects (starting with C-of-C-CANADA) require structured validation interviews as part of the customer discovery process. The V.1.2 Tracking Sheet (markdown-based) provides a static template, but a proper system is needed to:

Track interviews across multiple projects — C-of-C-CANADA, future ventures, and CODITECT's own product validation
Capture structured data — interview responses, scores, hypotheses validation, follow-up actions
Support multiple users/teams — different interviewers tracking their own conversations
Enable analytics — aggregate validation metrics, go/no-go thresholds, hypothesis confirmation rates
Scale to a product feature — eventually offered as part of CODITECT's GTM strategy suite for customers

The system must support a phased approach: rapid local development first, then cloud deployment for multi-tenant usage.

Decision

Build a Validation Interview Tracking System as a reusable component in coditect-gtm-strategy with two phases:

Phase 1: Local SQLite + TypeScript UI

Database: SQLite with strict schema, local file-based storage
Language: TypeScript strict mode (no any, no implicit types)
UI: React/Vite local development server
Scope: Single-user, multi-project tracking
Data Model: Projects, Contacts, Interviews, Responses, Hypotheses, Scores, Actions
CLI: Optional command-line interface for quick data entry

Phase 2: Cloud Multi-Tenant (Future)

Database: PostgreSQL with row-level security (RLS) for tenant isolation
Auth: CODITECT SSO (OAuth2/JWT) — reuse existing auth infrastructure
Multi-tenant: Organization → Team → User hierarchy with project-scoped data
API: RESTful API (Django or FastAPI) behind CODITECT API gateway
Real-time: WebSocket updates for team collaboration
Analytics: Aggregate dashboards per project, per venture, cross-venture

Data Model

Core Entities

┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│   Project    │────<│   Contact    │────<│  Interview   │
│              │     │              │     │              │
│ id (PK)      │     │ id (PK)      │     │ id (PK)      │
│ name         │     │ project_id   │     │ contact_id   │
│ description  │     │ org_name     │     │ interviewer  │
│ venture_id   │     │ org_type     │     │ scheduled_at │
│ status       │     │ contact_name │     │ completed_at │
│ created_at   │     │ title        │     │ status       │
│ updated_at   │     │ email        │     │ duration_min │
└─────────────┘     │ phone        │     │ notes        │
                    │ city         │     │ recording_url│
                    │ state_prov   │     │ overall_score│
                    │ country      │     └──────────────┘
                    │ member_count │            │
                    │ staff_count  │            │
                    │ annual_budget│     ┌──────┴───────┐
                    │ ams_platform │     │              │
                    │ status       │     ▼              ▼
                    │ source       │ ┌──────────┐ ┌──────────────┐
                    │ referral_from│ │ Response │ │ HypothesisVal│
                    └──────────────┘ │          │ │              │
                                     │ id (PK)  │ │ id (PK)      │
                                     │ interview│ │ interview_id │
                                     │ question │ │ hypothesis_id│
                                     │ section  │ │ confirmed    │
                                     │ answer   │ │ evidence     │
                                     │ score    │ │ confidence   │
                                     │ notes    │ │ notes        │
                                     └──────────┘ └──────────────┘
                                           │
                                     ┌─────┴──────┐
                                     │            │
                                     ▼            ▼
                              ┌──────────┐ ┌───────────┐
                              │  Action  │ │ Dimension │
                              │          │ │   Score   │
                              │ id (PK)  │ │           │
                              │ interview│ │ id (PK)   │
                              │ type     │ │ interview │
                              │ desc     │ │ dimension │
                              │ assignee │ │ score     │
                              │ due_date │ │ notes     │
                              │ status   │ └───────────┘
                              │ completed│
                              └──────────┘

Supporting Entities

Entity	Purpose
Hypothesis	Reusable hypothesis definitions per project (e.g., "80%+ pain confirmation")
InterviewTemplate	Section/question definitions (reusable across projects)
Outreach	Pre-interview outreach tracking (emails sent, responses, scheduling)
Referral	Referral chain tracking (who referred whom)

Schema Design Principles

Project-scoped everything — All data belongs to a project; no orphaned records
Audit trail — created_at, updated_at, created_by on all tables
Soft delete — deleted_at column instead of physical deletion
Enum as text — Status fields stored as text for SQLite compatibility, validated at app layer
JSON columns for flexibility — metadata JSONB for extensible attributes without schema changes
Score normalization — All scores on 1-5 scale with decimal precision

TypeScript Type System

// Strict mode: no any, no implicit types
// All types exported from types/index.ts

interface Project {
  id: string;           // UUID
  name: string;
  description: string;
  ventureId: string;    // Links to CODITECT venture
  status: ProjectStatus;
  hypotheses: Hypothesis[];
  template: InterviewTemplate;
  createdAt: Date;
  updatedAt: Date;
}

type ProjectStatus = 'planning' | 'active' | 'paused' | 'completed' | 'archived';
type ContactStatus = 'prospect' | 'contacted' | 'responded' | 'scheduled' | 'completed' | 'declined' | 'referred' | 'follow_up';
type InterviewStatus = 'scheduled' | 'in_progress' | 'completed' | 'cancelled' | 'no_show';
type ActionStatus = 'pending' | 'in_progress' | 'completed' | 'cancelled';
type ActionType = 'follow_up' | 'demo' | 'proposal' | 'referral' | 'internal' | 'escalation';

Phase 1 Technology Stack

Component	Technology	Rationale
Database	SQLite (better-sqlite3)	Zero-config, file-based, portable
ORM	Drizzle ORM	TypeScript-first, SQLite support, strict types
UI Framework	React 18 + Vite	Fast development, CODITECT standard
Styling	Tailwind CSS	CODITECT design system compatible
State	Zustand	Lightweight, TypeScript-first
Forms	React Hook Form + Zod	Type-safe validation
Charts	Recharts	Lightweight, React-native
Export	xlsx + jsPDF	Offline report generation

Phase 2 Migration Path

Phase 1 (Local)	Phase 2 (Cloud)	Migration
SQLite	PostgreSQL + RLS	Drizzle handles dialect switch
File-based auth	CODITECT SSO (JWT)	Add auth middleware
Single user	Multi-user/team/tenant	Add `tenant_id`, `user_id` columns
Local storage	GCS/S3 for recordings	Add storage service abstraction
No API	REST API (Django/FastAPI)	Extract service layer
Local reports	Cloud dashboards	Add Grafana/custom dashboards

Multi-Tenant Schema Extension (Phase 2)

-- Added columns for multi-tenancy
ALTER TABLE projects ADD COLUMN tenant_id UUID REFERENCES tenants(id);
ALTER TABLE projects ADD COLUMN team_id UUID REFERENCES teams(id);
ALTER TABLE interviews ADD COLUMN interviewer_id UUID REFERENCES users(id);

-- Row-level security policies
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

Directory Structure

coditect-gtm-strategy/
├── 09-validation-tracking/          # New directory
│   ├── README.md                    # System documentation
│   ├── package.json                 # Dependencies
│   ├── tsconfig.json               # Strict TypeScript config
│   ├── drizzle.config.ts           # Database config
│   ├── src/
│   │   ├── db/
│   │   │   ├── schema.ts           # Drizzle schema definition
│   │   │   ├── migrations/         # Auto-generated migrations
│   │   │   └── seed.ts             # Sample data seeder
│   │   ├── types/
│   │   │   ├── index.ts            # All TypeScript types
│   │   │   ├── project.ts          # Project types
│   │   │   ├── contact.ts          # Contact types
│   │   │   ├── interview.ts        # Interview types
│   │   │   └── analytics.ts        # Analytics types
│   │   ├── services/
│   │   │   ├── project.service.ts  # Project CRUD
│   │   │   ├── contact.service.ts  # Contact CRUD
│   │   │   ├── interview.service.ts # Interview CRUD
│   │   │   └── analytics.service.ts # Aggregation queries
│   │   ├── ui/
│   │   │   ├── App.tsx             # Main app shell
│   │   │   ├── pages/             # Route pages
│   │   │   ├── components/        # Shared components
│   │   │   └── hooks/             # Custom hooks
│   │   └── cli/
│   │       └── index.ts           # Optional CLI interface
│   └── data/
│       └── validation.db          # SQLite database file

Relationship to Existing Systems

System	Relationship
C-of-C-CANADA V.1.2 Tracking Sheet	Static predecessor; this system replaces it with dynamic tracking
C-of-C-CANADA V.1.1 Interview Guide	Question templates imported as `InterviewTemplate` records
CODITECT Feedback System (N.2.5)	Complementary; feedback is post-launch, validation is pre-launch
CODITECT GTM Strategy	Parent product; this is a module within the GTM toolkit
CODITECT Product Suite	Phase 2 — offered as a feature for CODITECT customers doing their own validation

Consequences

Positive

Reusable — any CODITECT venture can use the same system
Data-driven go/no-go — aggregate scores and hypothesis validation rates drive decisions
Product potential — becomes a revenue feature for CODITECT customers
TypeScript strict — catches data model issues at compile time
SQLite portable — works offline, no server dependencies for Phase 1

Negative

Phase 1 single-user — no concurrent access in SQLite mode
Migration effort — Phase 1 → Phase 2 requires schema migration tooling
Additional maintenance — new codebase in gtm-strategy submodule

Risks

SQLite → PostgreSQL drift — mitigated by Drizzle ORM's dialect abstraction
Over-engineering Phase 1 — mitigated by strict Phase 1 scope (local only)
Template rigidity — mitigated by JSON metadata fields for extensibility

ADR	Relationship
ADR-004	Beachhead Market — defines ICP criteria used in contact scoring
ADR-005	Pricing Strategy — informs budget/pricing interview questions
ADR-055	Container Sessions — Phase 2 may use container-based analytics
ADR-118	Database Architecture — Phase 2 follows CODITECT DB patterns
ADR-155	Project-Scoped Session Logs — same project-scoping pattern

Document Control:

Created: 2026-02-18
Author: CODITECT Engineering Team
Review: Before Phase 1 implementation

Context​

Decision​

Phase 1: Local SQLite + TypeScript UI​

Phase 2: Cloud Multi-Tenant (Future)​

Data Model​

Core Entities​

Supporting Entities​

Schema Design Principles​

TypeScript Type System​

Phase 1 Technology Stack​

Phase 2 Migration Path​

Multi-Tenant Schema Extension (Phase 2)​

Directory Structure​

Relationship to Existing Systems​

Consequences​

Positive​

Negative​

Risks​

Related ADRs​