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