Database Management

# Core Schema Design

## Tables Overview
├── valuations (main data)
│   ├── id (UUID, PK)
│   ├── product_data (JSONB)
│   ├── result (JSONB)
│   ├── confidence (DECIMAL)
│   ├── created_at (TIMESTAMPTZ)
│   └── user_id (FK → users)
│
├── users
│   ├── id (UUID, PK)
│   ├── email (VARCHAR, UNIQUE)
│   ├── api_key_hash (VARCHAR)
│   └── tier (ENUM)
│
├── api_keys
│   ├── id (UUID, PK)
│   ├── key_hash (VARCHAR)
│   ├── user_id (FK → users)
│   ├── scopes (ARRAY)
│   └── expires_at (TIMESTAMPTZ)
│
└── audit_logs
    ├── id (BIGSERIAL, PK)
    ├── action (VARCHAR)
    ├── actor_id (UUID)
    ├── resource (JSONB)
    └── timestamp (TIMESTAMPTZ)

## Indexing Strategy
- B-tree: Primary keys, foreign keys
- GIN: JSONB columns for search
- Partial: Active records only
- Covering: Common query patterns

# SQLAlchemy 2.0 Models

from sqlalchemy import Column, ForeignKey
from sqlalchemy.dialects.postgresql import (
    UUID, JSONB, ARRAY
)
from sqlalchemy.orm import relationship

class Valuation(Base):
    __tablename__ = "valuations"
    
    id = Column(UUID, primary_key=True)
    product_data = Column(JSONB, nullable=False)
    result = Column(JSONB)
    confidence = Column(Numeric(3, 2))
    status = Column(
        Enum(ValuationStatus),
        default=ValuationStatus.PENDING
    )
    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now()
    )
    user_id = Column(UUID, ForeignKey("users.id"))
    
    # Relationships
    user = relationship("User", back_populates="valuations")
    
    # Indexes
    __table_args__ = (
        Index("ix_valuations_user_created",
              "user_id", "created_at"),
        Index("ix_valuations_status",
              "status",
              postgresql_where=status != "completed"),
    )