Data Model

Derived from the end-to-end funnel (Research → Campaign assignment → Outreach → Connect → Closing). The names below are the ubiquitous language and must be kept in code.

Key modelling decisions specific to this repo:

A Target IS the prospect company. There is no separate deduplicated company master reused across many campaigns. A Target belongs to 0..1 Campaign (it may be "unassigned"), and re-assignment into another campaign is supported.
Provenance is first-class. Every stored fact lives in research_data_points with source, retrieved_at, and confidence.
Append-only / immutable tables (research_api_calls, bandit_events, audit_logs) are never UPDATEd or DELETEd — they are the audit spine controlling reads.

Tenancy: single-tenant build (see 01-architecture). No tenant_id columns; multi-tenancy is achieved by forking the instance.

Entity-relationship overview

erDiagram
    USER ||--o{ ROLE : has
    USER ||--o{ CAMPAIGN_ACCESS : granted
    CAMPAIGN ||--o{ CAMPAIGN_ACCESS : scopes

    CAMPAIGN ||--o{ PRODUCT : sells
    CAMPAIGN ||--|| ASSIGNMENT_RULE : "routed by"
    CAMPAIGN ||--o{ OUTREACH_VARIANT : defines
    CAMPAIGN ||--o{ CONNECT_VARIANT : defines
    CAMPAIGN ||--o{ RESEARCH_JOB : triggers
    CAMPAIGN ||--o{ TARGET : "contains (0..1 per target)"

    TARGET ||--o{ CONTACT : has
    TARGET ||--o{ DEPARTMENT : has
    TARGET ||--o{ RESEARCH_DATA_POINT : "described by"
    TARGET ||--o{ OUTREACH_MESSAGE : "emailed via"
    TARGET ||--o{ CALL : "called in"
    TARGET ||--o{ PIPELINE_EVENT : "transitions via"

    OUTREACH_VARIANT ||--o{ OUTREACH_STEP : sequences
    OUTREACH_STEP ||--o{ STEP_VERSION : "A/B versions"
    STEP_VERSION ||--o{ BANDIT_EVENT : "learns from"
    STEP_VERSION ||--o{ OUTREACH_MESSAGE : "rendered as"

    CONNECT_VARIANT ||--o{ CONNECT_STEP : sequences
    CONNECT_VARIANT ||--o{ SHIFT : "worked in"
    SHIFT ||--o{ PACKAGE : "split into (<=4)"
    PACKAGE ||--o{ TARGET : locks
    SHIFT ||--o{ CALL : produces

    VIRTUAL_COMPANY ||--|{ VIRTUAL_EMPLOYEE : "employs (1..3)"
    VIRTUAL_EMPLOYEE ||--o{ OUTREACH_MESSAGE : sends

    KNOWLEDGE_ENTRY }o--o{ OUTREACH_STEP : informs

Tables

Users & access

users — a login user.

field	type	notes
id	pk
name	string
email	string	unique among live users only
language	enum	`de` / `en` — per-user UI language
status	enum	active / soft-deleted (soft-delete invalidates sessions/tokens)
created_at, updated_at

The last active Admin is protected from delete/deactivate/demote. Auth is passwordless magic-link (tokens hashed at rest, single-use, 15-min TTL); no password column required.

roles — role assignment (RBAC via a permission layer, e.g. CASL).

field	type	notes
id, user_id	fk
role	enum	Admin / Supervisor / Connect-Agent / Closing-Agent

campaign_access — which campaigns a user is scoped to (per-resource authorization).

field	type	notes
id, user_id, campaign_id	fk
released	bool	Connect-Agent sees only released campaigns

Campaigns

campaigns

field	type	notes
id	pk
name	string
budget	bigint	minor units
budget_warning_threshold	bigint	fires warning email at 80%; auto-pause at 100%
daily_limit, per_run_limit	int	research throttles
status	enum	draft / running / paused / done

products — what's being sold (feeds campaigns + sales team).

field	type	notes
id, campaign_id	fk
name	string
description	text null

assignment_rules — one rule per campaign; decides which researched targets it accepts.

field	type	notes
id, campaign_id	fk	1:1 with campaign
type	enum	`threshold` (numeric benchmark cutoff) / `llm_prompt` (natural-language match)
threshold	smallint null	for `threshold` type
prompt	text null	for `llm_prompt` type

research_jobs — a research/discovery run for a campaign.

field	type	notes
id, campaign_id	fk
postal_codes	json	region(s) to search
industry	string	branch/category filter
quantity_limit	int	cap per trigger
trigger	enum	manual / cron
status	enum	queued / running / done / failed
created_at	timestamp

Targets (the prospect company)

targets — a prospect company. Belongs to 0..1 campaign; may be unassigned.

field	type	notes
id	pk
campaign_id	fk null	0..1 — null means "unassigned"; re-assignment supported
name	string
address, city	string
postal_code	string	indexed (research is postal-code driven)
industry	string	branch/category
website	string null
founded_on	date null	drives assignment rules like "founded this year"
benchmark_score	smallint null	0–100, denormalized latest score for fast sorting
benchmark_version	string null	which version produced `benchmark_score`
benchmark_breakdown	json null	per-dimension contribution (why it scored)
pipeline_status	enum	current connect/closing stage (see lifecycles below)
closing_agent_id	fk null	round-robin-assigned Closing-Agent (once in closing)
researched_at	timestamp null	drives >60-day stale re-research on assignment
assigned_at	timestamp null	when it attached to a campaign (passed the assignment rule)
created_at, updated_at

contacts — persons found at a target (1:n).

field	type	notes
id, target_id	fk
department_id	fk null
name	string
role	string null	e.g. "Geschäftsführer"
email	string null
phone	string null

departments — organizational units at a target.

field	type	notes
id, target_id	fk
name	string

research_data_points — one row per fact, with provenance. (special table)

field	type	notes
id, target_id	fk
attribute	enum	canonical attribute name
value	json	shape-validated per attribute
source	string	`<provider>[:step_model]` or `manual:user_id:<id>`
retrieved_at	timestamp	when the fact was obtained
confidence	float	0–1
raw_response	json null	optional raw payload
api_call_id	fk null	→ `research_api_calls`

Benchmark

The benchmark is a versioned 0–100 score computed once per target across 5 weighted dimensions: (1) Segment fit, (2) Size & hiring pressure, (3) Growth & dynamics, (4) Marketing/sales intent, (5) Contactability & data quality. It is denormalized onto targets.benchmark_score (+ benchmark_version, benchmark_breakdown) for fast sorting. Re-scoring writes a new version and never rewrites history; there is no auto-refresh except a re-research when a >60-day-stale target is assigned to a campaign.

Outreach

outreach_variants — a named outreach sequence configuration for a campaign.

field	type	notes
id, campaign_id	fk
name	string

outreach_steps — the ordered steps of a variant (3-mail sequence).

field	type	notes
id, outreach_variant_id	fk
sequence_no	tinyint	1..3
wait_hours	int	wait window before this step / before hand-off

step_versions — A/B content variants of a step; the Bandit's arms.

field	type	notes
id, outreach_step_id	fk
subject, body	text	AI-generated content
content_hash	string	hashed & audited
active	bool	eligible for selection

outreach_messages — one email per target per step actually sent.

field	type	notes
id, target_id	fk
step_version_id	fk	which arm the Bandit selected at send time
virtual_employee_id	fk	which synthetic sender identity sent it
provider_message_id	string null	Microsoft Graph message id
status	enum	queued / sent / opened / replied / bounced
reply_classification	enum null	genuine reply / out-of-office / spam / bounce
reply_body	text null
sent_at, replied_at	timestamp null

bandit_events — append-only learning signal for step-versions. (special table)

field	type	notes
id, step_version_id	fk	the arm
outcome	enum	success / failure
target_id, outreach_message_id, call_id	fk null	source of the outcome
created_at	timestamp

Bandit: a multi-armed bandit (Thompson Sampling default, epsilon-greedy alternative) selects the best StepVersion at send time and learns from reply outcomes. Success = a genuine reply (not spam / out-of-office); Failure = no reply in the wait window. Non-responders after the 3-mail sequence hand off to Connect.

knowledge_entries — reusable knowledge that informs outreach/connect content generation.

field	type	notes
id	pk
title	string
body	text

Virtual senders

virtual_companies — a synthetic sender company identity the platform manages.

field	type	notes
id	pk
name	string
domain	string	purchased via INWX

virtual_employees — 1..3 synthetic senders per virtual company.

field	type	notes
id, virtual_company_id	fk	1..3 per company
name	string
mailbox	string	Exchange Online mailbox (Microsoft Graph)
warmup_status	enum	warming / ready (Trulyinbox)
on_leave	bool	randomized sick days / planned vacations

Connect — call center

connect_variants — weekday/time windows within which shifts must fall.

field	type	notes
id, campaign_id	fk
name	string
windows	json	allowed weekday/time windows

connect_steps — the ordered call/follow-up steps of a connect variant.

field	type	notes
id, connect_variant_id	fk
sequence_no	tinyint
script	text	call script

shifts — a scheduled block of calling work, inside a connect variant's windows.

field	type	notes
id, connect_variant_id	fk
agent_id	fk (user)	the Connect-Agent
starts_at	timestamp	must fall within the variant's windows
status	enum	scheduled / active / done

packages — up to 4 call packages per shift, each a locked queue of top-benchmark targets.

field	type	notes
id, shift_id	fk	≤ 4 per shift
position	int	dial order within the package (benchmark desc)

Targets are locked into a package (the packages↔targets link carries dial position + worked flag).

calls — one call outcome (from CloudTalk webhooks + the live cockpit).

field	type	notes
id, target_id, shift_id	fk
agent_id	fk (user)
gatekeeper_reached	bool
decision_maker_reached	bool
interest	enum	none / low / high
appointment_booked	bool
duration_seconds	int null
recording_url	string null	from CloudTalk
disposition	string null	agent-entered outcome
called_at	timestamp

KPIs (derived, not stored): reach rate, gatekeeper quote, interest rate, appointment rate, per-agent provision. Controlling materializes these; live views compute on read.

Closing

Closing has no separate deal/appointment/contract entities — a Target moves through the closing pipeline (targets.pipeline_status + pipeline_events), is round-robin assigned via targets.closing_agent_id, and its contract/payment are handled through integrations:

SignRequest — contract send + signed-event webhook.
GoCardless — payment collection after close.
No-show tracking + warm-handoff are recorded as pipeline_events (with metadata such as scheduled_at, no_show, warm_handoff).

Pipeline & audit (cross-cutting)

pipeline_events — per-target stage transitions; the audit spine controlling reads.

field	type	notes
id, target_id	fk
from_status, to_status	enum	stage transition
actor_id	fk (user) null	who / what triggered it (null = system)
metadata	json null	e.g. scheduled_at, no_show, warm_handoff
created_at	timestamp

research_api_calls — append-only audit of every external HTTP/LLM call. (special table — never UPDATE/DELETE)

field	type	notes
id	pk
provider	string	e.g. anthropic / serper / firecrawl
endpoint	string
model_id	string null	for LLM calls
tokens	int null
cost	bigint	minor units
duration_ms	int
status	enum	ok / retried / failed
error	text null
requested_at	timestamp

audit_logs — immutable change history. (special table — not editable, not deletable)

field	type	notes
id	pk
auditable_type, auditable_id		the model changed
event	string	pipeline transition / campaign change / step-version change / permission change / settings change / manual correction
old_values, new_values	json	before → after
user_id	fk null	who
ip, user_agent	string null
tags	json null
created_at	timestamp

settings — key/value config; third-party secrets stored encrypted (set via UI, never in env — except the Anthropic key, an infrastructure credential). (special table)

field	type	notes
id, key
value	text	encrypted when the key holds a secret
encrypted	bool

Lifecycle

Lifecycle transitions are an explicit state machine in the domain layer (enums + guarded transitions — illegal transitions are impossible). Every transition is recorded in pipeline_events and mirrored into audit_logs.

Connect pipeline (8 stages): Targets → Not reached (follow-up) → Reached (gatekeeper blocked) → Reached (appointment interest) → Appointment scheduled → No interest (follow-up) → Finally rejected → Do not contact.

Bandit success = Appointment scheduled. Failure = not reached / finally rejected / no interest / do-not-contact. Intermediate states (gatekeeper, appointment interest) fire no event.

Closing pipeline (8 stages): New target (validation pre-stage) → Appointment scheduled → Follow-up → First meeting → Follow-up meeting → Sold (end) → Not sold (end) → Unqualified (end).

Indexing (minimum)

targets(campaign_id, pipeline_status)
targets(postal_code)
outreach_messages(target_id, sent_at)
bandit_events(step_version_id, created_at)
audit_logs(auditable_type, auditable_id, created_at)
research_data_points(target_id, attribute) and (target_id, attribute, retrieved_at DESC)
research_api_calls(provider, requested_at) and (status, requested_at)

Notes on fidelity to the plan

"A Target is the prospect company" → targets carries the company data directly; there is no shared company master, and campaign_id is nullable (0..1) to support unassigned targets + re-assignment.
"One score, computed once, flows everywhere" → benchmark_score denormalized on targets for fast sorting; full versioned history preserved, never rewritten.
"Assignment rule runs after research" → assignment_rules (threshold or LLM-prompt) decides which researched targets attach to a campaign (assigned_at set) vs. stay unassigned.
Provenance everywhere → every fact in research_data_points carries source / retrieved_at / confidence; manual edits use source = manual:user_id:<id>.
Legal note (design consideration, not a v1 build item): GDPR right-to-erasure and German UWG cautions should shape the schema (deletion cascades from targets/contacts), but a full GDPR / spam / telemarketing compliance layer is out of scope for v1 — get a written legal opinion before Outreach goes live.