Data model¶
All models are in backend/app/models.py (SQLAlchemy 2.0, typed Mapped[...]).
Base.metadata.create_all() runs on app start — there is no migration tool;
schema changes to an existing Postgres DB must be applied by hand.
Every table except companies and users carries company_id and is filtered
on it in every query.
Company — the tenant / workspace¶
| Column | Notes |
|---|---|
id |
PK |
name |
workspace name |
industry |
free text, default "General" |
stages |
JSON list of stage names, ordered. Default: DEFAULT_STAGES |
is_premium |
the paywall flag |
is_demo_seeded |
whether seed_demo_data has run for this company |
subscription_status |
"free" / Stripe status string |
stripe_customer_id |
nullable |
dormancy_days |
threshold for the dormancy engine, default 14 |
created_at |
DEFAULT_STAGES = ["New Lead", "Contacted", "Follow-Up Scheduled", "Negotiation", "Won", "Lost"].
The literal strings "Won" and "Lost" are treated as closed
(CLOSED = {"Won", "Lost"} in services/dormancy.py).
User¶
| Column | Notes |
|---|---|
id |
string PK — a UUID5 of dev-auth:<email> in dev, or the Supabase sub in prod |
email |
unique, indexed |
full_name |
|
role |
"super_admin" | "admin" | "agent" (default "admin") |
company_id |
FK, nullable (super-admin may have none) |
is_sample |
seeded sample agent |
Contact — a lead¶
| Column | Notes |
|---|---|
company_id |
FK, indexed |
first_name (req), last_name, email, phone, organization |
|
pipeline_status |
current stage name, default "New Lead" |
assigned_to_id |
FK → users.id, nullable |
custom_metadata |
JSON dict — per-industry fields, e.g. {"Property Value": "$450k"} |
last_activity_at |
driven by interactions, follow-ups and stage changes; the dormancy engine reads this |
created_at, is_sample |
Relationships: interactions and follow_ups (both cascade="all, delete-orphan"), assigned_to.
Interaction — a logged touchpoint (past)¶
| Column | Notes |
|---|---|
company_id, contact_id |
FKs, indexed |
kind |
note | call | email | meeting |
body |
text |
created_by_id |
FK → users.id, nullable |
FollowUp — a scheduled action (future)¶
| Column | Notes |
|---|---|
company_id, contact_id |
FKs, indexed |
scheduled_for |
datetime (tz-aware) |
action_type |
Call | Email | Meeting | Proposal |
notes |
text |
is_completed, completed_at |
|
owner_id |
FK → users.id |
State (derived, not stored): completed if is_completed, else overdue if
scheduled_for < now, else pending.
Sequence / SequenceRun — automated cadences (premium, mocked)¶
Sequence: name, trigger_status (stage that starts it), steps
(JSON list of {"offset_days", "channel", "template"}), is_active.
SequenceRun: one row per fired step — sequence_id, contact_id,
step_index, channel, rendered (the would-be message body), status
("sent", mock), sent_at.
Timezones¶
Datetimes are stored tz-aware (utcnow() returns datetime.now(timezone.utc)).
SQLite hands back naive datetimes on read, so several code paths re-apply
tzinfo=timezone.utc before comparing — see services/dormancy.py and
routers/followups.py. Keep that pattern when adding date comparisons.