backend/db.py (10555 bytes)
1 """Persistence. SQLite on localhost, Postgres in production - same models.""" 2 3 import enum 4 import secrets 5 from datetime import datetime, timezone 6 7 from sqlalchemy import ( 8 BigInteger, 9 DateTime, 10 Enum, 11 Float, 12 ForeignKey, 13 Integer, 14 String, 15 Text, 16 create_engine, 17 inspect, 18 text, 19 ) 20 from sqlalchemy.orm import ( 21 DeclarativeBase, 22 Mapped, 23 mapped_column, 24 relationship, 25 sessionmaker, 26 ) 27 28 from .settings import settings 29 30 31 def utcnow() -> datetime: 32 return datetime.now(timezone.utc) 33 34 35 def new_id(prefix: str) -> str: 36 return f"{prefix}_{secrets.token_hex(8)}" 37 38 39 class Base(DeclarativeBase): 40 pass 41 42 43 class JobStatus(str, enum.Enum): 44 # Uploaded and analysed, but not started: the user still gets to correct the 45 # detected language before committing to an hours-long alignment. 46 draft = "draft" 47 queued = "queued" 48 running = "running" 49 succeeded = "succeeded" 50 failed = "failed" 51 canceled = "canceled" 52 53 54 class Account(Base): 55 """One row per identified user. 56 57 Every visitor starts as an anonymous row keyed by a cookie. It becomes a 58 real account the moment an email is attached - by following a sign-in link 59 or by paying, since Stripe collects one at checkout. 60 """ 61 62 __tablename__ = "accounts" 63 64 id: Mapped[str] = mapped_column( 65 String(64), primary_key=True, default=lambda: new_id("acct") 66 ) 67 # Opaque token the browser stores; becomes a real session subject later. 68 device_token: Mapped[str] = mapped_column(String(64), unique=True, index=True) 69 # Lowercased. Unique, so an email always resolves to exactly one account. 70 email: Mapped[str | None] = mapped_column( 71 String(320), nullable=True, unique=True, index=True 72 ) 73 stripe_customer_id: Mapped[str | None] = mapped_column( 74 String(64), nullable=True, index=True 75 ) 76 # Paid conversions in hand. One credit = one book with every output. 77 purchased_credits: Mapped[int] = mapped_column(Integer, default=0) 78 79 # The unlimited plan. Status is Stripe's own word for it (active, past_due, 80 # canceled...); period_end is when the paid-for time runs out. 81 subscription_id: Mapped[str | None] = mapped_column(String(64), nullable=True) 82 subscription_status: Mapped[str | None] = mapped_column(String(32), nullable=True) 83 subscription_period_end: Mapped[datetime | None] = mapped_column( 84 DateTime(timezone=True), nullable=True 85 ) 86 87 # Set when this anonymous row was folded into a signed-in account. A 88 # payment can finish without a browser attached (the webhook), so the 89 # cookie is re-pointed lazily, the next time this device shows up. 90 merged_into: Mapped[str | None] = mapped_column(String(64), nullable=True) 91 created_at: Mapped[datetime] = mapped_column( 92 DateTime(timezone=True), default=utcnow 93 ) 94 95 jobs: Mapped[list["Job"]] = relationship(back_populates="account") 96 97 @property 98 def signed_in(self) -> bool: 99 """Whether this is a real account rather than an anonymous cookie. 100 101 Gates the features that cost us money or need someone to bill. An 102 anonymous visitor still gets their free book; they just cannot attach 103 a custom cover. 104 """ 105 return bool(self.email) 106 107 108 class Job(Base): 109 __tablename__ = "jobs" 110 111 id: Mapped[str] = mapped_column( 112 String(64), primary_key=True, default=lambda: new_id("job") 113 ) 114 account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True) 115 116 status: Mapped[JobStatus] = mapped_column( 117 Enum(JobStatus), default=JobStatus.queued, index=True 118 ) 119 language: Mapped[str] = mapped_column(String(16)) 120 splitter: Mapped[str] = mapped_column(String(16)) 121 model: Mapped[str] = mapped_column(String(32)) 122 123 # Display name. For a per-chapter audiobook this is "01.mp3 + 43 more". 124 audio_filename: Mapped[str] = mapped_column(String(512)) 125 text_filename: Mapped[str] = mapped_column(String(512)) 126 # 1 for a single file; higher when the book arrived as per-chapter parts 127 # that get merged before alignment. 128 audio_parts: Mapped[int] = mapped_column(Integer, default=1) 129 # Custom cover art for the rendered video. Requires a signed-in account; 130 # anonymous jobs fall back to the epub's own cover. 131 cover_filename: Mapped[str | None] = mapped_column(String(512), nullable=True) 132 audio_bytes: Mapped[int] = mapped_column(BigInteger, default=0) 133 audio_duration_seconds: Mapped[float | None] = mapped_column(Float, nullable=True) 134 135 progress: Mapped[float] = mapped_column(Float, default=0.0) # 0..1 136 stage: Mapped[str] = mapped_column(String(128), default="queued") 137 error: Mapped[str | None] = mapped_column(Text, nullable=True) 138 139 # Not used from 2.2 on. It counted jobs in the free window of earlier versions. 140 billed: Mapped[int] = mapped_column(Integer, default=0) 141 # "free": the job ran in the visitor's browser. "cloud": it ran on this 142 # server and took a credit. (Rows from before 2.2 can say "youtube".) 143 tier: Mapped[str] = mapped_column( 144 String(16), default="free", server_default=text("'free'") 145 ) 146 # 1 when the work happens in the visitor's browser and the server only 147 # keeps the books: nothing to queue, nothing to resume after a restart. 148 local: Mapped[int] = mapped_column(Integer, default=0, server_default=text("0")) 149 # 1 if a credit was spent on this job, so a failed run can hand it back. 150 credit_spent: Mapped[int] = mapped_column( 151 Integer, default=0, server_default=text("0") 152 ) 153 154 created_at: Mapped[datetime] = mapped_column( 155 DateTime(timezone=True), default=utcnow 156 ) 157 started_at: Mapped[datetime | None] = mapped_column( 158 DateTime(timezone=True), nullable=True 159 ) 160 finished_at: Mapped[datetime | None] = mapped_column( 161 DateTime(timezone=True), nullable=True 162 ) 163 164 account: Mapped[Account] = relationship(back_populates="jobs") 165 artifacts: Mapped[list["Artifact"]] = relationship( 166 back_populates="job", cascade="all, delete-orphan" 167 ) 168 169 170 class Artifact(Base): 171 """A downloadable output of a job: the subtitles, metadata and run log.""" 172 173 __tablename__ = "artifacts" 174 175 id: Mapped[str] = mapped_column( 176 String(64), primary_key=True, default=lambda: new_id("art") 177 ) 178 job_id: Mapped[str] = mapped_column(ForeignKey("jobs.id"), index=True) 179 kind: Mapped[str] = mapped_column(String(32)) # srt | metadata | log 180 filename: Mapped[str] = mapped_column(String(512)) 181 # Opaque storage key, not a filesystem path. 182 storage_key: Mapped[str] = mapped_column(String(1024)) 183 size_bytes: Mapped[int] = mapped_column(BigInteger, default=0) 184 created_at: Mapped[datetime] = mapped_column( 185 DateTime(timezone=True), default=utcnow 186 ) 187 188 job: Mapped[Job] = relationship(back_populates="artifacts") 189 190 191 class LoginToken(Base): 192 """A one-time sign-in link. Only the hash is stored, so a leaked database 193 cannot be replayed into anyone's account.""" 194 195 __tablename__ = "login_tokens" 196 197 id: Mapped[str] = mapped_column( 198 String(64), primary_key=True, default=lambda: new_id("login") 199 ) 200 token_hash: Mapped[str] = mapped_column(String(64), unique=True, index=True) 201 email: Mapped[str] = mapped_column(String(320), index=True) 202 # The device that asked, so its anonymous jobs follow it into the account. 203 account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True) 204 created_at: Mapped[datetime] = mapped_column( 205 DateTime(timezone=True), default=utcnow 206 ) 207 expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True)) 208 used_at: Mapped[datetime | None] = mapped_column( 209 DateTime(timezone=True), nullable=True 210 ) 211 212 213 class Purchase(Base): 214 """Ledger of completed checkouts. 215 216 The unique session id is what makes fulfilment idempotent: Stripe delivers 217 a payment twice (the webhook and the browser's return trip) and retries 218 webhooks freely, and each of those must credit the account exactly once. 219 """ 220 221 __tablename__ = "purchases" 222 223 id: Mapped[str] = mapped_column( 224 String(64), primary_key=True, default=lambda: new_id("buy") 225 ) 226 account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True) 227 plan_id: Mapped[str] = mapped_column(String(32)) 228 credits: Mapped[int] = mapped_column(Integer, default=0) 229 amount_cents: Mapped[int] = mapped_column(Integer, default=0) 230 currency: Mapped[str] = mapped_column(String(8), default="usd") 231 stripe_session_id: Mapped[str] = mapped_column(String(128), unique=True, index=True) 232 created_at: Mapped[datetime] = mapped_column( 233 DateTime(timezone=True), default=utcnow 234 ) 235 236 237 _is_sqlite = settings.resolved_database_url.startswith("sqlite") 238 239 _engine = create_engine( 240 settings.resolved_database_url, 241 # check_same_thread only matters for SQLite plus our worker threads. 242 connect_args={"check_same_thread": False} if _is_sqlite else {}, 243 pool_pre_ping=True, 244 ) 245 SessionLocal = sessionmaker(bind=_engine, expire_on_commit=False) 246 247 248 def _add_missing_columns() -> None: 249 """Bring an existing database up to the current models, additively. 250 251 create_all() makes missing tables but never touches one that exists, so a 252 deploy that adds a column would otherwise need hand-run SQL on the server. 253 This only ever ADDs a column - anything destructive is still a deliberate, 254 manual migration. 255 """ 256 insp = inspect(_engine) 257 with _engine.begin() as conn: 258 for table in Base.metadata.sorted_tables: 259 if not insp.has_table(table.name): 260 continue 261 have = {c["name"] for c in insp.get_columns(table.name)} 262 for col in table.columns: 263 if col.name in have: 264 continue 265 ddl = ( 266 f"ALTER TABLE {table.name} ADD COLUMN {col.name} " 267 f"{col.type.compile(dialect=_engine.dialect)}" 268 ) 269 if col.server_default is not None: 270 ddl += f" DEFAULT {col.server_default.arg.text}" 271 conn.execute(text(ddl)) 272 273 # A column added above cannot carry its index along with it. 274 existing = {i["name"] for i in insp.get_indexes(table.name)} 275 for index in table.indexes: 276 if index.name not in existing: 277 index.create(conn) 278 279 280 def init_db() -> None: 281 Base.metadata.create_all(_engine) 282 _add_missing_columns()