24713ce5e6
- Rebrand LoginPage logo to Oliver design system - Move Admin nav link to bottom-left user area - Remove "Ask AI to improve" button from step 2 (VariantsGrid) - Add CharCount component with copy limits across prompt, edit, and export views - Add delete variant with confirmation dialog on step 2 cards - Add theme palette migration (0007_theme_palette.py) - Add copyLimits.ts and themes.ts libs - Remove unused BannerEditor.tsx page and old logo PNG - Add AGENTS.md project entry point - Add docs/ directory Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
8.5 KiB
8.5 KiB
Database Schema
Database: PostgreSQL 16 with the pgvector extension.
Connection string pattern: postgresql+asyncpg://<user>:<password>@<host>:5432/<dbname>
Migrations managed by Alembic (backend/alembic/). Apply with:
docker compose exec api alembic upgrade head
Tables
users
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
email |
VARCHAR(255) | NOT NULL, UNIQUE, INDEX | Stored lowercase |
hashed_password |
VARCHAR(255) | NOT NULL | bcrypt |
role |
VARCHAR(50) | NOT NULL, default user |
user or admin |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
briefs
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
user_id |
UUID | NOT NULL, FK → users.id |
Owner |
text |
TEXT | NOT NULL | Raw campaign brief text |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
Relationship: one Brief → many BannerSet
conversations
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
user_id |
UUID | NOT NULL, FK → users.id |
Owner |
brief_id |
UUID | NOT NULL, FK → briefs.id |
Associated brief |
title |
VARCHAR(120) | NOT NULL, default "" |
First 60 chars of first message |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
updated_at |
TIMESTAMPTZ | NOT NULL | Updated on each new message |
Relationship: one Conversation → many ConversationMessage
conversation_messages
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
conversation_id |
UUID | NOT NULL, FK → conversations.id |
|
role |
VARCHAR(20) | NOT NULL | user or assistant |
kind |
VARCHAR(20) | NOT NULL, default text |
text, generation, refinement |
content |
TEXT | NOT NULL, default "" |
Message text |
banner_set_id |
UUID | NULLABLE, FK → banner_sets.id |
Set for generation/refinement messages |
job_id |
UUID | NULLABLE, FK → jobs.id |
Associated background job |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
Index: ix_conversation_messages_conv_created on (conversation_id, created_at) — composite for efficient history fetch.
banner_sets
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
brief_id |
UUID | NOT NULL, FK → briefs.id |
|
status |
VARCHAR(50) | NOT NULL, default draft |
draft, approved, exported |
workfront_campaign_id |
VARCHAR(255) | NULLABLE | Optional Workfront reference |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
Relationship: one BannerSet → many BannerVariant
banner_variants
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
banner_set_id |
UUID | NOT NULL, FK → banner_sets.id |
|
aspect_ratio |
VARCHAR(20) | NOT NULL | SM2, MD1, MD2, MD3, LG1, LG2, Custom |
pair_id |
UUID | NULLABLE, INDEX | Groups all format variants from same copy set |
custom_width |
INTEGER | NULLABLE | Pixels — only for Custom aspect_ratio |
custom_height |
INTEGER | NULLABLE | Pixels — only for Custom aspect_ratio |
theme |
VARCHAR(20) | NOT NULL, default navy |
navy, sky-blue, yellow, lime, teal |
short_title |
VARCHAR(64) | NOT NULL | Title copy |
long_body |
VARCHAR(128) | NOT NULL | Body copy |
cta |
VARCHAR(50) | NOT NULL | Primary CTA |
cta_secondary |
VARCHAR(50) | NULLABLE | Secondary CTA (Large formats only) |
dam_asset_ref |
VARCHAR(500) | NULLABLE | Adobe DAM asset ID |
dam_asset_url |
TEXT | NULLABLE | DAM asset image URL |
icon_id |
UUID | NULLABLE, FK → icons.id |
Selected illustration |
edited_by_user |
BOOLEAN | NOT NULL, default false |
Manual edits flag |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
jobs
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
type |
VARCHAR(50) | NOT NULL | generate_copy, chat_turn, render_pdf, index_icons, ingest_rag |
status |
VARCHAR(20) | NOT NULL, INDEX, default pending |
pending, running, done, failed |
payload |
JSON | NULLABLE | Input data (brief_id, n_variants, variant_ids, etc.) |
result |
JSON | NULLABLE | Output data (banner_set_id, pdf_path, etc.) |
error |
TEXT | NULLABLE | Error message on failure |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
finished_at |
TIMESTAMPTZ | NULLABLE | UTC completion time |
Index: status — for efficient polling / queue monitoring.
system_prompts
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
version |
INTEGER | NOT NULL, default 1 | Auto-incrementing per new version |
label |
VARCHAR(100) | NOT NULL, default Default |
Human-readable name |
system_text |
TEXT | NOT NULL | Core system prompt |
tov_text |
TEXT | NULLABLE | Tone-of-voice rules appended to system message |
tov_neutral_tone |
BOOLEAN | NOT NULL, default true |
Legacy flag (kept for DB compat) |
tov_uk_english |
BOOLEAN | NOT NULL, default true |
Legacy flag |
tov_no_exclamations |
BOOLEAN | NOT NULL, default true |
Legacy flag |
tov_sentence_case |
BOOLEAN | NOT NULL, default true |
Legacy flag |
tov_banned_phrases |
TEXT | NOT NULL | Comma-separated banned phrases |
short_title_max |
INTEGER | NOT NULL, default 32 | Per-version character limit |
long_body_max |
INTEGER | NOT NULL, default 128 | Per-version character limit |
cta_max |
INTEGER | NOT NULL, default 50 | Per-version character limit |
is_active |
BOOLEAN | NOT NULL, INDEX, default false |
Only one row = true at a time |
created_by_id |
UUID | NULLABLE, FK → users.id |
Admin who created this version |
created_at |
TIMESTAMPTZ | NOT NULL | UTC |
Audit trail: Previous versions are never deleted — only is_active is toggled to false.
icons
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
name |
VARCHAR(255) | NOT NULL | Human-readable name |
category |
VARCHAR(100) | NOT NULL, INDEX | e.g. Banking, Cards, Nature |
path |
VARCHAR(500) | NOT NULL | Relative path under assets/illustrations/ |
keywords |
ARRAY(VARCHAR) | NOT NULL, default [] |
Tag list for keyword search |
embedding |
Vector(1536) | NULLABLE | text-embedding-3-small vector for cosine search |
rag_chunks
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID | PK, default uuid4 | |
source_doc |
VARCHAR(255) | NOT NULL, INDEX | Source filename (e.g. Consumer Duty summary.docx) |
chunk_text |
TEXT | NOT NULL | Paragraph chunk |
chunk_index |
INTEGER | NOT NULL | Position within source document |
embedding |
Vector(1536) | NULLABLE | text-embedding-3-small vector for cosine retrieval |
dam_cache (model present, not actively used)
Defined in models/dam_cache.py — reserved for future DAM response caching.
Relationships Diagram
users ─────────────────────────────────┐
│ 1:N │ 1:N
briefs conversations
│ 1:N │ 1:N
banner_sets conversation_messages
│ 1:N (references jobs, banner_sets)
banner_variants ──── FK ──── icons
──── FK (optional) ──── jobs (via conversation_messages)
system_prompts (independent; FK to users for created_by)
rag_chunks (independent)
icons (independent; referenced by banner_variants)
jobs (independent; enqueued by API, updated by worker)
pgvector Usage
Two tables use pgvector Vector(1536) columns for cosine similarity search:
| Table | Column | Used in |
|---|---|---|
rag_chunks |
embedding |
RAG retrieval during copy generation |
icons |
embedding |
Icon matching during banner set creation |
Both use OpenAI text-embedding-3-small (1536 dimensions).
Cosine search is performed via SQLAlchemy in backend/app/rag/retrieve.py and backend/app/services/icon_matcher.py.