b77c8b0044
Design foundation for POC. Monorepo: FastAPI+Postgres backend, Vue 3 frontend, Python packages (llm-gateway, artifacts, connectors). Approval gate and token budgets are architectural constraints per measured prototype findings.
129 lines
4.7 KiB
Markdown
129 lines
4.7 KiB
Markdown
# 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
|
|
|
|
```sql
|
|
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.
|