| """SQLAlchemy models for Sheetly.""" |
|
|
| from __future__ import annotations |
|
|
| from datetime import datetime |
| from typing import Any, Optional |
|
|
| from sqlalchemy import ( |
| JSON, |
| Boolean, |
| DateTime, |
| Float, |
| ForeignKey, |
| Integer, |
| String, |
| Text, |
| ) |
| from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column |
|
|
| from app.timeutil import utc_now |
|
|
|
|
| class Base(DeclarativeBase): |
| """Declarative base.""" |
|
|
|
|
| class Batch(Base): |
| """Upload batch job.""" |
|
|
| __tablename__ = "batches" |
|
|
| id: Mapped[str] = mapped_column(String(36), primary_key=True) |
| status: Mapped[str] = mapped_column( |
| String(32), default="pending" |
| ) |
| total: Mapped[int] = mapped_column(Integer, default=0) |
| processed: Mapped[int] = mapped_column(Integer, default=0) |
| failed: Mapped[int] = mapped_column(Integer, default=0) |
| created_at: Mapped[datetime] = mapped_column( |
| DateTime, default=utc_now |
| ) |
| finished_at: Mapped[Optional[datetime]] = mapped_column( |
| DateTime, nullable=True |
| ) |
|
|
|
|
| class Document(Base): |
| """Processed document record.""" |
|
|
| __tablename__ = "documents" |
|
|
| id: Mapped[str] = mapped_column(String(36), primary_key=True) |
| batch_id: Mapped[Optional[str]] = mapped_column( |
| String(36), ForeignKey("batches.id"), nullable=True |
| ) |
| filename: Mapped[str] = mapped_column(String(512)) |
| file_path: Mapped[str] = mapped_column(String(1024)) |
| mime_type: Mapped[str] = mapped_column(String(128), default="") |
| doc_type: Mapped[str] = mapped_column( |
| String(64), default="other" |
| ) |
| status: Mapped[str] = mapped_column( |
| String(32), default="queued" |
| ) |
| language: Mapped[str] = mapped_column(String(8), default="") |
| extracted: Mapped[Optional[dict[str, Any]]] = mapped_column( |
| JSON, nullable=True |
| ) |
| confidence: Mapped[Optional[dict[str, Any]]] = mapped_column( |
| JSON, nullable=True |
| ) |
| issues: Mapped[Optional[list[Any]]] = mapped_column( |
| JSON, nullable=True |
| ) |
| is_duplicate: Mapped[bool] = mapped_column( |
| Boolean, default=False |
| ) |
| duplicate_of: Mapped[Optional[str]] = mapped_column( |
| String(36), nullable=True |
| ) |
| error: Mapped[Optional[str]] = mapped_column( |
| Text, nullable=True |
| ) |
| avg_confidence: Mapped[Optional[float]] = mapped_column( |
| Float, nullable=True |
| ) |
| created_at: Mapped[datetime] = mapped_column( |
| DateTime, default=utc_now |
| ) |
| updated_at: Mapped[datetime] = mapped_column( |
| DateTime, default=utc_now, onupdate=utc_now |
| ) |
|
|
|
|
| class DuplicateIndex(Base): |
| """Index for invoice duplicate detection.""" |
|
|
| __tablename__ = "duplicate_index" |
|
|
| id: Mapped[int] = mapped_column( |
| Integer, primary_key=True, autoincrement=True |
| ) |
| supplier: Mapped[str] = mapped_column(String(256), index=True) |
| invoice_number: Mapped[str] = mapped_column( |
| String(128), index=True |
| ) |
| document_id: Mapped[str] = mapped_column(String(36)) |
| created_at: Mapped[datetime] = mapped_column( |
| DateTime, default=utc_now |
| ) |
|
|
