jobhunt-platform/docs/data-model.md

Data model (POC)

PostgreSQL. Plain SQL DDL in apps/api/schema.sql, managed with a simple numeric migration runner (apps/api/migrations/). No ORM (owner preference: no SQLAlchemy/Alembic). Access via psycopg (v3) repositories in apps/api/db/.

Tables

profile (
  id uuid pk,
  full_name text not null,
  email text not null,
  phone text,
  location text,
  headline text,
  summary text,
  languages jsonb not null default '[]',     -- [{code, level}]
  hard_rules jsonb not null default '{}',   -- e.g. {"no_remote_only": false, "locations": ["Malmo","Copenhagen"]}
  created_at timestamptz default now(),
  updated_at timestamptz default now()
)

cv_section (
  id uuid pk,
  profile_id uuid not null references profile(id) on delete cascade,
  kind text not null check (kind in ('experience','education','skills','projects','other')),
  title text not null,
  org text,                                   -- company/school
  location text,
  start_date date,
  end_date date,                              -- null = current
  bullets jsonb not null default '[]',
  tags text[] not null default '{}',          -- stack/keywords for tailoring
  sort_order int not null default 0,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
)

job_posting (
  id uuid pk,
  source text not null,                       -- 'linkedin' | 'jobindex' | 'manual_url' | ...
  external_id text,                           -- source-native id when known
  url text not null,
  company text not null,
  title text not null,
  location text,
  description text not null default '',
  raw jsonb not null default '{}',
  fetched_at timestamptz default now(),
  unique (source, url)
)

application (
  id uuid pk,
  job_posting_id uuid not null references job_posting(id) on delete cascade,
  state text not null default 'discovered' check (state in
    ('discovered','scored','approved','rejected','drafting','sent','interviewing','offer','closed','expired')),
  score numeric,                              -- 0..100 from scoring rubric
  score_rationale jsonb,                      -- rubric breakdown from LLM
  notes text,
  state_changed_at timestamptz default now(),
  created_at timestamptz default now()
)

artifact (
  id uuid pk,
  application_id uuid not null references application(id) on delete cascade,
  kind text not null check (kind in ('cv','cover_letter','email','other')),
  filename text not null,
  content_hash text not null,                 -- sha256 of stored bytes
  storage_path text not null,
  version int not null default 1,
  origin text not null default 'ai_reviewed' check (origin in ('user_drafted','ai_drafted','ai_reviewed')),
  created_at timestamptz default now()
)

approval (
  id uuid pk,
  application_id uuid not null references application(id) on delete cascade,
  artifact_id uuid not null references artifact(id),
  artifact_hash text not null,                -- must equal artifact.content_hash at send time
  action text not null check (action in ('send_email','submit_application')),
  confirmed_by_user boolean not null default false,
  confirmed_at timestamptz,
  expires_at timestamptz not null,            -- now() + interval '24 hours'
  created_at timestamptz default now()
)

outbox (
  id uuid pk,
  approval_id uuid not null references approval(id),
  kind text not null default 'email',
  payload jsonb not null,                     -- to, subject, body, attachments [artifact ids]
  status text not null default 'pending' check (status in ('pending','sent','failed','cancelled')),
  sent_at timestamptz,
  error text,
  created_at timestamptz default now()
)

task_run (                                    -- LLM/token telemetry
  id uuid pk,
  task text not null,                         -- 'score','extract','critique','research', ...
  model text not null,
  provider text not null,
  input_tokens int not null,
  output_tokens int not null,
  cost_usd numeric,                           -- nullable until pricing configured
  duration_ms int not null,
  application_id uuid references application(id),
  created_at timestamptz default now()
)

Transition table (state machine, enforced in code)

from	to	guard
discovered	scored	scoring task completed
discovered	rejected	user action
scored	approved	user action
scored	rejected	user action
approved	drafting	user action or artifact created
drafting	sent	confirmed approval present + hash match
sent	interviewing	user action
interviewing	offer	user action
interviewing	closed	user action
offer	closed	user action
scored, approved	expired	posting gone or deadline passed (scheduler)

Any transition not listed = 409 Conflict. State changes are user actions or explicit scheduler rules, never LLM decisions.