# Database relationships

Generated by `python docs/generate.py` from all six migrations.

```mermaid
erDiagram
    category {
        INTEGER category_id PK
        TEXT name
    }
    checkout_quotes {
        TEXT token PK
        INTEGER person_id
        INTEGER cart_revision
        TEXT snapshot
        INTEGER expires_at
        INTEGER order_id
    }
    conversation_messages {
        INTEGER message_id PK
        TEXT conversation_id
        TEXT role
        TEXT content
        TEXT created_at
    }
    conversations {
        TEXT conversation_id PK
        INTEGER person_id
        TEXT summary
        INTEGER busy
        INTEGER busy_at
        TEXT lock_token
        TEXT created_at
    }
    customer_cart {
        INTEGER person_id PK
        INTEGER revision
        TEXT updated_at
    }
    customer_cart_items {
        INTEGER person_id PK
        INTEGER product_id PK
        INTEGER quantity
    }
    customer_memories {
        INTEGER memory_id PK
        INTEGER person_id
        TEXT memory_key
        TEXT text
        INTEGER version
        INTEGER active
        INTEGER source_message_id
        TEXT created_at
        TEXT updated_at
    }
    customer_memory_jobs {
        INTEGER memory_id PK
        INTEGER version
        TEXT operation
        TEXT status
        TEXT error
    }
    customer_preferences {
        INTEGER person_id PK
        INTEGER memory_enabled
    }
    customer_sessions {
        TEXT token_hash PK
        INTEGER person_id
        TEXT csrf_hash
        INTEGER expires_at
    }
    order_checkout {
        INTEGER order_id PK
        TEXT payment_method
        TEXT payment_status
        TEXT delivery_address
        TEXT shipping_status
    }
    order_items {
        INTEGER order_item_id PK
        INTEGER order_id
        INTEGER product_id
        INTEGER quantity
        DECIMAL unit_price
    }
    order_status {
        INTEGER status_id PK
        TEXT label
    }
    orders {
        INTEGER order_id PK
        INTEGER person_id
        INTEGER status_id
        TEXT order_date
        DECIMAL total_amount
        TEXT created_at
        TEXT updated_at
    }
    person {
        INTEGER person_id PK
        TEXT first_name
        TEXT last_name
        TEXT email
        TEXT password_hash
        TEXT phone
        TEXT address
        TEXT created_at
    }
    product {
        INTEGER product_id PK
        TEXT name
        TEXT description
        DECIMAL price
        INTEGER stock_quantity
        INTEGER category_id
        TEXT image_prompt
        TEXT image_path
        DECIMAL avg_rating
        TEXT review
        TEXT image_status
        TEXT created_at
        TEXT updated_at
        INTEGER search_revision
        REAL calories_per_100
        TEXT calorie_basis
        TEXT calorie_status
        TEXT calorie_source
        TEXT calorie_notes
    }
    product_fts {
        TEXT name
        TEXT category
        TEXT description
        TEXT nutrition
    }
    product_index_jobs {
        INTEGER product_id PK
        INTEGER revision
        TEXT operation
        TEXT status
        INTEGER attempts
        TEXT error
        REAL next_attempt_at
        TEXT updated_at
    }
    schema_migrations {
        TEXT version PK
    }
    verified_product_reviews {
        INTEGER review_id PK
        INTEGER person_id
        INTEGER order_id
        INTEGER product_id
        INTEGER rating
        TEXT comment
        TEXT created_at
        TEXT updated_at
    }
    orders ||--o{ checkout_quotes : "order_id"
    person ||--o{ checkout_quotes : "person_id"
    conversations ||--o{ conversation_messages : "conversation_id"
    person ||--o{ conversations : "person_id"
    person ||--o{ customer_cart : "person_id"
    product ||--o{ customer_cart_items : "product_id"
    customer_cart ||--o{ customer_cart_items : "person_id"
    conversation_messages ||--o{ customer_memories : "source_message_id"
    person ||--o{ customer_memories : "person_id"
    customer_memories ||--o{ customer_memory_jobs : "memory_id"
    person ||--o{ customer_preferences : "person_id"
    person ||--o{ customer_sessions : "person_id"
    orders ||--o{ order_checkout : "order_id"
    product ||--o{ order_items : "product_id"
    orders ||--o{ order_items : "order_id"
    order_status ||--o{ orders : "status_id"
    person ||--o{ orders : "person_id"
    category ||--o{ product : "category_id"
    product ||--o{ verified_product_reviews : "product_id"
    orders ||--o{ verified_product_reviews : "order_id"
    person ||--o{ verified_product_reviews : "person_id"
```

FTS shadow tables and sqlite_sequence are implementation details and omitted.
product_fts.rowid logically equals product.product_id. product_index_jobs.product_id is a durable logical reference, not an FK, so deleted products can retain cleanup jobs.

## Qdrant (derived indexes)

| Collection | Point ID | Authoritative join / isolation |
| --- | --- | --- |
| market_products | SQL product_id | payload category_id = SQL category_id; revision, document_hash and embedding_model checked |
| customer_memories | SQL memory_id | payload person_id, version and embedding_model; SQL owner/active/version checked again |

Both collections use 1024-dimensional BGE embeddings with cosine similarity. Qdrant is not a relational store; the links above are logical mappings, not database foreign keys.
