NoteStack-Test / backend /app /models /content.py
Arslan1997's picture
added blogs
34b0226
Raw History Blame
13.7 kB
import uuid
from datetime import datetime
from sqlalchemy import (
JSON,
BigInteger,
Boolean,
DateTime,
Float,
ForeignKey,
Index,
Integer,
String,
Text,
UniqueConstraint,
Uuid,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db import Base
from app.models.base import IdMixin, TimestampMixin
def _ws_fk() -> Mapped[uuid.UUID]:
return mapped_column(Uuid, ForeignKey("workspaces.id", ondelete="CASCADE"), index=True)
class Source(IdMixin, TimestampMixin, Base):
__tablename__ = "sources"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
feed_url: Mapped[str] = mapped_column(String(1000))
site_url: Mapped[str | None] = mapped_column(String(1000))
platform: Mapped[str] = mapped_column(String(20), default="rss") # substack | ghost | medium | rss | url
title: Mapped[str | None] = mapped_column(String(500))
sync_status: Mapped[str] = mapped_column(String(20), default="pending") # pending | syncing | ok | error
sync_error: Mapped[str | None] = mapped_column(Text)
last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class Document(IdMixin, TimestampMixin, Base):
__tablename__ = "documents"
__table_args__ = (UniqueConstraint("source_id", "url"),)
workspace_id: Mapped[uuid.UUID] = _ws_fk()
source_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("sources.id", ondelete="CASCADE"))
title: Mapped[str] = mapped_column(String(1000))
url: Mapped[str] = mapped_column(String(1000))
published_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
raw_html_key: Mapped[str | None] = mapped_column(String(500)) # R2 key
# Corpus relative path of the clean markdown file, e.g. sources/ada-substack-com/2026-03-01-pricing.md
path: Mapped[str | None] = mapped_column(String(500), index=True)
clean_text: Mapped[str] = mapped_column(Text, default="")
content_hash: Mapped[str | None] = mapped_column(String(64))
metadata_json: Mapped[dict] = mapped_column(JSON, default=dict)
# Resurfacing: 0..1 from the EvergreenScore module (reason and angle live in metadata_json)
evergreen_score: Mapped[float | None] = mapped_column(Float)
last_resurfaced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class Notebook(IdMixin, TimestampMixin, Base):
__tablename__ = "notebooks"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
title: Mapped[str] = mapped_column(String(300))
description: Mapped[str | None] = mapped_column(Text)
summary: Mapped[str | None] = mapped_column(Text)
class NotebookDocument(Base):
__tablename__ = "notebook_documents"
notebook_id: Mapped[uuid.UUID] = mapped_column(
Uuid, ForeignKey("notebooks.id", ondelete="CASCADE"), primary_key=True
)
document_id: Mapped[uuid.UUID] = mapped_column(
Uuid, ForeignKey("documents.id", ondelete="CASCADE"), primary_key=True
)
class VoiceProfile(IdMixin, TimestampMixin, Base):
__tablename__ = "voice_profiles"
workspace_id: Mapped[uuid.UUID] = mapped_column(
Uuid, ForeignKey("workspaces.id", ondelete="CASCADE"), unique=True
)
profile_json: Mapped[dict] = mapped_column(JSON, default=dict)
sample_doc_ids: Mapped[list] = mapped_column(JSON, default=list)
host_voices: Mapped[dict] = mapped_column(JSON, default=dict) # {"host_a": voice_id, "host_b": voice_id}
class VoiceConsent(IdMixin, TimestampMixin, Base):
"""Required before any voice cloning. The recorded sample lives in R2."""
__tablename__ = "voice_consents"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
user_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("users.id"))
sample_key: Mapped[str] = mapped_column(String(500))
consent_text: Mapped[str] = mapped_column(Text)
ip_address: Mapped[str | None] = mapped_column(String(64))
elevenlabs_voice_id: Mapped[str | None] = mapped_column(String(100))
revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class Chat(IdMixin, TimestampMixin, Base):
__tablename__ = "chats"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
notebook_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("notebooks.id", ondelete="CASCADE"))
title: Mapped[str | None] = mapped_column(String(300))
class Message(IdMixin, TimestampMixin, Base):
__tablename__ = "messages"
chat_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("chats.id", ondelete="CASCADE"), index=True)
role: Mapped[str] = mapped_column(String(20)) # user | assistant
content: Mapped[str] = mapped_column(Text)
citations: Mapped[list["Citation"]] = relationship(cascade="all, delete-orphan")
class Citation(IdMixin, Base):
__tablename__ = "citations"
message_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("messages.id", ondelete="CASCADE"))
document_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("documents.id", ondelete="CASCADE"))
marker: Mapped[int] = mapped_column(Integer) # [1], [2] in the answer text
path: Mapped[str] = mapped_column(String(500))
line_start: Mapped[int] = mapped_column(Integer)
line_end: Mapped[int] = mapped_column(Integer)
span: Mapped[str | None] = mapped_column(Text) # verified text read back from the file
class Artifact(IdMixin, TimestampMixin, Base):
__tablename__ = "artifacts"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
notebook_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("notebooks.id", ondelete="SET NULL"))
document_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("documents.id", ondelete="SET NULL"))
# summary | audio_overview | video | thread | linkedin | notes | quote_card | carousel | seo
type: Mapped[str] = mapped_column(String(30))
status: Mapped[str] = mapped_column(String(20), default="pending")
content_json: Mapped[dict] = mapped_column(JSON, default=dict)
storage_key: Mapped[str | None] = mapped_column(String(500))
class Job(IdMixin, TimestampMixin, Base):
"""Covers ingest, tts and render jobs. kind decides which fields matter.
This table is also the queue: workers claim queued rows with FOR UPDATE SKIP LOCKED
(see app/worker.py), so no separate broker is needed.
"""
__tablename__ = "jobs"
__table_args__ = (Index("ix_jobs_queue", "status", "run_after", "created_at"),)
workspace_id: Mapped[uuid.UUID] = _ws_fk()
kind: Mapped[str] = mapped_column(String(30)) # ingest | tts | render | generate
status: Mapped[str] = mapped_column(String(20), default="queued") # queued | running | done | failed
progress: Mapped[float] = mapped_column(Float, default=0.0)
message: Mapped[str | None] = mapped_column(String(500))
error: Mapped[str | None] = mapped_column(Text)
params: Mapped[dict] = mapped_column(JSON, default=dict)
result: Mapped[dict] = mapped_column(JSON, default=dict)
artifact_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("artifacts.id", ondelete="SET NULL"))
cost_usd: Mapped[float] = mapped_column(Float, default=0.0)
finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
# Queue bookkeeping
attempts: Mapped[int] = mapped_column(Integer, default=0)
max_attempts: Mapped[int] = mapped_column(Integer, default=3)
run_after: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # retry backoff
locked_by: Mapped[str | None] = mapped_column(String(100)) # worker id while running
heartbeat_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # stale detection
class Upload(IdMixin, TimestampMixin, Base):
__tablename__ = "uploads"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
user_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("users.id"))
key: Mapped[str] = mapped_column(String(500), unique=True)
filename: Mapped[str] = mapped_column(String(300))
content_type: Mapped[str] = mapped_column(String(100))
size_bytes: Mapped[int | None] = mapped_column(BigInteger)
status: Mapped[str] = mapped_column(String(20), default="pending") # pending | complete
class CalendarItem(IdMixin, TimestampMixin, Base):
__tablename__ = "calendar_items"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
artifact_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("artifacts.id", ondelete="CASCADE"))
document_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("documents.id", ondelete="SET NULL"))
platform: Mapped[str] = mapped_column(String(30)) # x | linkedin | bluesky | substack_notes
scheduled_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
# scheduled | publishing | posted | reminded | failed | draft
status: Mapped[str] = mapped_column(String(20), default="scheduled")
content: Mapped[str] = mapped_column(Text, default="")
thread: Mapped[list] = mapped_column(JSON, default=list) # extra posts after the first, for threads
social_account_id: Mapped[uuid.UUID | None] = mapped_column(
Uuid, ForeignKey("social_accounts.id", ondelete="SET NULL")
)
remind_by_email: Mapped[bool] = mapped_column(Boolean, default=False)
external_id: Mapped[str | None] = mapped_column(String(200))
external_url: Mapped[str | None] = mapped_column(String(1000))
posted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
error: Mapped[str | None] = mapped_column(Text)
attempts: Mapped[int] = mapped_column(Integer, default=0)
metrics: Mapped[dict] = mapped_column(JSON, default=dict) # likes, reposts, replies, impressions
class SocialAccount(IdMixin, TimestampMixin, Base):
"""A connected X, LinkedIn or Bluesky account. Tokens are Fernet encrypted (app/services/crypto.py)."""
__tablename__ = "social_accounts"
__table_args__ = (UniqueConstraint("workspace_id", "platform", "external_id"),)
workspace_id: Mapped[uuid.UUID] = _ws_fk()
platform: Mapped[str] = mapped_column(String(20))
handle: Mapped[str] = mapped_column(String(200))
external_id: Mapped[str] = mapped_column(String(200))
access_token: Mapped[str] = mapped_column(Text)
refresh_token: Mapped[str | None] = mapped_column(Text)
expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
scopes: Mapped[str | None] = mapped_column(String(500))
status: Mapped[str] = mapped_column(String(20), default="active") # active | expired | revoked
avatar_url: Mapped[str | None] = mapped_column(String(1000))
class Topic(IdMixin, TimestampMixin, Base):
__tablename__ = "topics"
__table_args__ = (UniqueConstraint("workspace_id", "slug"),)
workspace_id: Mapped[uuid.UUID] = _ws_fk()
name: Mapped[str] = mapped_column(String(200))
slug: Mapped[str] = mapped_column(String(200))
summary: Mapped[str | None] = mapped_column(Text)
post_count: Mapped[int] = mapped_column(Integer, default=0)
class DocumentTopic(Base):
__tablename__ = "document_topics"
document_id: Mapped[uuid.UUID] = mapped_column(
Uuid, ForeignKey("documents.id", ondelete="CASCADE"), primary_key=True
)
topic_id: Mapped[uuid.UUID] = mapped_column(Uuid, ForeignKey("topics.id", ondelete="CASCADE"), primary_key=True)
weight: Mapped[float] = mapped_column(Float, default=1.0)
class TrackedLink(IdMixin, TimestampMixin, Base):
__tablename__ = "tracked_links"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
slug: Mapped[str] = mapped_column(String(32), unique=True)
target_url: Mapped[str] = mapped_column(String(2000))
calendar_item_id: Mapped[uuid.UUID | None] = mapped_column(
Uuid, ForeignKey("calendar_items.id", ondelete="SET NULL")
)
class EngagementEvent(IdMixin, TimestampMixin, Base):
__tablename__ = "engagement_events"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
tracked_link_id: Mapped[uuid.UUID | None] = mapped_column(
Uuid, ForeignKey("tracked_links.id", ondelete="CASCADE")
)
kind: Mapped[str] = mapped_column(String(30)) # click | like | repost | reply | subscribe
value: Mapped[float] = mapped_column(Float, default=1.0)
metadata_json: Mapped[dict] = mapped_column(JSON, default=dict)
class DspyTrace(IdMixin, TimestampMixin, Base):
__tablename__ = "dspy_traces"
workspace_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("workspaces.id", ondelete="SET NULL"))
module: Mapped[str] = mapped_column(String(60), index=True)
model: Mapped[str] = mapped_column(String(100))
inputs: Mapped[dict] = mapped_column(JSON, default=dict)
outputs: Mapped[dict] = mapped_column(JSON, default=dict)
score: Mapped[float | None] = mapped_column(Float)
feedback: Mapped[str | None] = mapped_column(Text)
# Only usable for optimization when the workspace opted in.
training_allowed: Mapped[bool] = mapped_column(Boolean, default=False)
class UsageEvent(IdMixin, TimestampMixin, Base):
"""Metering: LLM tokens, TTS characters, render seconds. One row per billable call."""
__tablename__ = "usage_events"
workspace_id: Mapped[uuid.UUID] = _ws_fk()
job_id: Mapped[uuid.UUID | None] = mapped_column(Uuid, ForeignKey("jobs.id", ondelete="SET NULL"))
kind: Mapped[str] = mapped_column(String(20)) # llm | tts | render
provider: Mapped[str] = mapped_column(String(40))
model: Mapped[str | None] = mapped_column(String(100))
quantity: Mapped[float] = mapped_column(Float) # tokens, characters or seconds
unit: Mapped[str] = mapped_column(String(20))
cost_usd: Mapped[float] = mapped_column(Float, default=0.0)