17759ef564
- apps/api: psycopg v3 repositories, schema.sql + migration runner, state machine implementing the data-model transition table, approval gate with sha256 hash match + 24h expiry enforced at send time, EchoTransport pluggable sender, LLM calls routed through packages.llm-gateway with mock mode, 47 pytest tests green - apps/api/Dockerfile.test: python 3.13-slim test image (DinD-safe: migrations copied as directory) - docker-compose.yml: add api-test service on compose network (host port publishing is broken in this sandbox; container-to-container networking used) - Fix: replace masked placeholder password in config.py/conftest.py defaults
106 lines
No EOL
3.8 KiB
SQL
106 lines
No EOL
3.8 KiB
SQL
-- schema.sql — full DDL for the jobhunt platform POC
|
|
-- Plain SQL, no ORM. Applied by the migration runner in db/migrate.py.
|
|
|
|
CREATE TABLE IF NOT EXISTS profile (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
full_name text NOT NULL,
|
|
email text NOT NULL,
|
|
phone text,
|
|
location text,
|
|
headline text,
|
|
summary text,
|
|
languages jsonb NOT NULL DEFAULT '[]',
|
|
hard_rules jsonb NOT NULL DEFAULT '{}',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cv_section (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
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,
|
|
location text,
|
|
start_date date,
|
|
end_date date,
|
|
bullets jsonb NOT NULL DEFAULT '[]',
|
|
tags text[] NOT NULL DEFAULT '{}',
|
|
sort_order int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS job_posting (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
source text NOT NULL,
|
|
external_id text,
|
|
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 NOT NULL DEFAULT now(),
|
|
UNIQUE (source, url)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS application (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
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,
|
|
score_rationale jsonb,
|
|
notes text,
|
|
state_changed_at timestamptz NOT NULL DEFAULT now(),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS artifact (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
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,
|
|
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 NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS approval (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
application_id uuid NOT NULL REFERENCES application(id) ON DELETE CASCADE,
|
|
artifact_id uuid NOT NULL REFERENCES artifact(id),
|
|
artifact_hash text NOT NULL,
|
|
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,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS outbox (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
approval_id uuid NOT NULL REFERENCES approval(id),
|
|
kind text NOT NULL DEFAULT 'email',
|
|
payload jsonb NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed','cancelled')),
|
|
sent_at timestamptz,
|
|
error text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS task_run (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
task text NOT NULL,
|
|
model text NOT NULL,
|
|
provider text NOT NULL,
|
|
input_tokens int NOT NULL,
|
|
output_tokens int NOT NULL,
|
|
cost_usd numeric,
|
|
duration_ms int NOT NULL,
|
|
application_id uuid REFERENCES application(id),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
); |