Database schema

Generated from deanreach/models.py by scripts/schema_md.py. SQLite by default; the same models create the tables on PostgreSQL when DATABASE_URL is set. Datetimes are stored in UTC.

disciplines

Keyword lists that tag contacts with an academic discipline; disabled disciplines are skipped at send time.

Column	Type	Notes
id	INTEGER	primary key
name	VARCHAR(80)	unique
keywords	VARCHAR(500)	default ''
enabled	BOOLEAN	default True

institutions

One row per college or university. Status and an optional per-institution cap override the global rules.

Column	Type	Notes
id	INTEGER	primary key
name	VARCHAR(200)	unique
domain	VARCHAR(120)	unique
kind	VARCHAR(60)	default 'University'
location	VARCHAR(120)	default ''
status	VARCHAR(30)	default 'active'
notes	TEXT	default ''
max_contacts	INTEGER	nullable
last_contacted_at	DATETIME	nullable
created_at	DATETIME

runs

Every discovery or outreach run with a JSON summary of counts.

Column	Type	Notes
id	INTEGER	primary key
kind	VARCHAR(20)
trigger	VARCHAR(20)	default 'manual'
status	VARCHAR(20)	default 'running'
started_at	DATETIME
finished_at	DATETIME	nullable
summary	TEXT	default '{}'

settings

Key/value store for the automation settings edited in the admin.

Column	Type	Notes
key	VARCHAR(80)	primary key
value	TEXT	default ''

target_titles

Regular expressions that decide which job titles count, with a send priority (1 first).

Column	Type	Notes
id	INTEGER	primary key
name	VARCHAR(80)	unique
pattern	VARCHAR(300)
priority	INTEGER	default 3
enabled	BOOLEAN	default True

templates

Editable email templates with merge fields; one per discipline plus a default.

Column	Type	Notes
id	INTEGER	primary key
name	VARCHAR(120)	unique
subject	VARCHAR(300)
body	TEXT
discipline	VARCHAR(80)	default ''
attach_cv	BOOLEAN	default True
is_default	BOOLEAN	default False
enabled	BOOLEAN	default True
updated_at	DATETIME

exclusions

Do-not-contact rules: email, domain, institution, person (by name) or department at one institution.

Column	Type	Notes
id	INTEGER	primary key
kind	VARCHAR(20)
value	VARCHAR(300)
institution_id	INTEGER	references institutions.id, nullable
reason	VARCHAR(300)	default ''
created_by	VARCHAR(60)	default 'admin'
created_at	DATETIME

sources

Public pages the crawler reads on the discovery schedule, with the result of the last crawl.

Column	Type	Notes
id	INTEGER	primary key
institution_id	INTEGER	references institutions.id
url	VARCHAR(500)	unique
label	VARCHAR(200)	default ''
discipline_hint	VARCHAR(80)	default ''
enabled	BOOLEAN	default True
last_crawled_at	DATETIME	nullable
last_status	VARCHAR(200)	default ''
last_extractor	VARCHAR(120)	default ''
last_found	INTEGER	default 0
created_at	DATETIME

contacts

Every person found. status is the outreach state; email_normalized is the duplicate key; evidence keeps where and how the person was found and the score breakdown.

Column	Type	Notes
id	INTEGER	primary key
institution_id	INTEGER	references institutions.id
source_id	INTEGER	references sources.id, nullable
name	VARCHAR(200)
honorific	VARCHAR(20)	default ''
first_name	VARCHAR(100)	default ''
last_name	VARCHAR(100)	default ''
title	VARCHAR(200)	default ''
title_match	VARCHAR(80)	default ''
department	VARCHAR(200)	default ''
discipline	VARCHAR(80)	default ''
email	VARCHAR(200)	default ''
email_normalized	VARCHAR(200)	indexed, default ''
source_url	VARCHAR(500)	default ''
discovered_at	DATETIME
last_seen_at	DATETIME
status	VARCHAR(30)	indexed, default 'needs_review'
status_reason	VARCHAR(300)	default ''
confidence	INTEGER	default 0
extractor	VARCHAR(60)	default ''
evidence	TEXT	default ''
contacted_at	DATETIME	nullable
contact_count	INTEGER	default 0
duplicate_of_id	INTEGER	references contacts.id, nullable
notes	TEXT	default ''
updated_at	DATETIME

messages

Every email sent (or built in dry-run mode): recipient, rendered text, attachment, Gmail ids, the request preview, errors.

Column	Type	Notes
id	INTEGER	primary key
contact_id	INTEGER	references contacts.id, indexed
institution_id	INTEGER	references institutions.id, indexed
template_id	INTEGER	references templates.id, nullable
run_id	INTEGER	references runs.id, nullable
to_email	VARCHAR(200)
to_name	VARCHAR(200)	default ''
subject	VARCHAR(300)
body	TEXT
attachment_name	VARCHAR(200)	default ''
status	VARCHAR(20)	default 'sent'
transport	VARCHAR(20)	default 'gmail'
gmail_message_id	VARCHAR(120)	default ''
gmail_thread_id	VARCHAR(120)	default ''
request_preview	TEXT	default ''
error	TEXT	default ''
sent_at	DATETIME	indexed

run_events

The trace of a run, one line per decision, linked to the contact or source it concerns.

Column	Type	Notes
id	INTEGER	primary key
run_id	INTEGER	references runs.id, indexed
seq	INTEGER
level	VARCHAR(10)	default 'info'
stage	VARCHAR(20)	default ''
message	TEXT
contact_id	INTEGER	references contacts.id, nullable
source_id	INTEGER	references sources.id, nullable

Status vocabulary

contacts.status: eligible, needs_review, previously_contacted, cooldown, do_not_contact, blocked, invalid. institutions.status: active, cooldown, blocked, do_not_contact. messages.status: sent, dry_run, failed.