| 1 | package main |
| 2 | |
| 3 | import ( |
| 4 | "database/sql" |
| 5 | "time" |
| 6 | ) |
| 7 | |
| 8 | // cloudDB is the provisioner's state: which callers (home servers) may |
| 9 | // provision, and which spaces each caller's users already have (the quota |
| 10 | // ledger). It never holds message data, keys, or anything about a space beyond |
| 11 | // its hostname — the requester ids are the home server's opaque random user |
| 12 | // ids, meaningless outside it. |
| 13 | type cloudDB struct{ sql *sql.DB } |
| 14 | |
| 15 | func openCloudDB(path string) (*cloudDB, error) { |
| 16 | d, err := sql.Open("sqlite", path) |
| 17 | if err != nil { |
| 18 | return nil, err |
| 19 | } |
| 20 | // One writer at a time keeps WAL happy (mirrors openDB). |
| 21 | d.SetMaxOpenConns(1) |
| 22 | for _, p := range []string{ |
| 23 | "PRAGMA journal_mode=WAL", |
| 24 | "PRAGMA busy_timeout=5000", |
| 25 | "PRAGMA synchronous=NORMAL", |
| 26 | } { |
| 27 | if _, err := d.Exec(p); err != nil { |
| 28 | return nil, err |
| 29 | } |
| 30 | } |
| 31 | db := &cloudDB{sql: d} |
| 32 | if err := db.migrate(); err != nil { |
| 33 | return nil, err |
| 34 | } |
| 35 | return db, nil |
| 36 | } |
| 37 | |
| 38 | // migrate is additive, like every other schema in this repo, so deploying new |
| 39 | // code over an existing DB is safe. |
| 40 | func (db *cloudDB) migrate() error { |
| 41 | if _, err := db.sql.Exec(` |
| 42 | -- Who may provision: bearer tokens minted for home servers (cf. the push |
| 43 | -- relay's relay_tokens). Stored hashed — a stolen DB yields no live credential. |
| 44 | CREATE TABLE IF NOT EXISTS caller_tokens ( |
| 45 | id TEXT PRIMARY KEY, |
| 46 | label TEXT NOT NULL, |
| 47 | token_hash TEXT NOT NULL UNIQUE, |
| 48 | created_at INTEGER NOT NULL, |
| 49 | last_used_at INTEGER |
| 50 | ); |
| 51 | -- Provisioned spaces: the quota ledger AND the id source for a space's hostname. |
| 52 | -- The integer id is the primary key; the subdomain label is sqids(id) (see |
| 53 | -- cloud.go), so the hostname encodes the id reversibly. host is assigned only |
| 54 | -- after the id is known (it's NULL while a create is in flight), so it can't be |
| 55 | -- the PK. requester is the caller's opaque user id (a home server's random uid). |
| 56 | CREATE TABLE IF NOT EXISTS spaces ( |
| 57 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 58 | host TEXT UNIQUE, |
| 59 | caller_id TEXT NOT NULL, |
| 60 | requester TEXT NOT NULL, |
| 61 | created_at INTEGER NOT NULL |
| 62 | ); |
| 63 | -- Every allocation, forever (rows here survive space deletion): the global |
| 64 | -- creation budget counts THIS, so create-and-delete can't reset the throttle. |
| 65 | CREATE TABLE IF NOT EXISTS creates_log ( |
| 66 | created_at INTEGER NOT NULL |
| 67 | );`); err != nil { |
| 68 | return err |
| 69 | } |
| 70 | if err := db.migrateSpacesID(); err != nil { |
| 71 | return err |
| 72 | } |
| 73 | if err := db.migrateBilling(); err != nil { |
| 74 | return err |
| 75 | } |
| 76 | if err := db.migrateMeter(); err != nil { |
| 77 | return err |
| 78 | } |
| 79 | if err := db.migrateUsage(); err != nil { |
| 80 | return err |
| 81 | } |
| 82 | return db.migratePool() |
| 83 | } |
| 84 | |
| 85 | // migrateUsage (#857): the per-space usage ledger — quantities only (token |
| 86 | // counts, request counts, run counts), never content — plus the meter token |
| 87 | // each instance authenticates its reports with. The token itself is pushed |
| 88 | // into the instance env (LCHAT_METER_URL/_TOKEN) by the sweep; only its hash |
| 89 | // lives here, same posture as caller tokens. |
| 90 | func (db *cloudDB) migrateUsage() error { |
| 91 | if has, err := db.spacesHasColumn("meter_token_hash"); err != nil { |
| 92 | return err |
| 93 | } else if !has { |
| 94 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN meter_token_hash TEXT NOT NULL DEFAULT ''`); err != nil { |
| 95 | return err |
| 96 | } |
| 97 | } |
| 98 | _, err := db.sql.Exec(`CREATE TABLE IF NOT EXISTS usage ( |
| 99 | space_id INTEGER NOT NULL, |
| 100 | day TEXT NOT NULL, |
| 101 | model TEXT NOT NULL DEFAULT '', |
| 102 | dimension TEXT NOT NULL, |
| 103 | quantity INTEGER NOT NULL DEFAULT 0, |
| 104 | PRIMARY KEY (space_id, day, model, dimension) |
| 105 | ); |
| 106 | -- Applied report ids — the dedupe ledger that makes ingest idempotent. A |
| 107 | -- sidecar resends a report verbatim when an ack is lost; the id tells a |
| 108 | -- replay from new usage. Pruned after a retention window (the sidecar |
| 109 | -- retries on a minutes scale, so days of memory is generous). |
| 110 | CREATE TABLE IF NOT EXISTS usage_reports ( |
| 111 | space_id INTEGER NOT NULL, |
| 112 | report_id TEXT NOT NULL, |
| 113 | applied_at INTEGER NOT NULL, |
| 114 | PRIMARY KEY (space_id, report_id) |
| 115 | )`) |
| 116 | return err |
| 117 | } |
| 118 | |
| 119 | // setMeterTokenHash records the hash of the meter token last pushed to a |
| 120 | // space's instance. Overwriting is the rotation path: last push wins. |
| 121 | func (db *cloudDB) setMeterTokenHash(id int64, hash string) error { |
| 122 | _, err := db.sql.Exec(`UPDATE spaces SET meter_token_hash = ? WHERE id = ?`, hash, id) |
| 123 | return err |
| 124 | } |
| 125 | |
| 126 | // spaceByMeterToken resolves a usage report's bearer token to its space. An |
| 127 | // empty stored hash never matches (hashToken of any presented token is |
| 128 | // non-empty), so unmetered spaces can't be reported against. |
| 129 | func (db *cloudDB) spaceByMeterToken(token string) (int64, bool) { |
| 130 | var id int64 |
| 131 | err := db.sql.QueryRow( |
| 132 | `SELECT id FROM spaces WHERE meter_token_hash = ? AND meter_token_hash != ''`, |
| 133 | hashToken(token)).Scan(&id) |
| 134 | return id, err == nil |
| 135 | } |
| 136 | |
| 137 | // addUsage folds one report into the ledger — additive upserts, so repeated |
| 138 | // partial-day reports accumulate instead of clobbering. Idempotent on |
| 139 | // (space, report id): the report row and the quantity upserts share one |
| 140 | // transaction, so a replay of an already-applied report (ack lost after |
| 141 | // commit) is recognized and dropped whole. |
| 142 | func (db *cloudDB) addUsage(spaceID int64, reportID string, rows []botUsageRow) error { |
| 143 | tx, err := db.sql.Begin() |
| 144 | if err != nil { |
| 145 | return err |
| 146 | } |
| 147 | defer tx.Rollback() |
| 148 | res, err := tx.Exec(`INSERT INTO usage_reports(space_id, report_id, applied_at) |
| 149 | VALUES(?, ?, ?) ON CONFLICT(space_id, report_id) DO NOTHING`, |
| 150 | spaceID, reportID, time.Now().Unix()) |
| 151 | if err != nil { |
| 152 | return err |
| 153 | } |
| 154 | if n, err := res.RowsAffected(); err != nil { |
| 155 | return err |
| 156 | } else if n == 0 { |
| 157 | return nil // replay of an applied report — already counted |
| 158 | } |
| 159 | for _, r := range rows { |
| 160 | if _, err := tx.Exec(`INSERT INTO usage(space_id, day, model, dimension, quantity) |
| 161 | VALUES(?, ?, ?, ?, ?) |
| 162 | ON CONFLICT(space_id, day, model, dimension) DO UPDATE SET quantity = quantity + excluded.quantity`, |
| 163 | spaceID, r.Day, r.Model, r.Dimension, r.Quantity); err != nil { |
| 164 | return err |
| 165 | } |
| 166 | } |
| 167 | return tx.Commit() |
| 168 | } |
| 169 | |
| 170 | // pruneUsageReports drops applied-report ids older than the cutoff. The |
| 171 | // sidecar's retry horizon is minutes, so days of dedupe memory is generous. |
| 172 | func (db *cloudDB) pruneUsageReports(cutoff int64) error { |
| 173 | _, err := db.sql.Exec(`DELETE FROM usage_reports WHERE applied_at < ?`, cutoff) |
| 174 | return err |
| 175 | } |
| 176 | |
| 177 | // usageForMonth reads one space's ledger rows for a UTC month ("2006-01") — |
| 178 | // the budget's allowance window (cloud_budget.go). |
| 179 | func (db *cloudDB) usageForMonth(spaceID int64, month string) ([]botUsageRow, error) { |
| 180 | rows, err := db.sql.Query( |
| 181 | `SELECT day, model, dimension, quantity FROM usage WHERE space_id = ? AND day LIKE ?`, |
| 182 | spaceID, month+"-%") |
| 183 | if err != nil { |
| 184 | return nil, err |
| 185 | } |
| 186 | return scanUsageRows(rows) |
| 187 | } |
| 188 | |
| 189 | // fleetUsageForMonth sums every space's rows for a month — the pool's view. |
| 190 | func (db *cloudDB) fleetUsageForMonth(month string) ([]botUsageRow, error) { |
| 191 | rows, err := db.sql.Query( |
| 192 | `SELECT day, model, dimension, SUM(quantity) FROM usage WHERE day LIKE ? |
| 193 | GROUP BY day, model, dimension`, month+"-%") |
| 194 | if err != nil { |
| 195 | return nil, err |
| 196 | } |
| 197 | return scanUsageRows(rows) |
| 198 | } |
| 199 | |
| 200 | // usageFor reads a space's ledger rows (tests + the future usage surface). |
| 201 | func (db *cloudDB) usageFor(spaceID int64) ([]botUsageRow, error) { |
| 202 | rows, err := db.sql.Query( |
| 203 | `SELECT day, model, dimension, quantity FROM usage WHERE space_id = ? ORDER BY day, model, dimension`, spaceID) |
| 204 | if err != nil { |
| 205 | return nil, err |
| 206 | } |
| 207 | return scanUsageRows(rows) |
| 208 | } |
| 209 | |
| 210 | func scanUsageRows(rows *sql.Rows) ([]botUsageRow, error) { |
| 211 | defer rows.Close() |
| 212 | var out []botUsageRow |
| 213 | for rows.Next() { |
| 214 | var r botUsageRow |
| 215 | if err := rows.Scan(&r.Day, &r.Model, &r.Dimension, &r.Quantity); err != nil { |
| 216 | return nil, err |
| 217 | } |
| 218 | out = append(out, r) |
| 219 | } |
| 220 | return out, rows.Err() |
| 221 | } |
| 222 | |
| 223 | // migrateSpacesID rebuilds a spaces table created under the original host-PK |
| 224 | // schema (no integer id) into the id-keyed one above. AUTOINCREMENT means a |
| 225 | // torn-down space's id is never reused, so its sqid subdomain can't be handed to |
| 226 | // a different space later. A no-op once the id column exists. |
| 227 | func (db *cloudDB) migrateSpacesID() error { |
| 228 | rows, err := db.sql.Query(`PRAGMA table_info(spaces)`) |
| 229 | if err != nil { |
| 230 | return err |
| 231 | } |
| 232 | hasID := false |
| 233 | for rows.Next() { |
| 234 | var cid, notnull, pk int |
| 235 | var name, ctype string |
| 236 | var dflt sql.NullString |
| 237 | if err := rows.Scan(&cid, &name, &ctype, ¬null, &dflt, &pk); err != nil { |
| 238 | rows.Close() |
| 239 | return err |
| 240 | } |
| 241 | if name == "id" { |
| 242 | hasID = true |
| 243 | } |
| 244 | } |
| 245 | rows.Close() |
| 246 | if hasID { |
| 247 | return nil |
| 248 | } |
| 249 | // Preserve any existing rows (they get fresh sequential ids in created_at order). |
| 250 | _, err = db.sql.Exec(` |
| 251 | ALTER TABLE spaces RENAME TO spaces_old; |
| 252 | CREATE TABLE spaces ( |
| 253 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 254 | host TEXT UNIQUE, |
| 255 | caller_id TEXT NOT NULL, |
| 256 | requester TEXT NOT NULL, |
| 257 | created_at INTEGER NOT NULL |
| 258 | ); |
| 259 | INSERT INTO spaces (host, caller_id, requester, created_at) |
| 260 | SELECT host, caller_id, requester, created_at FROM spaces_old ORDER BY created_at; |
| 261 | DROP TABLE spaces_old;`) |
| 262 | return err |
| 263 | } |
| 264 | |
| 265 | // spacesHasColumn reports whether the spaces table already carries a column — |
| 266 | // the guard that keeps the billing migrations additive and re-runnable. |
| 267 | func (db *cloudDB) spacesHasColumn(column string) (bool, error) { |
| 268 | rows, err := db.sql.Query(`PRAGMA table_info(spaces)`) |
| 269 | if err != nil { |
| 270 | return false, err |
| 271 | } |
| 272 | defer rows.Close() |
| 273 | for rows.Next() { |
| 274 | var cid, notnull, pk int |
| 275 | var name, ctype string |
| 276 | var dflt sql.NullString |
| 277 | if err := rows.Scan(&cid, &name, &ctype, ¬null, &dflt, &pk); err != nil { |
| 278 | return false, err |
| 279 | } |
| 280 | if name == column { |
| 281 | return true, nil |
| 282 | } |
| 283 | } |
| 284 | return false, rows.Err() |
| 285 | } |
| 286 | |
| 287 | // migrateBilling adds the billing columns to spaces (see billing.go). This is |
| 288 | // the ENTIRE billing data footprint, by design: two opaque Stripe ids, a flag, |
| 289 | // a tier key, and a period end — never an email, name, or card detail (Stripe |
| 290 | // holds those; see docs/cloud-provisioner.md § Billing). |
| 291 | func (db *cloudDB) migrateBilling() error { |
| 292 | if has, err := db.spacesHasColumn("entitlement"); err != nil { |
| 293 | return err |
| 294 | } else if !has { |
| 295 | if _, err := db.sql.Exec(` |
| 296 | ALTER TABLE spaces ADD COLUMN stripe_customer_id TEXT; |
| 297 | ALTER TABLE spaces ADD COLUMN stripe_subscription_id TEXT; |
| 298 | ALTER TABLE spaces ADD COLUMN entitlement TEXT NOT NULL DEFAULT 'free'; |
| 299 | ALTER TABLE spaces ADD COLUMN paid_until INTEGER NOT NULL DEFAULT 0;`); err != nil { |
| 300 | return err |
| 301 | } |
| 302 | } |
| 303 | // tier landed one change after the columns above, so it gets its own guard |
| 304 | // (a DB migrated in between has entitlement but not tier). |
| 305 | if has, err := db.spacesHasColumn("tier"); err != nil { |
| 306 | return err |
| 307 | } else if !has { |
| 308 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN tier TEXT NOT NULL DEFAULT ''`); err != nil { |
| 309 | return err |
| 310 | } |
| 311 | } |
| 312 | // cancel_at_period_end: the payer has cancelled and the subscription runs |
| 313 | // out at paid_until — home shows "ends <date>" on the row. |
| 314 | if has, err := db.spacesHasColumn("cancel_at_period_end"); err != nil { |
| 315 | return err |
| 316 | } else if !has { |
| 317 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN cancel_at_period_end INTEGER NOT NULL DEFAULT 0`); err != nil { |
| 318 | return err |
| 319 | } |
| 320 | } |
| 321 | // band: which honor price the subscription is on (docs/pricing-bands.md) — |
| 322 | // base/supporter/reduced/solidarity. '' on pre-band rows reads as base. |
| 323 | if has, err := db.spacesHasColumn("band"); err != nil { |
| 324 | return err |
| 325 | } else if !has { |
| 326 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN band TEXT NOT NULL DEFAULT ''`); err != nil { |
| 327 | return err |
| 328 | } |
| 329 | } |
| 330 | // seats: the head count a per-seat subscription currently bills, mirrored |
| 331 | // from Stripe's own subscription item (docs/tier-economics.md § Per-seat). |
| 332 | // 0 on flat tiers and pre-seat rows; the renewal true-up compares it |
| 333 | // against the live member count. |
| 334 | if has, err := db.spacesHasColumn("seats"); err != nil { |
| 335 | return err |
| 336 | } else if !has { |
| 337 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN seats INTEGER NOT NULL DEFAULT 0`); err != nil { |
| 338 | return err |
| 339 | } |
| 340 | } |
| 341 | // Pay-before-create (the tier picker in the create flow): the Checkout |
| 342 | // Session bound to a pending allocation, and the name chosen at create — |
| 343 | // held here (never in Stripe metadata) until the claim provisions it. |
| 344 | if has, err := db.spacesHasColumn("checkout_session_id"); err != nil { |
| 345 | return err |
| 346 | } else if !has { |
| 347 | if _, err := db.sql.Exec(` |
| 348 | ALTER TABLE spaces ADD COLUMN checkout_session_id TEXT; |
| 349 | ALTER TABLE spaces ADD COLUMN pending_name TEXT NOT NULL DEFAULT ''`); err != nil { |
| 350 | return err |
| 351 | } |
| 352 | } |
| 353 | return nil |
| 354 | } |
| 355 | |
| 356 | // migrateMeter adds the storage meter's columns (#668 slice 1): the byte count |
| 357 | // controld last measured and when. Bytes of ciphertext — content-blind, like |
| 358 | // everything in this ledger. |
| 359 | func (db *cloudDB) migrateMeter() error { |
| 360 | if has, err := db.spacesHasColumn("storage_bytes"); err != nil { |
| 361 | return err |
| 362 | } else if !has { |
| 363 | if _, err := db.sql.Exec(` |
| 364 | ALTER TABLE spaces ADD COLUMN storage_bytes INTEGER NOT NULL DEFAULT 0; |
| 365 | ALTER TABLE spaces ADD COLUMN metered_at INTEGER NOT NULL DEFAULT 0;`); err != nil { |
| 366 | return err |
| 367 | } |
| 368 | } |
| 369 | // quota_bytes landed one change later (enforcement): what the cloud last |
| 370 | // pushed into the instance's LCHAT_BLOB_QUOTA_BYTES. 0 = never pushed. |
| 371 | if has, err := db.spacesHasColumn("quota_bytes"); err != nil { |
| 372 | return err |
| 373 | } else if !has { |
| 374 | if _, err := db.sql.Exec(`ALTER TABLE spaces ADD COLUMN quota_bytes INTEGER NOT NULL DEFAULT 0`); err != nil { |
| 375 | return err |
| 376 | } |
| 377 | } |
| 378 | // The sweep also records each space's claimed-ness (setup_complete / |
| 379 | // members), so clients can tell an unusable shell from a live space with |
| 380 | // server truth instead of client-held credentials. Defaults match a fresh |
| 381 | // create exactly: never set up, one pre-seeded admin. |
| 382 | if has, err := db.spacesHasColumn("setup_complete"); err != nil || has { |
| 383 | return err |
| 384 | } |
| 385 | _, err := db.sql.Exec(` |
| 386 | ALTER TABLE spaces ADD COLUMN setup_complete INTEGER NOT NULL DEFAULT 0; |
| 387 | ALTER TABLE spaces ADD COLUMN members INTEGER NOT NULL DEFAULT 1;`) |
| 388 | return err |
| 389 | } |
| 390 | |
| 391 | // mintCallerToken creates a caller token, stores only its hash, and returns the |
| 392 | // plaintext (shown once). |
| 393 | func (db *cloudDB) mintCallerToken(label string) (id, token string, err error) { |
| 394 | if token, err = randToken(); err != nil { |
| 395 | return "", "", err |
| 396 | } |
| 397 | if id, err = randToken(); err != nil { |
| 398 | return "", "", err |
| 399 | } |
| 400 | _, err = db.sql.Exec( |
| 401 | `INSERT INTO caller_tokens(id, label, token_hash, created_at) VALUES(?, ?, ?, ?)`, |
| 402 | id, label, hashToken(token), time.Now().Unix(), |
| 403 | ) |
| 404 | return id, token, err |
| 405 | } |
| 406 | |
| 407 | // authCaller resolves a bearer token to its caller id, bumping last_used_at. |
| 408 | func (db *cloudDB) authCaller(token string) (id string, ok bool) { |
| 409 | if token == "" { |
| 410 | return "", false |
| 411 | } |
| 412 | if db.sql.QueryRow( |
| 413 | `SELECT id FROM caller_tokens WHERE token_hash = ?`, hashToken(token), |
| 414 | ).Scan(&id) != nil { |
| 415 | return "", false |
| 416 | } |
| 417 | // Best-effort; a failed bump doesn't deny an otherwise-valid token. |
| 418 | _, _ = db.sql.Exec(`UPDATE caller_tokens SET last_used_at = ? WHERE id = ?`, time.Now().Unix(), id) |
| 419 | return id, true |
| 420 | } |
| 421 | |
| 422 | // CallerToken is a token row for the admin listing — never the token itself. |
| 423 | type CallerToken struct { |
| 424 | ID string `json:"id"` |
| 425 | Label string `json:"label"` |
| 426 | CreatedAt int64 `json:"created_at"` |
| 427 | LastUsedAt *int64 `json:"last_used_at"` |
| 428 | } |
| 429 | |
| 430 | func (db *cloudDB) listCallerTokens() ([]CallerToken, error) { |
| 431 | rows, err := db.sql.Query(`SELECT id, label, created_at, last_used_at FROM caller_tokens ORDER BY created_at`) |
| 432 | if err != nil { |
| 433 | return nil, err |
| 434 | } |
| 435 | defer rows.Close() |
| 436 | out := []CallerToken{} |
| 437 | for rows.Next() { |
| 438 | var t CallerToken |
| 439 | if err := rows.Scan(&t.ID, &t.Label, &t.CreatedAt, &t.LastUsedAt); err != nil { |
| 440 | return nil, err |
| 441 | } |
| 442 | out = append(out, t) |
| 443 | } |
| 444 | return out, rows.Err() |
| 445 | } |
| 446 | |
| 447 | func (db *cloudDB) revokeCallerToken(id string) (bool, error) { |
| 448 | res, err := db.sql.Exec(`DELETE FROM caller_tokens WHERE id = ?`, id) |
| 449 | if err != nil { |
| 450 | return false, err |
| 451 | } |
| 452 | n, _ := res.RowsAffected() |
| 453 | return n > 0, nil |
| 454 | } |
| 455 | |
| 456 | // allocateSpace reserves the next id for a (caller, requester) if they're under |
| 457 | // quota, atomically: the quota count and the insert share one transaction (and |
| 458 | // SetMaxOpenConns(1) serializes writers), so two concurrent creates can't both |
| 459 | // slip past the limit. The row is inserted with a NULL host — the caller turns |
| 460 | // the returned id into a hostname via sqids, then finalizeSpace records it. |
| 461 | // overQuota=true (with a zero id) means the request is at/over the limit. |
| 462 | // |
| 463 | // Only FREE spaces count against the quota: a paid space carries itself |
| 464 | // (per-space pricing, #562), so upgrading one frees its slot. Pending creates |
| 465 | // (NULL host) are free by default and still count — they hold a slot until |
| 466 | // they finalize or roll back. |
| 467 | func (db *cloudDB) allocateSpace(callerID, requester string, quota int) (id int64, overQuota bool, err error) { |
| 468 | tx, err := db.sql.Begin() |
| 469 | if err != nil { |
| 470 | return 0, false, err |
| 471 | } |
| 472 | defer tx.Rollback() |
| 473 | var n int |
| 474 | if err := tx.QueryRow( |
| 475 | `SELECT COUNT(*) FROM spaces WHERE caller_id = ? AND requester = ? AND entitlement = 'free'`, |
| 476 | callerID, requester, |
| 477 | ).Scan(&n); err != nil { |
| 478 | return 0, false, err |
| 479 | } |
| 480 | if n >= quota { |
| 481 | return 0, true, nil |
| 482 | } |
| 483 | now := time.Now().Unix() |
| 484 | res, err := tx.Exec( |
| 485 | `INSERT INTO spaces(host, caller_id, requester, created_at) VALUES(NULL, ?, ?, ?)`, |
| 486 | callerID, requester, now, |
| 487 | ) |
| 488 | if err != nil { |
| 489 | return 0, false, err |
| 490 | } |
| 491 | if _, err := tx.Exec(`INSERT INTO creates_log(created_at) VALUES(?)`, now); err != nil { |
| 492 | return 0, false, err |
| 493 | } |
| 494 | if id, err = res.LastInsertId(); err != nil { |
| 495 | return 0, false, err |
| 496 | } |
| 497 | return id, false, tx.Commit() |
| 498 | } |
| 499 | |
| 500 | // allocateDemoSpace reserves a demo row (docs/demo-spaces.md). Demos never |
| 501 | // count against — and are never counted by — the free-space quota; their own |
| 502 | // bounds are the live-demo cap (counted in cloud.go before this runs) and the |
| 503 | // shared creates_log budget, which they do feed: a demo create is still a |
| 504 | // create. |
| 505 | func (db *cloudDB) allocateDemoSpace(callerID, requester string) (int64, error) { |
| 506 | tx, err := db.sql.Begin() |
| 507 | if err != nil { |
| 508 | return 0, err |
| 509 | } |
| 510 | defer tx.Rollback() |
| 511 | now := time.Now().Unix() |
| 512 | res, err := tx.Exec( |
| 513 | `INSERT INTO spaces(host, caller_id, requester, created_at, entitlement) VALUES(NULL, ?, ?, ?, ?)`, |
| 514 | callerID, requester, now, entDemo, |
| 515 | ) |
| 516 | if err != nil { |
| 517 | return 0, err |
| 518 | } |
| 519 | if _, err := tx.Exec(`INSERT INTO creates_log(created_at) VALUES(?)`, now); err != nil { |
| 520 | return 0, err |
| 521 | } |
| 522 | id, err := res.LastInsertId() |
| 523 | if err != nil { |
| 524 | return 0, err |
| 525 | } |
| 526 | return id, tx.Commit() |
| 527 | } |
| 528 | |
| 529 | // allocatePendingPaidSpace reserves a pay-first row (the tier picker's paid |
| 530 | // branch): entitlement 'pending' keeps it outside the free quota and every |
| 531 | // sweep, and the chosen display name waits here for the claim. It still feeds |
| 532 | // creates_log — minting checkouts is a create for the budget breaker's |
| 533 | // purposes, or session spam would sidestep it. |
| 534 | func (db *cloudDB) allocatePendingPaidSpace(callerID, requester, name string) (int64, error) { |
| 535 | tx, err := db.sql.Begin() |
| 536 | if err != nil { |
| 537 | return 0, err |
| 538 | } |
| 539 | defer tx.Rollback() |
| 540 | now := time.Now().Unix() |
| 541 | res, err := tx.Exec( |
| 542 | `INSERT INTO spaces(host, caller_id, requester, created_at, entitlement, pending_name) VALUES(NULL, ?, ?, ?, ?, ?)`, |
| 543 | callerID, requester, now, entPending, name, |
| 544 | ) |
| 545 | if err != nil { |
| 546 | return 0, err |
| 547 | } |
| 548 | if _, err := tx.Exec(`INSERT INTO creates_log(created_at) VALUES(?)`, now); err != nil { |
| 549 | return 0, err |
| 550 | } |
| 551 | id, err := res.LastInsertId() |
| 552 | if err != nil { |
| 553 | return 0, err |
| 554 | } |
| 555 | return id, tx.Commit() |
| 556 | } |
| 557 | |
| 558 | // setCheckoutSession binds the Stripe Checkout Session to its pending row — |
| 559 | // audit and debugging; the claim's authority is the session Stripe returns. |
| 560 | func (db *cloudDB) setCheckoutSession(id int64, session string) error { |
| 561 | _, err := db.sql.Exec(`UPDATE spaces SET checkout_session_id = ? WHERE id = ?`, session, id) |
| 562 | return err |
| 563 | } |
| 564 | |
| 565 | // pendingSpace reads the claim path's view of one allocation: who it belongs |
| 566 | // to, whether it's provisioned yet, and the name chosen at create. |
| 567 | type pendingSpace struct { |
| 568 | ID int64 |
| 569 | CallerID string |
| 570 | Requester string |
| 571 | Host string // "" until provisioned |
| 572 | Entitlement string |
| 573 | PendingName string |
| 574 | } |
| 575 | |
| 576 | func (db *cloudDB) pendingSpaceByID(id int64) (pendingSpace, bool, error) { |
| 577 | p := pendingSpace{ID: id} |
| 578 | err := db.sql.QueryRow( |
| 579 | `SELECT caller_id, requester, COALESCE(host, ''), entitlement, pending_name FROM spaces WHERE id = ?`, id, |
| 580 | ).Scan(&p.CallerID, &p.Requester, &p.Host, &p.Entitlement, &p.PendingName) |
| 581 | if err == sql.ErrNoRows { |
| 582 | return pendingSpace{}, false, nil |
| 583 | } |
| 584 | if err != nil { |
| 585 | return pendingSpace{}, false, err |
| 586 | } |
| 587 | return p, true, nil |
| 588 | } |
| 589 | |
| 590 | // prunePendingCreates drops pending allocations whose checkout can no longer |
| 591 | // complete (Stripe sessions expire after 24h; the cutoff arrives well past |
| 592 | // that). A PAID row is never pending, so money is never pruned. |
| 593 | func (db *cloudDB) prunePendingCreates(cutoff int64) (int64, error) { |
| 594 | res, err := db.sql.Exec( |
| 595 | `DELETE FROM spaces WHERE entitlement = ? AND host IS NULL AND created_at <= ?`, entPending, cutoff) |
| 596 | if err != nil { |
| 597 | return 0, err |
| 598 | } |
| 599 | return res.RowsAffected() |
| 600 | } |
| 601 | |
| 602 | // countDemos counts every live demo row, pending creates included — the |
| 603 | // live-demo cap must see a create the moment it's reserved, not once it's up. |
| 604 | func (db *cloudDB) countDemos() (int, error) { |
| 605 | var n int |
| 606 | err := db.sql.QueryRow(`SELECT COUNT(*) FROM spaces WHERE entitlement = ?`, entDemo).Scan(&n) |
| 607 | return n, err |
| 608 | } |
| 609 | |
| 610 | // demosPastTTL lists every demo past its lifetime — finalized or pending (a |
| 611 | // pending row is an allocation whose create died mid-flight; it holds a demo |
| 612 | // slot and gets cleaned ledger-only). |
| 613 | func (db *cloudDB) demosPastTTL(cutoff int64) ([]reapCandidate, error) { |
| 614 | rows, err := db.sql.Query( |
| 615 | `SELECT id, COALESCE(host, ''), created_at FROM spaces |
| 616 | WHERE entitlement = ? AND created_at <= ? ORDER BY created_at`, entDemo, cutoff) |
| 617 | if err != nil { |
| 618 | return nil, err |
| 619 | } |
| 620 | defer rows.Close() |
| 621 | var out []reapCandidate |
| 622 | for rows.Next() { |
| 623 | var c reapCandidate |
| 624 | if err := rows.Scan(&c.ID, &c.Host, &c.CreatedAt); err != nil { |
| 625 | return nil, err |
| 626 | } |
| 627 | out = append(out, c) |
| 628 | } |
| 629 | return out, rows.Err() |
| 630 | } |
| 631 | |
| 632 | // createsSince counts allocations at/after cutoff — the global budget's meter. |
| 633 | func (db *cloudDB) createsSince(cutoff int64) (int, error) { |
| 634 | var n int |
| 635 | err := db.sql.QueryRow(`SELECT COUNT(*) FROM creates_log WHERE created_at >= ?`, cutoff).Scan(&n) |
| 636 | return n, err |
| 637 | } |
| 638 | |
| 639 | // finalizeSpace records the hostname once the instance is created. |
| 640 | func (db *cloudDB) finalizeSpace(id int64, host string) error { |
| 641 | _, err := db.sql.Exec(`UPDATE spaces SET host = ? WHERE id = ?`, host, id) |
| 642 | return err |
| 643 | } |
| 644 | |
| 645 | // deleteSpaceByID rolls back an allocation whose instance never came up, so a |
| 646 | // failed create doesn't hold a quota slot. (The id is still spent — AUTOINCREMENT |
| 647 | // never reuses it — which is fine; ids are cheap and gaps are harmless.) |
| 648 | func (db *cloudDB) deleteSpaceByID(id int64) error { |
| 649 | _, err := db.sql.Exec(`DELETE FROM spaces WHERE id = ?`, id) |
| 650 | return err |
| 651 | } |
| 652 | |
| 653 | // reapCandidate is a finalized space old enough for the reaper to check. |
| 654 | type reapCandidate struct { |
| 655 | ID int64 |
| 656 | Host string |
| 657 | CreatedAt int64 |
| 658 | } |
| 659 | |
| 660 | // reclaimCandidates returns one account's own free-entitlement rows created |
| 661 | // at/before cutoff, oldest first — the pool the create path may self-reclaim |
| 662 | // from when the quota is hit. Unlike spacesOlderThan this INCLUDES pending |
| 663 | // rows (NULL host, returned as ""): a create that died between allocate and |
| 664 | // finalize holds a quota slot forever otherwise, which at quota 1 would brick |
| 665 | // the account. Paid rows are excluded at the source, same as the reaper. |
| 666 | func (db *cloudDB) reclaimCandidates(callerID, requester string, cutoff int64) ([]reapCandidate, error) { |
| 667 | rows, err := db.sql.Query( |
| 668 | `SELECT id, COALESCE(host, ''), created_at FROM spaces |
| 669 | WHERE caller_id = ? AND requester = ? AND entitlement = 'free' AND created_at <= ? |
| 670 | ORDER BY created_at`, callerID, requester, cutoff) |
| 671 | if err != nil { |
| 672 | return nil, err |
| 673 | } |
| 674 | defer rows.Close() |
| 675 | var out []reapCandidate |
| 676 | for rows.Next() { |
| 677 | var c reapCandidate |
| 678 | if err := rows.Scan(&c.ID, &c.Host, &c.CreatedAt); err != nil { |
| 679 | return nil, err |
| 680 | } |
| 681 | out = append(out, c) |
| 682 | } |
| 683 | return out, rows.Err() |
| 684 | } |
| 685 | |
| 686 | // spacesOlderThan returns finalized spaces created at/before cutoff (unix secs), |
| 687 | // oldest first — the reaper's candidate set. A NULL host (create in flight or |
| 688 | // rolled back) is never a candidate, and neither is a paid space: someone paying |
| 689 | // for a space is the strongest possible "claimed" signal, so it's excluded here |
| 690 | // at the source rather than trusted to a downstream guard. |
| 691 | func (db *cloudDB) spacesOlderThan(cutoff int64) ([]reapCandidate, error) { |
| 692 | rows, err := db.sql.Query( |
| 693 | `SELECT id, host, created_at FROM spaces |
| 694 | WHERE host IS NOT NULL AND created_at <= ? AND entitlement = 'free' |
| 695 | ORDER BY created_at`, cutoff) |
| 696 | if err != nil { |
| 697 | return nil, err |
| 698 | } |
| 699 | defer rows.Close() |
| 700 | var out []reapCandidate |
| 701 | for rows.Next() { |
| 702 | var c reapCandidate |
| 703 | if err := rows.Scan(&c.ID, &c.Host, &c.CreatedAt); err != nil { |
| 704 | return nil, err |
| 705 | } |
| 706 | out = append(out, c) |
| 707 | } |
| 708 | return out, rows.Err() |
| 709 | } |
| 710 | |
| 711 | // spaceOwned reports whether this exact (caller, requester) provisioned host — |
| 712 | // the ownership check in front of deletion. Nobody can delete a space through |
| 713 | // the provisioner that the provisioner didn't create for them. |
| 714 | func (db *cloudDB) spaceOwned(callerID, requester, host string) (int64, bool, error) { |
| 715 | var id int64 |
| 716 | err := db.sql.QueryRow( |
| 717 | `SELECT id FROM spaces WHERE caller_id = ? AND requester = ? AND host = ?`, |
| 718 | callerID, requester, host, |
| 719 | ).Scan(&id) |
| 720 | if err == sql.ErrNoRows { |
| 721 | return 0, false, nil |
| 722 | } |
| 723 | if err != nil { |
| 724 | return 0, false, err |
| 725 | } |
| 726 | return id, true, nil |
| 727 | } |
| 728 | |
| 729 | // Space is one provisioned instance in a requester's listing. |
| 730 | type Space struct { |
| 731 | Host string `json:"host"` |
| 732 | URL string `json:"url"` |
| 733 | CreatedAt int64 `json:"created_at"` |
| 734 | Entitlement string `json:"entitlement"` |
| 735 | Tier string `json:"tier,omitempty"` |
| 736 | PaidUntil int64 `json:"paid_until,omitempty"` |
| 737 | // The payer cancelled; the subscription (and tier) runs out at PaidUntil. |
| 738 | CancelAtPeriodEnd bool `json:"cancel_at_period_end,omitempty"` |
| 739 | // The storage meter's last reading (bytes of ciphertext; 0 = not yet |
| 740 | // metered), when it was taken, and the cap last pushed to the instance |
| 741 | // (0 = none — the instance's compiled default applies). |
| 742 | StorageBytes int64 `json:"storage_bytes,omitempty"` |
| 743 | MeteredAt int64 `json:"metered_at,omitempty"` |
| 744 | StorageCap int64 `json:"storage_cap,omitempty"` |
| 745 | // Claimed-ness, as of the last sweep: whether the admin ever finished |
| 746 | // setup, and the member count (1 = just the pre-seeded admin). Lets the |
| 747 | // owner's UI recognize an unusable shell with server truth. |
| 748 | SetupComplete bool `json:"setup_complete"` |
| 749 | Members int `json:"members,omitempty"` |
| 750 | } |
| 751 | |
| 752 | // listSpaces returns a requester's finalized spaces (a NULL host is a create |
| 753 | // still in flight or one that failed before rollback — not shown). |
| 754 | func (db *cloudDB) listSpaces(callerID, requester string) ([]Space, error) { |
| 755 | rows, err := db.sql.Query( |
| 756 | `SELECT host, created_at, entitlement, tier, paid_until, cancel_at_period_end, storage_bytes, metered_at, quota_bytes, setup_complete, members FROM spaces |
| 757 | WHERE caller_id = ? AND requester = ? AND host IS NOT NULL ORDER BY id`, |
| 758 | callerID, requester, |
| 759 | ) |
| 760 | if err != nil { |
| 761 | return nil, err |
| 762 | } |
| 763 | defer rows.Close() |
| 764 | out := []Space{} |
| 765 | for rows.Next() { |
| 766 | var s Space |
| 767 | if err := rows.Scan(&s.Host, &s.CreatedAt, &s.Entitlement, &s.Tier, &s.PaidUntil, &s.CancelAtPeriodEnd, &s.StorageBytes, &s.MeteredAt, &s.StorageCap, &s.SetupComplete, &s.Members); err != nil { |
| 768 | return nil, err |
| 769 | } |
| 770 | s.URL = "https://" + s.Host |
| 771 | out = append(out, s) |
| 772 | } |
| 773 | return out, rows.Err() |
| 774 | } |
| 775 | |
| 776 | // ---- billing state (see billing.go) ------------------------------------------- |
| 777 | |
| 778 | const ( |
| 779 | entFree = "free" |
| 780 | entPaid = "paid" |
| 781 | entDemo = "demo" // 24h try-it spaces (docs/demo-spaces.md, demo.go) |
| 782 | // entPending marks a pay-first allocation awaiting its checkout: not a |
| 783 | // space yet (host NULL, never listed, never quota-counted, invisible to |
| 784 | // the reaper's free-only sweep) — it either becomes paid at claim time or |
| 785 | // is pruned once its checkout can no longer complete. |
| 786 | entPending = "pending" |
| 787 | ) |
| 788 | |
| 789 | // spaceEntitlement reads one space's entitlement flag. |
| 790 | func (db *cloudDB) spaceEntitlement(id int64) (string, error) { |
| 791 | var ent string |
| 792 | err := db.sql.QueryRow(`SELECT entitlement FROM spaces WHERE id = ?`, id).Scan(&ent) |
| 793 | return ent, err |
| 794 | } |
| 795 | |
| 796 | // markSpacePaid records a completed checkout: the opaque Stripe ids, the tier |
| 797 | // bought, plus the paid flag (and a fresh subscription is by definition not |
| 798 | // cancelling). paid_until is left alone — the subscription event that follows |
| 799 | // the checkout carries the period end (see handleSubscriptionEvent). |
| 800 | func (db *cloudDB) markSpacePaid(id int64, customerID, subscriptionID, tier, band string) error { |
| 801 | res, err := db.sql.Exec( |
| 802 | `UPDATE spaces SET stripe_customer_id = ?, stripe_subscription_id = ?, tier = ?, band = ?, entitlement = ?, cancel_at_period_end = 0 WHERE id = ?`, |
| 803 | customerID, subscriptionID, tier, band, entPaid, id, |
| 804 | ) |
| 805 | if err != nil { |
| 806 | return err |
| 807 | } |
| 808 | if n, _ := res.RowsAffected(); n == 0 { |
| 809 | return sql.ErrNoRows |
| 810 | } |
| 811 | return nil |
| 812 | } |
| 813 | |
| 814 | // setEntitlement flips a space's entitlement + subscription period end. |
| 815 | func (db *cloudDB) setEntitlement(id int64, ent string, paidUntil int64) error { |
| 816 | return db.setEntitlementTier(id, ent, "", "", 0, paidUntil, false) |
| 817 | } |
| 818 | |
| 819 | // setEntitlementTier additionally moves the tier + band when known ("" keeps |
| 820 | // the recorded value — e.g. an event without price data), mirrors the billed |
| 821 | // seat count (0 keeps the recorded value, same rationale), and records |
| 822 | // whether the subscription is set to run out at paid_until. |
| 823 | func (db *cloudDB) setEntitlementTier(id int64, ent, tier, band string, seats int, paidUntil int64, cancelAtEnd bool) error { |
| 824 | res, err := db.sql.Exec( |
| 825 | `UPDATE spaces SET entitlement = ?, paid_until = ?, tier = COALESCE(NULLIF(?, ''), tier), band = COALESCE(NULLIF(?, ''), band), seats = COALESCE(NULLIF(?, 0), seats), cancel_at_period_end = ? WHERE id = ?`, |
| 826 | ent, paidUntil, tier, band, seats, cancelAtEnd, id, |
| 827 | ) |
| 828 | if err != nil { |
| 829 | return err |
| 830 | } |
| 831 | if n, _ := res.RowsAffected(); n == 0 { |
| 832 | return sql.ErrNoRows |
| 833 | } |
| 834 | return nil |
| 835 | } |
| 836 | |
| 837 | // meterRow is one space in the meter's sweep set — enough billing state to |
| 838 | // reconcile its quota alongside the byte reading, plus what the per-seat |
| 839 | // renewal true-up needs (subscription id, billed seats, renewal time). |
| 840 | type meterRow struct { |
| 841 | ID int64 |
| 842 | Host string |
| 843 | Entitlement string |
| 844 | Tier string |
| 845 | QuotaBytes int64 |
| 846 | MeterTokenHash string |
| 847 | Seats int |
| 848 | PaidUntil int64 |
| 849 | SubscriptionID string |
| 850 | Members int // last metered COUNT(users) — the stats-down fallback for per-seat caps |
| 851 | } |
| 852 | |
| 853 | // allFinalizedSpaces is the meter's sweep set: every space with a hostname. |
| 854 | func (db *cloudDB) allFinalizedSpaces() ([]meterRow, error) { |
| 855 | rows, err := db.sql.Query( |
| 856 | `SELECT id, host, entitlement, tier, quota_bytes, meter_token_hash, seats, paid_until, COALESCE(stripe_subscription_id, ''), members FROM spaces WHERE host IS NOT NULL ORDER BY id`) |
| 857 | if err != nil { |
| 858 | return nil, err |
| 859 | } |
| 860 | defer rows.Close() |
| 861 | var out []meterRow |
| 862 | for rows.Next() { |
| 863 | var m meterRow |
| 864 | if err := rows.Scan(&m.ID, &m.Host, &m.Entitlement, &m.Tier, &m.QuotaBytes, &m.MeterTokenHash, &m.Seats, &m.PaidUntil, &m.SubscriptionID, &m.Members); err != nil { |
| 865 | return nil, err |
| 866 | } |
| 867 | out = append(out, m) |
| 868 | } |
| 869 | return out, rows.Err() |
| 870 | } |
| 871 | |
| 872 | // migratePool adds the free-pool gauge's history table (docs/free-pool.md): |
| 873 | // one row per meter sweep, enough for the control loop, the admin API, and |
| 874 | // eventually the status page. Additive like every other migration here. |
| 875 | func (db *cloudDB) migratePool() error { |
| 876 | _, err := db.sql.Exec(` |
| 877 | CREATE TABLE IF NOT EXISTS pool_meter ( |
| 878 | ts INTEGER PRIMARY KEY, |
| 879 | free_count INTEGER NOT NULL, |
| 880 | demo_count INTEGER NOT NULL, |
| 881 | paid_count INTEGER NOT NULL, |
| 882 | free_cost_day REAL NOT NULL, |
| 883 | total_cost_day REAL NOT NULL, |
| 884 | net_revenue_day REAL NOT NULL, |
| 885 | budget_day REAL NOT NULL, |
| 886 | pressure REAL NOT NULL |
| 887 | );`) |
| 888 | return err |
| 889 | } |
| 890 | |
| 891 | // poolRows is the pool gauge's sweep set: what the budget math needs to know |
| 892 | // about every finalized space. NetEURYear stays zero here — the DB layer |
| 893 | // doesn't know the price book; the sweep resolves it. |
| 894 | func (db *cloudDB) poolRows() ([]poolRow, error) { |
| 895 | rows, err := db.sql.Query( |
| 896 | `SELECT entitlement, tier, band, seats, storage_bytes FROM spaces WHERE host IS NOT NULL`) |
| 897 | if err != nil { |
| 898 | return nil, err |
| 899 | } |
| 900 | defer rows.Close() |
| 901 | var out []poolRow |
| 902 | for rows.Next() { |
| 903 | var r poolRow |
| 904 | if err := rows.Scan(&r.Entitlement, &r.Tier, &r.Band, &r.Seats, &r.StorageBytes); err != nil { |
| 905 | return nil, err |
| 906 | } |
| 907 | out = append(out, r) |
| 908 | } |
| 909 | return out, rows.Err() |
| 910 | } |
| 911 | |
| 912 | // insertPoolMeter records one gauge reading and prunes history past ~400 days |
| 913 | // (a year of context plus slack; the table stays a few thousand rows). |
| 914 | func (db *cloudDB) insertPoolMeter(ts int64, r poolReading) error { |
| 915 | if _, err := db.sql.Exec( |
| 916 | `INSERT OR REPLACE INTO pool_meter |
| 917 | (ts, free_count, demo_count, paid_count, free_cost_day, total_cost_day, net_revenue_day, budget_day, pressure) |
| 918 | VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, |
| 919 | ts, r.FreeCount, r.DemoCount, r.PaidCount, r.FreeCostDay, r.TotalCostDay, r.NetRevenueDay, r.BudgetDay, r.Pressure, |
| 920 | ); err != nil { |
| 921 | return err |
| 922 | } |
| 923 | _, err := db.sql.Exec(`DELETE FROM pool_meter WHERE ts < ?`, ts-400*24*3600) |
| 924 | return err |
| 925 | } |
| 926 | |
| 927 | // poolRevenueHistory reads trailing sweeps' instantaneous revenue readings — |
| 928 | // the smoothing window's raw material (pool.go smoothRevenue). |
| 929 | func (db *cloudDB) poolRevenueHistory(since int64) ([]float64, error) { |
| 930 | rows, err := db.sql.Query(`SELECT net_revenue_day FROM pool_meter WHERE ts >= ? ORDER BY ts`, since) |
| 931 | if err != nil { |
| 932 | return nil, err |
| 933 | } |
| 934 | defer rows.Close() |
| 935 | var out []float64 |
| 936 | for rows.Next() { |
| 937 | var v float64 |
| 938 | if err := rows.Scan(&v); err != nil { |
| 939 | return nil, err |
| 940 | } |
| 941 | out = append(out, v) |
| 942 | } |
| 943 | return out, rows.Err() |
| 944 | } |
| 945 | |
| 946 | // latestPoolMeter reads the newest gauge reading (ok=false: never swept). |
| 947 | func (db *cloudDB) latestPoolMeter() (ts int64, r poolReading, ok bool, err error) { |
| 948 | err = db.sql.QueryRow( |
| 949 | `SELECT ts, free_count, demo_count, paid_count, free_cost_day, total_cost_day, net_revenue_day, budget_day, pressure |
| 950 | FROM pool_meter ORDER BY ts DESC LIMIT 1`, |
| 951 | ).Scan(&ts, &r.FreeCount, &r.DemoCount, &r.PaidCount, &r.FreeCostDay, &r.TotalCostDay, &r.NetRevenueDay, &r.BudgetDay, &r.Pressure) |
| 952 | if err == sql.ErrNoRows { |
| 953 | return 0, r, false, nil |
| 954 | } |
| 955 | if err != nil { |
| 956 | return 0, r, false, err |
| 957 | } |
| 958 | return ts, r, true, nil |
| 959 | } |
| 960 | |
| 961 | // spaceBillingByID reads the billing state a quota push needs. |
| 962 | func (db *cloudDB) spaceBillingByID(id int64) (m meterRow, err error) { |
| 963 | m.ID = id |
| 964 | err = db.sql.QueryRow( |
| 965 | `SELECT COALESCE(host,''), entitlement, tier, quota_bytes, seats, members FROM spaces WHERE id = ?`, id, |
| 966 | ).Scan(&m.Host, &m.Entitlement, &m.Tier, &m.QuotaBytes, &m.Seats, &m.Members) |
| 967 | return m, err |
| 968 | } |
| 969 | |
| 970 | // spaceCustomer reads a space's opaque Stripe customer id ("" = never paid) |
| 971 | // plus its recorded band — the portal path picks a per-band configuration. |
| 972 | func (db *cloudDB) spaceCustomer(id int64) (customer, band string, err error) { |
| 973 | var c *string |
| 974 | if err := db.sql.QueryRow(`SELECT stripe_customer_id, band FROM spaces WHERE id = ?`, id).Scan(&c, &band); err != nil { |
| 975 | return "", "", err |
| 976 | } |
| 977 | if c == nil { |
| 978 | return "", band, nil |
| 979 | } |
| 980 | return *c, band, nil |
| 981 | } |
| 982 | |
| 983 | // spaceSubscription reads a space's entitlement plus its opaque Stripe |
| 984 | // subscription id ("" = never subscribed) — what destroy consults to decide |
| 985 | // whether a live subscription must be cancelled first. |
| 986 | func (db *cloudDB) spaceSubscription(id int64) (entitlement, subscriptionID string, err error) { |
| 987 | var sub *string |
| 988 | if err := db.sql.QueryRow(`SELECT entitlement, stripe_subscription_id FROM spaces WHERE id = ?`, id).Scan(&entitlement, &sub); err != nil { |
| 989 | return "", "", err |
| 990 | } |
| 991 | if sub == nil { |
| 992 | return entitlement, "", nil |
| 993 | } |
| 994 | return entitlement, *sub, nil |
| 995 | } |
| 996 | |
| 997 | // setQuotaBytes records what the cloud last pushed into an instance's env. |
| 998 | func (db *cloudDB) setQuotaBytes(id, quota int64) error { |
| 999 | _, err := db.sql.Exec(`UPDATE spaces SET quota_bytes = ? WHERE id = ?`, quota, id) |
| 1000 | return err |
| 1001 | } |
| 1002 | |
| 1003 | // setStorageBytes records one meter reading plus the space's claimed-ness. |
| 1004 | func (db *cloudDB) setStorageBytes(id, bytes int64, setupComplete bool, members int, at int64) error { |
| 1005 | _, err := db.sql.Exec( |
| 1006 | `UPDATE spaces SET storage_bytes = ?, setup_complete = ?, members = ?, metered_at = ? WHERE id = ?`, |
| 1007 | bytes, setupComplete, members, at, id) |
| 1008 | return err |
| 1009 | } |
| 1010 | |
| 1011 | // findSpaceBySubscription maps a Stripe subscription id back to its space — the |
| 1012 | // fallback for subscription events that arrive without space_id metadata. |
| 1013 | func (db *cloudDB) findSpaceBySubscription(subscriptionID string) (int64, bool, error) { |
| 1014 | var id int64 |
| 1015 | err := db.sql.QueryRow( |
| 1016 | `SELECT id FROM spaces WHERE stripe_subscription_id = ?`, subscriptionID, |
| 1017 | ).Scan(&id) |
| 1018 | if err == sql.ErrNoRows { |
| 1019 | return 0, false, nil |
| 1020 | } |
| 1021 | if err != nil { |
| 1022 | return 0, false, err |
| 1023 | } |
| 1024 | return id, true, nil |
| 1025 | } |
| 1026 | |
| 1027 | // spaceByHost reads the claim path's view of one space (demo.go). ok=false: |
| 1028 | // no such finalized host. |
| 1029 | func (db *cloudDB) spaceByHost(host string) (id int64, entitlement string, createdAt int64, ok bool, err error) { |
| 1030 | err = db.sql.QueryRow( |
| 1031 | `SELECT id, entitlement, created_at FROM spaces WHERE host = ?`, host, |
| 1032 | ).Scan(&id, &entitlement, &createdAt) |
| 1033 | if err == sql.ErrNoRows { |
| 1034 | return 0, "", 0, false, nil |
| 1035 | } |
| 1036 | if err != nil { |
| 1037 | return 0, "", 0, false, err |
| 1038 | } |
| 1039 | return id, entitlement, createdAt, true, nil |
| 1040 | } |
| 1041 | |
| 1042 | // claimDemoSpace converts one demo into the claiming account's free space — |
| 1043 | // ownership transfer + entitlement flip in one guarded UPDATE, so a racing |
| 1044 | // claim (or the reaper's ledger delete) loses cleanly: rows affected 0 means |
| 1045 | // the row is no longer a claimable demo. |
| 1046 | func (db *cloudDB) claimDemoSpace(id int64, callerID, requester string) (bool, error) { |
| 1047 | res, err := db.sql.Exec( |
| 1048 | `UPDATE spaces SET caller_id = ?, requester = ?, entitlement = ? WHERE id = ? AND entitlement = ?`, |
| 1049 | callerID, requester, entFree, id, entDemo) |
| 1050 | if err != nil { |
| 1051 | return false, err |
| 1052 | } |
| 1053 | n, err := res.RowsAffected() |
| 1054 | return n == 1, err |
| 1055 | } |
| 1056 | |
| 1057 | // countFreeSpaces counts an account's free spaces — the claim path's quota |
| 1058 | // check (allocateSpace runs the same count inside its own transaction). |
| 1059 | func (db *cloudDB) countFreeSpaces(callerID, requester string) (int, error) { |
| 1060 | var n int |
| 1061 | err := db.sql.QueryRow( |
| 1062 | `SELECT COUNT(*) FROM spaces WHERE caller_id = ? AND requester = ? AND entitlement = 'free'`, |
| 1063 | callerID, requester).Scan(&n) |
| 1064 | return n, err |
| 1065 | } |
| 1066 | |
amadan