Skip to content

database.md

Latest commit

 

History

History
340 lines (292 loc) · 14.8 KB

database.md

File metadata and controls

340 lines (292 loc) · 14.8 KB
Raw

Database Documentation

Overview

The Photobook application uses PostgreSQL as its primary database with SQLAlchemy 2.0 as the async ORM. The database stores user accounts, photobook collections, and the claim-centric Knowledge Graph.

Entity-Relationship Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                                  Users                                       │
│  ┌───────────────────────────────────────────────────────────────────────┐  │
│  │ id: SERIAL PRIMARY KEY                                                 │  │
│  │ email: VARCHAR(255) UNIQUE NOT NULL                                    │  │
│  │ hashed_password: VARCHAR(255) NOT NULL                                 │  │
│  │ is_active: BOOLEAN DEFAULT TRUE                                        │  │
│  │ created_at: TIMESTAMPTZ DEFAULT now()                                  │  │
│  └───────────────────────────────────────────────────────────────────────┘  │
└────────────────────────────────────┬────────────────────────────────────────┘
                                     │
                    ┌────────────────┴────────────────┐
                    │                                 │
                    ▼                                 ▼
┌─────────────────────────────────┐  ┌─────────────────────────────────────────┐
│          Photobooks             │  │              KG Nodes                    │
│ ┌─────────────────────────────┐ │  │ ┌─────────────────────────────────────┐ │
│ │ id: SERIAL PRIMARY KEY      │ │  │ │ id: UUID PRIMARY KEY                │ │
│ │ user_id: FK → users         │ │  │ │ node_type: ENUM                     │ │
│ │ title: VARCHAR(255)         │ │  │ │ canonical_name: VARCHAR(255)        │ │
│ │ description: TEXT           │ │  │ │ canonical_type: VARCHAR(100)        │ │
│ │ cover_image_id: FK → nodes  │ │  │ │ properties: JSONB                   │ │
│ │ created_at: TIMESTAMPTZ     │ │  │ │ embedding: JSONB                    │ │
│ │ updated_at: TIMESTAMPTZ     │ │  │ │ user_id: FK → users                 │ │
│ └─────────────────────────────┘ │  │ │ created_at: TIMESTAMPTZ             │ │
└─────────────────────────────────┘  │ │ updated_at: TIMESTAMPTZ             │ │
                                     │ └─────────────────────────────────────┘ │
                                     └────────────────────┬────────────────────┘
                                                          │
                              ┌───────────────────────────┼───────────────────────────┐
                              │                           │                           │
                              ▼                           ▼                           ▼
               ┌─────────────────────────┐  ┌─────────────────────────┐  ┌────────────────────────┐
               │       KG Claims         │  │      KG Evidence        │  │    KG User Actions     │
               │ ┌─────────────────────┐ │  │ ┌─────────────────────┐ │  │ ┌────────────────────┐ │
               │ │ id: UUID            │ │  │ │ id: UUID            │ │  │ │ id: UUID           │ │
               │ │ subject_id: FK→nodes│ │  │ │ claim_id: FK→claims │ │  │ │ claim_id: FK→claims│ │
               │ │ predicate: VARCHAR  │ │  │ │ image_node_id: FK   │ │  │ │ user_id: FK→users  │ │
               │ │ object_id: FK→nodes │ │  │ │ bbox: JSONB         │ │  │ │ action_type: ENUM  │ │
               │ │ literal_value: TEXT │ │  │ │ mask_path: VARCHAR  │ │  │ │ previous_status    │ │
               │ │ confidence: FLOAT   │ │  │ │ model_output: JSONB │ │  │ │ new_status         │ │
               │ │ status: ENUM        │ │  │ │ confidence: FLOAT   │ │  │ │ correction_data    │ │
               │ │ valid_from: TSTZ    │ │  │ │ created_at: TSTZ    │ │  │ │ created_at: TSTZ   │ │
               │ │ valid_to: TSTZ      │ │  │ └─────────────────────┘ │  │ └────────────────────┘ │
               │ │ source_type: ENUM   │ │  └─────────────────────────┘  └────────────────────────┘
               │ │ source_model: VARCHAR│ │
               │ │ user_id: FK→users   │ │
               │ │ created_at: TSTZ    │ │
               │ │ updated_at: TSTZ    │ │
               │ └─────────────────────┘ │
               └─────────────────────────┘

Tables

users

Core user account table for authentication.

Column Type Constraints Description
id SERIAL PRIMARY KEY Auto-increment ID
email VARCHAR(255) UNIQUE, NOT NULL User email
hashed_password VARCHAR(255) NOT NULL bcrypt hash
is_active BOOLEAN DEFAULT TRUE Account status
created_at TIMESTAMPTZ DEFAULT now() Created timestamp

photobooks

Collections for organizing images.

Column Type Constraints Description
id SERIAL PRIMARY KEY Auto-increment ID
user_id INTEGER FK → users Owner
title VARCHAR(255) NOT NULL Photobook title
description TEXT NULLABLE Description
cover_image_id UUID FK → kg_nodes Cover image node
created_at TIMESTAMPTZ DEFAULT now() Created timestamp
updated_at TIMESTAMPTZ DEFAULT now() Updated timestamp

kg_nodes

Generic graph nodes for all entity types.

Column Type Constraints Description
id UUID PRIMARY KEY UUID v4
node_type ENUM NOT NULL NodeType enum
canonical_name VARCHAR(255) NULLABLE Display name
canonical_type VARCHAR(100) NULLABLE Sub-type
properties JSONB NULLABLE Flexible metadata
embedding JSONB NULLABLE Vector embedding
user_id INTEGER FK → users Owner
created_at TIMESTAMPTZ DEFAULT now() Created
updated_at TIMESTAMPTZ DEFAULT now() Updated

Indexes:

  • ix_kg_nodes_user_type on (user_id, node_type)

Properties JSONB for IMAGE nodes:

{
  "filename": "abc123.jpg",
  "original_name": "vacation_photo.jpg",
  "file_path": "images/abc123.jpg",
  "thumbnail_path": "thumbnails/abc123_thumb.jpg",
  "mime_type": "image/jpeg",
  "size_bytes": 2048576,
  "width": 4032,
  "height": 3024,
  "exif_data": {
    "latitude": 28.272,
    "longitude": -16.642,
    "DateTimeOriginal": "2024:12:15 14:30:00"
  },
  "photobook_id": 1,
  "vlm_description": "A beach scene with sunset...",
  "tags": ["beach", "sunset", "vacation"]
}

kg_claims

Reified edges with confidence and provenance.

Column Type Constraints Description
id UUID PRIMARY KEY UUID v4
subject_id UUID FK → kg_nodes Subject node
predicate VARCHAR(255) NOT NULL Relation type
object_id UUID FK → kg_nodes Object node (optional)
literal_value TEXT NULLABLE Literal value (optional)
confidence FLOAT DEFAULT 0.5 0.0 to 1.0
status ENUM DEFAULT 'proposed' ClaimStatus
valid_from TIMESTAMPTZ NULLABLE Temporal start
valid_to TIMESTAMPTZ NULLABLE Temporal end
source_type ENUM DEFAULT 'SYSTEM' SourceType
source_model VARCHAR(100) NULLABLE Model name
source_version VARCHAR(50) NULLABLE Model version
user_id INTEGER FK → users Owner
created_at TIMESTAMPTZ DEFAULT now() Created
updated_at TIMESTAMPTZ DEFAULT now() Updated

Indexes:

  • ix_kg_claims_subject_predicate on (subject_id, predicate)
  • ix_kg_claims_user_status on (user_id, status)
  • ix_kg_claims_temporal on (valid_from, valid_to)

kg_evidence

Visual grounding for claims.

Column Type Constraints Description
id UUID PRIMARY KEY UUID v4
claim_id UUID FK → kg_claims Parent claim
image_node_id UUID FK → kg_nodes Source image
bbox JSONB NULLABLE {x, y, w, h}
mask_path VARCHAR(500) NULLABLE Segmentation mask
model_output JSONB NULLABLE Raw model output
confidence FLOAT DEFAULT 1.0 Evidence confidence
created_at TIMESTAMPTZ DEFAULT now() Created

kg_user_actions

Audit log for human-in-the-loop actions.

Column Type Constraints Description
id UUID PRIMARY KEY UUID v4
claim_id UUID FK → kg_claims Target claim
user_id INTEGER FK → users Acting user
action_type ENUM NOT NULL ActionType
previous_status ENUM NULLABLE Before status
new_status ENUM NULLABLE After status
correction_data JSONB NULLABLE Correction details
created_at TIMESTAMPTZ DEFAULT now() Action timestamp

Enums

NodeType

class NodeType(str, Enum):
    IMAGE = "IMAGE"
    DETECTION = "DETECTION"
    ENTITY = "ENTITY"
    PERSON = "PERSON"
    PLACE = "PLACE"
    EVENT = "EVENT"
    ATTRIBUTE = "ATTRIBUTE"
    SCENE = "SCENE"
    TAG = "TAG"
    TEXT = "TEXT"
    UNKNOWN = "UNKNOWN"

ClaimStatus

class ClaimStatus(str, Enum):
    PROPOSED = "proposed"           # AI-generated, unverified
    WEAKLY_SUPPORTED = "weakly_supported"  # Multiple evidence sources
    ACCEPTED = "accepted"           # User verified
    REJECTED = "rejected"           # User rejected
    SUPERSEDED = "superseded"       # Replaced by newer claim
    INFERRED = "inferred"           # Derived from other claims

SourceType

class SourceType(str, Enum):
    SYSTEM = "SYSTEM"       # System-generated
    USER = "USER"           # User-created
    AI_VISION = "AI_VISION" # VLM analysis
    AI_FACE = "AI_FACE"     # Face recognition
    EXTERNAL = "EXTERNAL"   # External API

ActionType

class ActionType(str, Enum):
    ACCEPT = "accept"
    REJECT = "reject"
    CORRECT = "correct"

Migrations

Using Alembic for database migrations:

cd backend

# Create new migration
alembic revision --autogenerate -m "Add column description"

# Apply all migrations
alembic upgrade head

# Rollback one migration
alembic downgrade -1

# View current revision
alembic current

# View history
alembic history

Connection Configuration

# core/config.py
class Settings(BaseSettings):
    database_url: str = "postgresql+asyncpg://photobook:photobook@localhost:5432/photobook"
    database_pool_size: int = 5
    database_max_overflow: int = 10
# db/session.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

engine = create_async_engine(
    settings.database_url,
    pool_size=settings.database_pool_size,
    max_overflow=settings.database_max_overflow,
)

async_session = async_sessionmaker(engine, expire_on_commit=False)

async def get_session() -> AsyncSession:
    async with async_session() as session:
        yield session

Common Queries

Get user's images with tags

SELECT n.*, 
       array_agg(DISTINCT t.canonical_name) as tags
FROM kg_nodes n
LEFT JOIN kg_claims c ON n.id = c.subject_id AND c.predicate = 'has_tag'
LEFT JOIN kg_nodes t ON c.object_id = t.id
WHERE n.user_id = 1 
  AND n.node_type = 'IMAGE'
GROUP BY n.id
ORDER BY n.created_at DESC
LIMIT 50;

Get claims for an image with evidence

SELECT c.*, 
       e.bbox, e.confidence as evidence_confidence,
       obj.canonical_name as object_name,
       obj.node_type as object_type
FROM kg_claims c
LEFT JOIN kg_evidence e ON c.id = e.claim_id
LEFT JOIN kg_nodes obj ON c.object_id = obj.id
WHERE c.subject_id = 'image-uuid-here'
  AND c.status != 'rejected'
ORDER BY c.confidence DESC;

Find person across images

SELECT DISTINCT n.id as image_id, 
       n.properties->>'file_path' as file_path,
       c.confidence
FROM kg_claims c
JOIN kg_nodes n ON c.subject_id = n.id
WHERE c.object_id = 'person-uuid-here'
  AND c.predicate = 'depicts_person'
  AND n.node_type = 'IMAGE'
ORDER BY c.confidence DESC;

Performance Considerations

  1. JSONB Indexes: Create GIN indexes for frequently queried JSONB fields:

    CREATE INDEX ix_nodes_properties_photobook 
ON kg_nodes USING GIN ((properties->'photobook_id'));

  2. Partial Indexes: For status-filtered queries:

    CREATE INDEX ix_claims_active 
ON kg_claims (subject_id, predicate) 
WHERE status NOT IN ('rejected', 'superseded');

  3. Connection Pooling: Use appropriate pool sizes for concurrent requests.

  4. Cascade Deletes: Claims and evidence should cascade on node deletion.