// Package console is the platform console behind // https://platform.carlosframework.com: sign up / sign in with Keymail, see // your apps and their live platform state. Slice 1 is strictly READ-ONLY // against platform state — the console reads the registry and the release // bucket, and mutates nothing but its own little SQLite. package console import ( "context" "crypto/ecdsa" "crypto/elliptic" "crypto/rand" "crypto/sha256" "crypto/x509" "database/sql" "encoding/base64" "encoding/hex" "errors" "fmt" "log" "os" "path/filepath" "strings" "sync/atomic" "time" "github.com/carlosframework/platform/internal/release" _ "modernc.org/sqlite" ) const ( // SessionTTL is how long a console session cookie stays valid. SessionTTL = 30 * 24 * time.Hour // ConfirmationTTL bounds a returning-sign-in confirmation link. ConfirmationTTL = 15 * time.Minute ) // Store is the console's own SQLite: who exists, who owns what, who is // signed in, and the console's Keymail credentials. Platform state (routes, // channels) is never in here — that is read live from the registry and the // deployment bucket. type Store struct { claimBucket release.Store claimSQLAdmit func(context.Context, string, string, string, func(context.Context) error) error // db is what every query runs on: the plain pool, or, inside // WithAppMutation, the bound view (store_gate.go). db sqlHandle // rawPool and rawBoot are the raw handles, private to Open, Close, // withClaimSQL and the boot seam (claim_sql_census_test.go keeps them // there). Flag-off, both are db itself. rawPool *sql.DB rawBoot *sql.DB // gate is the structural SQL gate; nil flag-off. bound is set on the // narrow view withClaimSQL hands its callback. gate *claimGate bound *claimBinding // mailEventsPruned is when mail_events was last trimmed to its // retention window, as unix seconds (mailevents.go). Zero means not yet // this process, which is what makes the first event after a restart // prune. The stamps are pointers so a Store view shares them with the // Store it was cut from. mailEventsPruned *atomic.Int64 // mailPushPruned is the same stamp for the push queue (mailpush.go). mailPushPruned *atomic.Int64 // codeKey is the in-memory HMAC key for emailed sign-in codes // (emailsignin_store.go). Minted per process and never written down, so a // database read cannot recover a code. A Store view shares it. codeKey []byte // codeEpoch is a random per-process label stored in front of each // code_mac, so a code minted by an earlier process is recognised as such. codeEpoch string } func newStore(db *sql.DB) *Store { key := make([]byte, 32) // crypto/rand.Read does not return an error (it aborts the process if the // system source fails), so there is no weaker key to fall back to. _, _ = rand.Read(key) ep := make([]byte, 8) _, _ = rand.Read(ep) return &Store{codeEpoch: hex.EncodeToString(ep), db: db, rawPool: db, rawBoot: db, mailEventsPruned: new(atomic.Int64), mailPushPruned: new(atomic.Int64), codeKey: key} } // Open opens (creating if needed) the console DB at path. func Open(path string) (*Store, error) { if dir := filepath.Dir(path); dir != "." && dir != "" { if err := os.MkdirAll(dir, 0o755); err != nil { return nil, err } } db, err := sql.Open("sqlite", path) if err != nil { return nil, err } // One writer at a time keeps WAL happy. db.SetMaxOpenConns(1) // busy_timeout FIRST: journal_mode=WAL takes a lock, and with no timeout // set yet a concurrent opener fails immediately with SQLITE_BUSY. for _, p := range []string{ "PRAGMA busy_timeout=5000", "PRAGMA journal_mode=WAL", "PRAGMA synchronous=NORMAL", } { if _, err := db.Exec(p); err != nil { db.Close() return nil, err } } s := newStore(db) if err := s.migrate(); err != nil { db.Close() return nil, err } return s, nil } // Close releases the underlying database handles. A WithAppMutation view // cannot close the store it was cut from. func (s *Store) Close() error { if s.bound != nil { return fmt.Errorf("%w: a claim SQL view cannot close the store", ErrClaimSQLScope) } err := s.rawPool.Close() if s.rawBoot != s.rawPool { if berr := s.rawBoot.Close(); err == nil { err = berr } } return err } // migrate is additive-only (CREATE TABLE IF NOT EXISTS); future columns go // in the ALTER list, whose "duplicate column" error is swallowed so the list // stays idempotent. func (s *Store) migrate() error { for _, stmt := range []string{ `CREATE TABLE IF NOT EXISTS owners ( address TEXT NOT NULL, app TEXT NOT NULL, created_at INTEGER NOT NULL, UNIQUE(address, app) );`, `CREATE TABLE IF NOT EXISTS sessions ( token_hash TEXT PRIMARY KEY, address TEXT NOT NULL, keymail_token TEXT NOT NULL DEFAULT '', created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS users ( address TEXT PRIMARY KEY, created_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS grants ( address TEXT PRIMARY KEY, token TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS confirmations ( token_hash TEXT PRIMARY KEY, address TEXT NOT NULL, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS keys ( name TEXT PRIMARY KEY, pkcs8 TEXT NOT NULL, created_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS apps ( app TEXT PRIMARY KEY, created_by TEXT NOT NULL, created_at INTEGER NOT NULL );`, `CREATE TABLE IF NOT EXISTS audit ( id INTEGER PRIMARY KEY AUTOINCREMENT, app TEXT NOT NULL, address TEXT NOT NULL, action TEXT NOT NULL, detail TEXT NOT NULL DEFAULT '', created_at INTEGER NOT NULL );`, // ---- multi-account ------------------------------------------------- // The account is the tenancy primitive: it owns apps and has members. // id is MINTED into the bucket (domains.MintAccount) and materialized // here, never autoincrement — see account.go. // name is deliberately NOT unique: identity is the sqid, so there is // nothing to squat. `CREATE TABLE IF NOT EXISTS accounts ( id INTEGER PRIMARY KEY, sqid TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at INTEGER NOT NULL );`, // Membership replaces the per-app owners grant: app access follows // from account membership. Exactly two roles, owner and member. `CREATE TABLE IF NOT EXISTS memberships ( account_id INTEGER NOT NULL, address TEXT NOT NULL, role TEXT NOT NULL, added_at INTEGER NOT NULL, PRIMARY KEY (account_id, address) );`, // Invites are single-use, expiring, revocable. The token is stored in // the clear (unlike sessions/confirmations): it is delivered into the // addressee's own Keymail inbox and is worthless without also being // signed in as that address, and an owner has to be able to LIST and // revoke a specific pending invite. `CREATE TABLE IF NOT EXISTS invites ( token TEXT PRIMARY KEY, account_id INTEGER NOT NULL, address TEXT NOT NULL, invited_by TEXT NOT NULL, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, accepted_at INTEGER NOT NULL DEFAULT 0 );`, // The alert watcher's memory (alertwatch.go): the firing state the // console last told ONE operator about, per (box, rule, address). A // row's absence means "never told them", which is why AlertSeen // reports known separately — a first sighting of a firing rule is // news, a first sighting of a quiet one is not. // // Per-recipient rather than per-rule because delivery fails per // recipient: one operator's revoked keymail grant must not re-page // every healthy operator on the next pass. The empty address is a // synthetic row meaning "nobody was mailable when this transition // happened" (see alertwatch.go). `CREATE TABLE IF NOT EXISTS alert_seen ( box TEXT NOT NULL, rule TEXT NOT NULL, address TEXT NOT NULL, firing INTEGER NOT NULL, PRIMARY KEY (box, rule, address) );`, // Per-tenant SES send/bounce/complaint/reject counts, one row per // (account, app, day) — the abuse signal and the only source of a // per-tenant send count (tenant-email spec §9, Task 16). Keyed by // account (the sqid, matching mail.Record's own acct string), not // account_id: this table is written from handleSESEvents, which // resolves straight off mail.ListDomains and never looks up a // console account row to get one. `CREATE TABLE IF NOT EXISTS mail_counts ( account TEXT NOT NULL, app TEXT NOT NULL, day TEXT NOT NULL, -- UTC YYYY-MM-DD sends INTEGER NOT NULL DEFAULT 0, bounces INTEGER NOT NULL DEFAULT 0, complaints INTEGER NOT NULL DEFAULT 0, rejects INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (account, app, day) );`, // Per-SENDER counts (senders design §8). A new table rather than a // widened key, because SQLite cannot ALTER a primary key. Every count // is written here from now on; mail_counts stays, unwritten, and is // dropped a release later by DropLegacyMailCounts. // // day is a UTC date string, not a timestamp, exactly as mail_counts' // is — which is why this table cannot join mailAppTables blindly: the // rename, purge and reap sweeps key on mailAppTimeColumn and there is // no such column here. `CREATE TABLE IF NOT EXISTS mail_sender_counts ( account TEXT NOT NULL, app TEXT NOT NULL, sender TEXT NOT NULL, day TEXT NOT NULL, -- UTC YYYY-MM-DD sends INTEGER NOT NULL DEFAULT 0, bounces INTEGER NOT NULL DEFAULT 0, complaints INTEGER NOT NULL DEFAULT 0, rejects INTEGER NOT NULL DEFAULT 0, deliveries INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (account, app, sender, day) );`, // What happened to each message, per (account, app) — one row per // recipient per SES notification (mailevents.go). The counts above // say how many bounced today; this says which message, to whom, and // what the receiving server answered, so that a member can find out // and an app can be told. Keyed like mail_counts, by the account // sqid, for the same reason. // // notice is SNS's own MessageId for the notification: SNS delivers // at least once, and a notification delivered twice is one row, not // two. by_sender records HOW the row was attributed — 1 when SES // named this app's own sending identity, 0 when the sending domain // was all there was to go on and every app sharing it got a copy. // It is kept because the difference matters to anything that hands // a row on: only a row SES attributed to the app itself is that // app's to be told about. // // Recipient addresses are personal data, so the table is bounded in // time (mailEventsRetention) rather than kept for ever. `CREATE TABLE IF NOT EXISTS mail_events ( account TEXT NOT NULL, app TEXT NOT NULL, notice TEXT NOT NULL, recipient TEXT NOT NULL, message_id TEXT NOT NULL, type TEXT NOT NULL, status TEXT NOT NULL DEFAULT '', diagnostic TEXT NOT NULL DEFAULT '', detail TEXT NOT NULL DEFAULT '', by_sender INTEGER NOT NULL DEFAULT 0, at INTEGER NOT NULL, received_at INTEGER NOT NULL, PRIMARY KEY (account, app, notice, recipient) );`, `CREATE INDEX IF NOT EXISTS mail_events_by_time ON mail_events (account, app, at);`, `CREATE INDEX IF NOT EXISTS mail_events_by_message ON mail_events (account, app, message_id);`, `CREATE INDEX IF NOT EXISTS mail_events_by_age ON mail_events (received_at);`, // Where an app wants to be told about its email (mailpush.go): one // path per app, POSTed on the app's own platform host. Absent row = // the app is not told, which is every app until it asks. // // failing_since is when the app's current run of failed deliveries // began (0: none), and suspended_at when the push was suspended for // a day of them (0: not suspended) — see mailPushSuspendAfter. Both // clear when the push is turned on again. `CREATE TABLE IF NOT EXISTS mail_event_hooks ( account TEXT NOT NULL, app TEXT NOT NULL, path TEXT NOT NULL, set_by TEXT NOT NULL, set_at INTEGER NOT NULL, failing_since INTEGER NOT NULL DEFAULT 0, suspended_at INTEGER NOT NULL DEFAULT 0, suspended_why TEXT NOT NULL DEFAULT '', PRIMARY KEY (account, app) );`, // The queue of those notifications, one row per SES notification per // app, retried until delivered or given up on. The payload carries // recipients' addresses, so the row lives no longer than the log it // was cut from (mailEventsRetention), delivered or not. notice is // UNIQUE with the app so SNS redelivering a notification queues it // once. `CREATE TABLE IF NOT EXISTS mail_event_deliveries ( id INTEGER PRIMARY KEY, account TEXT NOT NULL, app TEXT NOT NULL, notice TEXT NOT NULL, payload TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_at INTEGER NOT NULL, created_at INTEGER NOT NULL, delivered_at INTEGER NOT NULL DEFAULT 0, abandoned_at INTEGER NOT NULL DEFAULT 0, last_attempt_at INTEGER NOT NULL DEFAULT 0, last_status INTEGER NOT NULL DEFAULT 0, last_error TEXT NOT NULL DEFAULT '', abandoned_why TEXT NOT NULL DEFAULT '', UNIQUE (account, app, notice) );`, // A pass seeks each app's oldest due rows through this index, so its // cost follows the number of apps asking, not the size of anyone's // backlog. `CREATE INDEX IF NOT EXISTS mail_event_deliveries_app_due ON mail_event_deliveries (account, app, delivered_at, abandoned_at, next_at);`, `CREATE INDEX IF NOT EXISTS mail_event_deliveries_by_age ON mail_event_deliveries (created_at);`, // One row, written the first time a request arrives carrying a valid // edge forwarded-for proof (edgeproof.go). From then on a loopback // peer's X-Forwarded-For is believed only with a proof beside it. `CREATE TABLE IF NOT EXISTS edge_proof_seen ( id INTEGER PRIMARY KEY CHECK (id = 1), seen_at INTEGER NOT NULL );`, // The console's move pass pages (movesrecovery.go): the last // attempted page per finding, so a repeating page keeps a full // hour between attempts across restarts, and a once-only page is // never repeated. `CREATE TABLE IF NOT EXISTS move_pages ( rule TEXT PRIMARY KEY, attempted_at INTEGER NOT NULL );`, // The move pass's outbox (movesrecovery.go, Plan 6 Task 3): move log // entries the pass owes and has not yet written, with their exact // bytes and the instance incarnation their move's evidence carried, // until the recovery pass's drainer writes them, finds them written, // or finds the incarnation gone. An unconfirmed row waits for the // write it records to be confirmed; next_at is the earliest next // attempt, and warned marks the one journal warning an hour-old row // gets. This is the table's only definition: it exists only on this // unmerged branch, so no console database has an older shape. `CREATE TABLE IF NOT EXISTS move_log_outbox ( host TEXT NOT NULL, op TEXT NOT NULL, name TEXT NOT NULL, incarnation TEXT NOT NULL, body BLOB NOT NULL, queued_at INTEGER NOT NULL, confirmed INTEGER NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_at INTEGER NOT NULL DEFAULT 0, warned INTEGER NOT NULL DEFAULT 0, held INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (host, op, name) );`, // Plans (plans-caps-packs spec §4, §7): one row per account naming // its plan and packs. revision moves on every change; // published_revision/published_catalog say which revision, under // which catalog, the bucket's limits record already carries. `CREATE TABLE IF NOT EXISTS account_plans ( account_id INTEGER PRIMARY KEY, plan TEXT NOT NULL, packs TEXT NOT NULL DEFAULT '{}', source TEXT NOT NULL, sponsored TEXT NOT NULL DEFAULT '', reason TEXT NOT NULL DEFAULT '', revision INTEGER NOT NULL, published_revision INTEGER NOT NULL DEFAULT 0, published_catalog TEXT NOT NULL DEFAULT '', updated_by TEXT NOT NULL, updated_at INTEGER NOT NULL );`, } { if _, err := s.db.Exec(stmt); err != nil { return err } } // Additive column changes land here, forgiving "duplicate column" so // re-running is always safe. for _, stmt := range []string{ `ALTER TABLE users ADD COLUMN operator INTEGER NOT NULL DEFAULT 0;`, // deliveries (tenant-email, ruling 2a): SES already publishes // Delivery to the event destination and the ingest already // verifies and parses it — this stops throwing the answer away. // Display-only: the cap and the bounce/complaint thresholds are // deliberately NOT rebased on it, because SES's own account-review // math is bounces over SENDS, and a limiter that measures something // else from what SES reviews on is a limiter that trips at the // wrong time. `ALTER TABLE mail_counts ADD COLUMN deliveries INTEGER NOT NULL DEFAULT 0;`, // Where a returning user lands: the account they were last in. `ALTER TABLE users ADD COLUMN last_account INTEGER NOT NULL DEFAULT 0;`, // Apps belong to an account. The column lands with default 1 (ops), so // every already-claimed app keeps working; the PRIMARY KEY stays `app` // until the migration verb rebuilds the table, because changing a PK // is not an additive migration. `ALTER TABLE apps ADD COLUMN account_id INTEGER NOT NULL DEFAULT 1;`, // The audit trail is per (account, app) too. Existing rows default to // ops, which is where every pre-accounts app lived — so no history is // lost and none of it lands in somebody else's account. `ALTER TABLE audit ADD COLUMN account_id INTEGER NOT NULL DEFAULT 1;`, // Structured activity is additive to the original prose-only detail. // Empty fields mean an old row; Detail remains its lossless fallback. `ALTER TABLE audit ADD COLUMN target_type TEXT NOT NULL DEFAULT '';`, `ALTER TABLE audit ADD COLUMN target TEXT NOT NULL DEFAULT '';`, `ALTER TABLE audit ADD COLUMN message TEXT NOT NULL DEFAULT '';`, // Trash state for apps (30-day cooloff; the row IS the name // reservation, so it survives trash and dies at purge) and tombstone // MEMORY for accounts: a marked account row is never re-materialized, // which is what stops a rewritten bucket manifest resurrecting a // deleted account — see MaterializeAccounts. `ALTER TABLE apps ADD COLUMN deleted_at INTEGER NOT NULL DEFAULT 0;`, `ALTER TABLE accounts ADD COLUMN deleted_at INTEGER NOT NULL DEFAULT 0;`, // Placement (customer-fleets, 2026-08-06 ruling): the STORE OF RECORD // for which fleet (if any) an app's routes/instances should run on — // "" = unplaced. Route-derivation (fleets.go's appPlacement) used to // be the only source, which left a freshly-created app with zero // routes unable to carry a placement at all (`carlos apps create // -place` would be a silent no-op). This column is what `carlos apps // place` and apps-create's optional place field both write; every // existing row defaults to "", identical to "never placed" under the // old route-only reading. `ALTER TABLE apps ADD COLUMN placement TEXT NOT NULL DEFAULT '';`, // Fleet pool assignment (economics-v2, Task 4): the operator-set // pricing/capacity tier driving Task 5's placement into a fleet pool. // "" means "never assigned" — every existing row defaults to it, // identical to "unassigned" under the old tier-less reading. See // SetAccountTier for the fixed set of non-empty values. `ALTER TABLE accounts ADD COLUMN tier TEXT NOT NULL DEFAULT '';`, // Control-plane apps (console-as-an-app spec §6): the flag REFUSES // destruction — trash, purge, rename, transfer — and refuses a // service principal's promote. It deliberately does NOT gate a // person's promote: `stable`'s fresh-auth 403 has no API-side // step-up and `carlos deploy` log.Fatalfs on a failed promote AFTER // the ship has landed (deploy.go:206-208), so gating promotion would // break `carlos deploy -app console` — the command the flag exists to // make safe. Deploying the console should be easy; destroying it // should be hard. `ALTER TABLE apps ADD COLUMN control_plane INTEGER NOT NULL DEFAULT 0;`, // The path `carlos deploy` polls for the build the app's own PROCESS // reports, instead of X-Carlos-Version — which the edge stamps from // the route row (edge.go:1336) and which therefore lies from the // moment adoption swaps the symlink until something restarts the // process. "" keeps today's header behaviour byte for byte. `ALTER TABLE apps ADD COLUMN build_probe TEXT NOT NULL DEFAULT '';`, // Temporary apps (temporary static sites spec §2). The lifecycle lives // HERE, never in the bucket, so bucket write access cannot make an app // temporary. expires_at 0 = permanent. restoring 1 = between a // restore's "about to delete the marker" and "row is live" (§6), and // counts against the cap. purge_after/purge_for are fixed at trash // time from whether the row was temporary then; nothing re-derives // them. `ALTER TABLE apps ADD COLUMN expires_at INTEGER NOT NULL DEFAULT 0;`, `ALTER TABLE apps ADD COLUMN restoring INTEGER NOT NULL DEFAULT 0;`, `ALTER TABLE apps ADD COLUMN purge_after INTEGER NOT NULL DEFAULT 0;`, `ALTER TABLE apps ADD COLUMN purge_for INTEGER NOT NULL DEFAULT 0;`, // App region (nearest-region spec §5.1): the placement code new // instances default to, in the RECORD vocabulary — "" is the // deployment's home, which is also what every existing row reads, so // the column default IS the backfill (§5.4). region_source is a label // for people and logs: chosen | timezone | ip | default | "" (the row // predates the feature). No timezone, IP or coordinate is stored. `ALTER TABLE apps ADD COLUMN region TEXT NOT NULL DEFAULT '';`, `ALTER TABLE apps ADD COLUMN region_source TEXT NOT NULL DEFAULT '';`, // How a session was proved (email sign-in spec §5.4). Every session // that predates the column was made through Keymail, so the default // is the backfill. `ALTER TABLE sessions ADD COLUMN method TEXT NOT NULL DEFAULT 'keymail';`, } { if _, err := s.db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") { return err } } // The one-shot backfill of mail_sender_counts from mail_counts (senders // design §8). It runs AFTER the ALTER list because mail_counts.deliveries // is added there, and it runs at most once for the life of the database: // a backfill repeated at every start would resurrect rows a purge or a // rename had since removed, since mail_counts is not swept by anything. // PRAGMA user_version is the marker because the console has no other // place to keep one and this is the first thing that has needed it. if err := s.backfillMailSenderCounts(); err != nil { return err } if err := s.migrateAppsKey(); err != nil { return err } // CLI identity's own tables (authstore.go), kept out of the list above so // the feature is one file to read and one file to remove. if err := s.migrateCLIAuth(); err != nil { return err } // Passkey step-up's own tables (stepup.go), for the same reason. if err := s.migrateStepUp(); err != nil { return err } // Email sign-in's challenge table (emailsignin_store.go), for the same reason. if err := s.migrateEmailSignin(); err != nil { return err } // Pending-change quorum's own tables (pending.go), for the same reason. if err := s.migratePendingChanges(); err != nil { return err } // Door keys' own tables (doorkeys_store.go), for the same reason. return s.migrateDoorKeys() } // mailCountsBackfillVersion is the user_version this migration stamps. The // next migration that needs a one-shot marker takes 2 and reads this // constant rather than the literal. const mailCountsBackfillVersion = 1 func (s *Store) backfillMailSenderCounts() error { var version int if err := s.db.QueryRow(`PRAGMA user_version`).Scan(&version); err != nil { return err } if version >= mailCountsBackfillVersion { return nil } if _, err := s.db.Exec(`INSERT OR IGNORE INTO mail_sender_counts (account, app, sender, day, sends, bounces, complaints, rejects, deliveries) SELECT account, app, 'app', day, sends, bounces, complaints, rejects, deliveries FROM mail_counts`); err != nil { return err } // PRAGMA takes no bound parameter, and the value is a constant in this // file rather than anything a caller supplies. _, err := s.db.Exec(fmt.Sprintf(`PRAGMA user_version = %d`, mailCountsBackfillVersion)) return err } // DropLegacyMailCounts removes the pre-senders counts table. Guarded by a // flag that defaults off and driven by an operator a release after the // backfill, for the same reason mail.SweepLegacyRecords is: the rollback // window has to close before the old shape goes, and dropping it early would // leave a rolled-back console counting into a table that is not there. func (s *Store) DropLegacyMailCounts(enabled bool) error { if !enabled { return nil } _, err := s.db.Exec(`DROP TABLE IF EXISTS mail_counts`) return err } // migrateAppsKey moves the apps table from PRIMARY KEY(app) — a GLOBAL name // land-grab — to PRIMARY KEY(account_id, app), which is what "app names are // unique within an account, never globally" actually means. SQLite cannot // ALTER a primary key, so this is the one place in the console that rebuilds // a table rather than appending to the ALTER list. // // It is safe to do here rather than behind an operator verb because every row // is carried across unchanged (account_id already defaulted to 1 = ops), the // table is small, and the whole thing runs in one transaction. Leaving it to // a verb would mean a console that silently refuses a second account's "blog" // until someone remembers to run it. // // Idempotent: it does nothing once the new key is in place. func (s *Store) migrateAppsKey() error { var ddl string err := s.db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='apps'`).Scan(&ddl) if err != nil { return err } if strings.Contains(ddl, "PRIMARY KEY (account_id, app)") { return nil // already rebuilt } tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() for _, stmt := range []string{ `CREATE TABLE apps_new ( app TEXT NOT NULL, account_id INTEGER NOT NULL DEFAULT 1, created_by TEXT NOT NULL, created_at INTEGER NOT NULL, deleted_at INTEGER NOT NULL DEFAULT 0, placement TEXT NOT NULL DEFAULT '', control_plane INTEGER NOT NULL DEFAULT 0, build_probe TEXT NOT NULL DEFAULT '', region TEXT NOT NULL DEFAULT '', region_source TEXT NOT NULL DEFAULT '', expires_at INTEGER NOT NULL DEFAULT 0, restoring INTEGER NOT NULL DEFAULT 0, purge_after INTEGER NOT NULL DEFAULT 0, purge_for INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (account_id, app) );`, // The ALTERs above run before migrateAppsKey in migrate(), so // deleted_at, placement, control_plane, build_probe, region, // region_source and the temporary columns already exist on the old // table by the time this rebuild copies them — a row already // trashed, already placed, already the control plane, already // carrying a build probe or an app region, or already temporary, // when the rebuild runs must not have that state silently dropped // (nearest-region spec §5.2, temporary static sites spec §2). `INSERT INTO apps_new (app, account_id, created_by, created_at, deleted_at, placement, control_plane, build_probe, region, region_source, expires_at, restoring, purge_after, purge_for) SELECT app, account_id, created_by, created_at, deleted_at, placement, control_plane, build_probe, region, region_source, expires_at, restoring, purge_after, purge_for FROM apps;`, `DROP TABLE apps;`, `ALTER TABLE apps_new RENAME TO apps;`, } { if _, err := tx.Exec(stmt); err != nil { return err } } return tx.Commit() } // ---- owners ----------------------------------------------------------------- // Grant makes address an owner of app (idempotent). func (s *Store) Grant(address, app string) error { return s.GrantAt(address, app, time.Now()) } // GrantAt is Grant with an explicit timestamp — the backfill promotes the // EARLIEST grant holder when there is no operator, so that ordering has to be // settable by a test rather than by sleeping. func (s *Store) GrantAt(address, app string, at time.Time) error { if release.IsTemporaryName(app) { return ErrTemporaryNameReserved } _, err := s.db.Exec(`INSERT OR IGNORE INTO owners (address, app, created_at) VALUES (?, ?, ?)`, address, app, at.Unix()) return err } // Revoke removes address's ownership of app. func (s *Store) Revoke(address, app string) error { _, err := s.db.Exec(`DELETE FROM owners WHERE address = ? AND app = ?`, address, app) return err } // AppsFor returns the apps address owns, sorted. func (s *Store) AppsFor(address string) ([]string, error) { rows, err := s.db.Query(`SELECT app FROM owners WHERE address = ? ORDER BY app`, address) if err != nil { return nil, err } defer rows.Close() var out []string for rows.Next() { var a string if err := rows.Scan(&a); err != nil { return nil, err } out = append(out, a) } return out, rows.Err() } // Owners returns every grant, keyed by owner address, apps sorted. func (s *Store) Owners() (map[string][]string, error) { rows, err := s.db.Query(`SELECT address, app FROM owners ORDER BY address, app`) if err != nil { return nil, err } defer rows.Close() out := map[string][]string{} for rows.Next() { var addr, app string if err := rows.Scan(&addr, &app); err != nil { return nil, err } out[addr] = append(out[addr], app) } return out, rows.Err() } // ---- accounts, memberships, invites ----------------------------------------- // GrantHolder is one pre-accounts app-grant holder that the backfill // deliberately left OUT of ops, with the apps they held grants for. The // backfill cannot decide where these people belong — that is an account per // person or per project, which is an operator's judgement — so it reports them // instead of guessing. type GrantHolder struct { Address string Apps []string } // BackfillOpsMemberships maps the pre-accounts world onto the new one: the // built-in ops account exists, every OPERATOR is an owner of it, and every // granted app name is claimed in it (before accounts, every app was in ops by // definition). It returns the grant holders it left out, so the caller can say // who still needs an account of their own. // // WHY non-operators are excluded — this widened access in production once // already, and the reasoning has to survive the next person to read it: // // Authorization is "Role(account, address) != "" AND HasAppIn(account, app)". // A membership is therefore access to EVERY app in the account, not to the app // somebody once held a grant for. The first version of this function swept all // grant holders into ops as members, on the theory that a member is the // harmless role. It is not harmless in ops: ops holds console, platform-site, // status, and every app that has not yet been migrated out. On the flagship // that handed a user who held ONE grant — for a toy app — the ability to // promote and roll back kass, keymail and the console itself. Nobody granted // them that; the migration invented it, and it took hand-edited SQL to undo. // // A per-app grant was never deployment-wide authority. What IS deployment-wide // authority is the operator flag, so that — and only that — makes an ops // membership here, as owner. // // There is deliberately no "promote somebody so ops has an owner" fallback any // more. The old code promoted the earliest grant holder when no operator // existed, reasoning that an unadministrable account is worse than an // unexpected owner. But that is the same widening by a quieter route, and it // is no longer the only way out: an ownerless ops is recoverable box-side with // `console member -add bab -role owner` — the tool that did not exist // when that fallback was written. // // Strictly ADDITIVE and idempotent, so it can run on every console start: it // only ever INSERT OR IGNOREs, so no existing membership is ever demoted, // re-roled or removed. That matters concretely — the flagship has already run // this once under the old semantics and been cleaned up by hand, and a re-run // must not resurrect what was removed. It also deliberately does NOT delete the // owners table: the grants stay readable while the migration is verified. func (s *Store) BackfillOpsMemberships(now time.Time) ([]GrantHolder, error) { tx, err := s.db.Begin() if err != nil { return nil, err } defer tx.Rollback() if _, err := tx.Exec(`INSERT OR IGNORE INTO accounts (id, sqid, name, created_at) VALUES (?, ?, 'ops', ?)`, OpsAccountID, opsSqid, now.Unix()); err != nil { return nil, err } // OR IGNORE, never OR REPLACE or UPDATE: an address that is already a // member keeps whatever role it has. This is the only membership write in // the whole function. if _, err := tx.Exec(`INSERT OR IGNORE INTO memberships (account_id, address, role, added_at) SELECT ?, address, ?, ? FROM users WHERE operator != 0`, OpsAccountID, RoleOwner, now.Unix()); err != nil { return nil, err } // The app NAMES are still claimed in ops: that is where every pre-accounts // app physically lives, and claiming them keeps two accounts from minting // the same name before the migration moves them. `console migrate-app` is // what re-homes them afterwards. // // The NOT IN is not a tidy-up — it is the fix for a bug that was live on the // flagship, and it was found by running the migration in production, not by // reading this code. `carlos accounts migrate` + `console migrate-app` moved // jelly out of ops into account bac. The migration deliberately leaves the // legacy grant readable (see the note above about not deleting the owners // table). The next console RESTART ran this statement, found a grant for // "jelly", found no jelly row in OPS — because it had just been moved out — // and inserted one. The same app then existed in two accounts at once, and // kass and keymail were lined up for the same on the following restart. The // migration was being undone, indefinitely, by restarting the console; it // took a hand-deleted row and hand-revoked grants to stop it. // // ON CONFLICT could never have caught this: the primary key is // (account_id, app), so an ops row and a bac row for the same app do not // conflict — they are, by the schema's own rules, two different apps. The // claim has to be conditioned on the app name being unclaimed EVERYWHERE, // which is what the backfill actually means: take the names nothing has // placed yet. An app that already has a row somewhere has been placed, and // the row that placed it is the authority — never this sweep. if _, err := tx.Exec(`INSERT OR IGNORE INTO apps (app, account_id, created_by, created_at) SELECT o.app, ?, MIN(o.address), ? FROM owners o WHERE o.app NOT IN (SELECT app FROM apps) AND o.app NOT LIKE 'tmp-%' GROUP BY o.app`, OpsAccountID, now.Unix()); err != nil { return nil, err } // A tmp- grant never becomes a row: the namespace is reserved for // temporary apps, and only /new-temporary may create one (spec §3, // astra 7.2). Said at every start, because only an operator can revoke it. skipped, err := tx.Query(`SELECT DISTINCT app FROM owners WHERE app LIKE 'tmp-%' AND app NOT IN (SELECT app FROM apps) ORDER BY app`) if err != nil { return nil, err } for skipped.Next() { var app string if err := skipped.Scan(&app); err != nil { skipped.Close() return nil, err } log.Printf("console: not claiming %s for ops: names starting with tmp- belong to temporary apps. Revoke the grant with: console revoke
%s", app, app) } skipped.Close() if err := skipped.Err(); err != nil { return nil, err } // Who is left over: grant holders who are not operators and hold no ops // membership. The membership exclusion is what makes the report quiet once // an operator has dealt with someone — deliberately admitting them, or // giving them their own account — rather than nagging on every restart. rows, err := tx.Query(`SELECT o.address, o.app FROM owners o WHERE o.address NOT IN (SELECT address FROM users WHERE operator != 0) AND o.address NOT IN (SELECT address FROM memberships WHERE account_id = ?) ORDER BY o.address, o.app`, OpsAccountID) if err != nil { return nil, err } var out []GrantHolder for rows.Next() { var addr, app string if err := rows.Scan(&addr, &app); err != nil { rows.Close() return nil, err } if n := len(out); n > 0 && out[n-1].Address == addr { out[n-1].Apps = append(out[n-1].Apps, app) continue } out = append(out, GrantHolder{Address: addr, Apps: []string{app}}) } rows.Close() if err := rows.Err(); err != nil { return nil, err } if err := tx.Commit(); err != nil { return nil, err } return out, nil } // grantHolderAdvice renders the leftover grant holders as something an // operator can act on: who, which apps, and the exact three commands that give // one of them an account. A count and a list would be a to-do; this is a // runbook, and the difference decides whether it gets done. // // Returns "" when there is nobody, so the caller logs nothing rather than a // line saying nothing happened. func grantHolderAdvice(holders []GrantHolder) string { if len(holders) == 0 { return "" } var b strings.Builder fmt.Fprintf(&b, "console: %d grant holders need accounts of their own — they hold pre-accounts "+ "app grants and were deliberately NOT made members of ops (a membership there is access to "+ "console, platform-site and every unmigrated app): ", len(holders)) for i, h := range holders { if i > 0 { b.WriteString("; ") } fmt.Fprintf(&b, "%s (%s)", h.Address, strings.Join(h.Apps, " ")) } // Two commands, not three: `accounts create -owner` now records the owner in // the account manifest and the console materializes it into an owner // membership, so the `console member -add … -role owner` step this advice // used to carry is gone. It was never optional — an operator who skipped it // got an account nobody could open, which is exactly what happened on the // flagship — and the surest way to stop a step being skipped is to not need // it. The -owner flag is REQUIRED, so it has to appear here or the copied // command fails. b.WriteString(". Give each one their own with: carlos accounts create --name --owner
" + "&& console migrate-app --app --to ") return b.String() } // Roles. Two, not a matrix: enough authority to make invites safe, without // inventing a permissions model before there is a second user to test it // against. const ( RoleOwner = "owner" RoleMember = "member" ) // InviteTTL bounds a pending invite. const InviteTTL = 7 * 24 * time.Hour // opsSqid is the ops account's frozen public identity. Spelled out rather // than imported from release so the store layer stays dependency-free; the // two are pinned equal by TestMintedSqidIsTheFrozenEncoding. const opsSqid = "bab" // Account is one tenancy root as the console sees it. Role is filled only by // AccountsFor (it is the viewer's role, not a property of the account). type Account struct { ID int64 Sqid string Name string Role string CreatedAt time.Time // Seg is the path segment the console generates for this account in the // viewer's chrome (preferredSeg) — display state, never stored. Seg string } // Invite is one pending invitation. type Invite struct { Token string AccountID int64 Address string InvitedBy string CreatedAt time.Time ExpiresAt time.Time } // CreateAccount records a minted account and makes its creator the first // owner, in ONE transaction: an account with no owner is unadministrable — // nobody left who can invite, rename, or delete it — so the two writes must // never be separable by a crash. func (s *Store) CreateAccount(id int64, sqid, name, ownerAddress string, now time.Time) error { tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() if _, err := tx.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (?, ?, ?, ?)`, id, sqid, name, now.Unix()); err != nil { return err } if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at) VALUES (?, ?, ?, ?)`, id, ownerAddress, RoleOwner, now.Unix()); err != nil { return err } return tx.Commit() } // EnsureAccount records an account the console did NOT mint — one that already // exists in the bucket (MaterializeAccounts). added reports whether this call // created the row. // // Unlike CreateAccount it deliberately creates no membership, and that is not // an oversight: this call knows only what a manifest's identity fields say. // Inventing an owner — the invoking operator, the first user, anyone — would be // minting authority out of thin air over an account this box knows nothing // about. An ownerless row is honest and harmless (no membership means no // access). // // Where the owner DOES come from is the manifest's own `owner` field, applied // separately by AdoptManifestOwner, so identity and authority stay two distinct // decisions: a manifest with a broken owner still materializes an account, and // an old manifest with no owner at all still materializes exactly as it did // before the field existed. // // INSERT OR IGNORE, never REPLACE: an account the console already knows must // keep its id, its name and its members. func (s *Store) EnsureAccount(id int64, sqid, name string, created time.Time) (added bool, err error) { res, err := s.db.Exec(`INSERT OR IGNORE INTO accounts (id, sqid, name, created_at) VALUES (?, ?, ?, ?)`, id, sqid, name, created.Unix()) if err != nil { return false, err } n, err := res.RowsAffected() return n > 0, err } // AdoptManifestOwner turns an account manifest's `owner` field into the owner // membership — the console half of the fix for a PRODUCTION failure, found by // running the migration on the flagship and not by reading this code. // // What happened: `carlos accounts create` recorded an owner nowhere at all. The // bucket manifest and the registry row both landed, and the account had no // members. Membership IS authorization here, so after kass and keymail were // migrated into fresh accounts the operator could not open either of them in // the console — the account existed, held the apps, and had no door. The only // way in was a `console member -add … -role owner` that nothing told anybody to // run. // // The carlos binary still does not open this database — that boundary is // deliberate and was enforced in an earlier review. Instead the manifest, which // is already the one place account identity crosses between the CLI, the // console and every box, now carries the intended owner, and this is where the // console reads it. // // It fills a VACANCY and nothing else. The manifest is re-read on every console // start, so treating it as an instruction would make it a standing grant: an // address removed by an operator would come back on the next restart, which is // precisely the shape of the app-backfill bug this same production run turned // up. So: an account that already has ANY owner is left completely alone, and // only an ownerless one is filled. Returns whether it wrote anything. // // This is the SECOND of two independent guards, and deliberately not the load // bearing one. The manifest is not write-once — it can be rewritten by anyone // with bucket write access — so the caller only ever invokes this on the pass // that first creates the account row (see materializeOwner). That structural // rule is what stops a rewritten manifest granting authority; this vacancy // check is what stops a race between the row insert and this write, and what // keeps the function safe to reason about on its own. // // The named address may already be a plain member (an accepted invite always // grants member) — then they are promoted, rather than the account gaining a // second row for the same person. func (s *Store) AdoptManifestOwner(accountID int64, address string, now time.Time) (bool, error) { tx, err := s.db.Begin() if err != nil { return false, err } defer tx.Rollback() // Re-counted INSIDE the transaction rather than trusting HasOwner: that one // is an optimization, this one is the rule, and a concurrent `console member // -add … -role owner` must not be able to slip between the two. var owners int if err := tx.QueryRow(`SELECT COUNT(*) FROM memberships WHERE account_id = ? AND role = ?`, accountID, RoleOwner).Scan(&owners); err != nil { return false, err } if owners > 0 { return false, nil } if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at) VALUES (?, ?, ?, ?) ON CONFLICT(account_id, address) DO UPDATE SET role = excluded.role`, accountID, address, RoleOwner, now.Unix()); err != nil { return false, err } if err := tx.Commit(); err != nil { return false, err } return true, nil } // AccountBySqid returns the account with that public identity, or (nil, nil) // when there is none — "no such account" is an answer, not an error, and the // caller renders it as the same 404 a nonexistent page gets. // // A marked (deleted) row is filtered out here: a tombstoned account must be // invisible everywhere a console door looks one up, exactly like a row that // was never inserted. AccountAnyBySqid is the one exception, and it exists // for a different question — "has this sqid ever been seen" — not this one. func (s *Store) AccountBySqid(sqid string) (*Account, error) { var a Account var created int64 err := s.db.QueryRow(`SELECT id, sqid, name, created_at FROM accounts WHERE sqid = ? AND deleted_at = 0`, sqid). Scan(&a.ID, &a.Sqid, &a.Name, &created) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } a.CreatedAt = time.Unix(created, 0).UTC() return &a, nil } // AccountByID is AccountBySqid keyed the other way, for the MRU landing. func (s *Store) AccountByID(id int64) (*Account, error) { var a Account var created int64 err := s.db.QueryRow(`SELECT id, sqid, name, created_at FROM accounts WHERE id = ? AND deleted_at = 0`, id). Scan(&a.ID, &a.Sqid, &a.Name, &created) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, err } a.CreatedAt = time.Unix(created, 0).UTC() return &a, nil } // AccountAnyBySqid is AccountBySqid WITHOUT the deleted_at filter — it sees a // marked row instead of pretending it does not exist. deleted reports which // case this was, so the account struct itself needs no new field for it. // // The callers are materialization and the /domains doors' namespace check // (otherAccountsNamespace, which reserves a deleted account's namespace too). // Materialization is why it exists: the whole point of keeping a tombstoned // row (see DeleteAccount) is that the pass which decides whether to re-birth // an account from its bucket manifest must be able to tell "never seen" from // "seen, and deleted". Every other door in the console asks the filtered // question instead. func (s *Store) AccountAnyBySqid(sqid string) (*Account, bool, error) { var a Account var created, deletedAt int64 err := s.db.QueryRow(`SELECT id, sqid, name, created_at, deleted_at FROM accounts WHERE sqid = ?`, sqid). Scan(&a.ID, &a.Sqid, &a.Name, &created, &deletedAt) if err == sql.ErrNoRows { return nil, false, nil } if err != nil { return nil, false, err } a.CreatedAt = time.Unix(created, 0).UTC() return &a, deletedAt != 0, nil } // AccountsFor returns the accounts address is a member of, in creation order // — the switcher's list, and the "your first membership" fallback when there // is no MRU history. Ordered by created_at then id so two accounts created in // the same second still order deterministically. func (s *Store) AccountsFor(address string) ([]Account, error) { rows, err := s.db.Query(`SELECT a.id, a.sqid, a.name, a.created_at, m.role FROM accounts a JOIN memberships m ON m.account_id = a.id WHERE m.address = ? AND a.deleted_at = 0 ORDER BY a.created_at, a.id`, address) if err != nil { return nil, err } defer rows.Close() var out []Account for rows.Next() { var a Account var created int64 if err := rows.Scan(&a.ID, &a.Sqid, &a.Name, &created, &a.Role); err != nil { return nil, err } a.CreatedAt = time.Unix(created, 0).UTC() out = append(out, a) } return out, rows.Err() } // Accounts lists every live account — the economics pass's iteration set (an // account with no statements yet must still appear in the census). Filtered // by deleted_at like every other account reader (AccountBySqid, AccountsFor): // a retired account stops accruing usage the same tick it stops billing. func (s *Store) Accounts() ([]Account, error) { rows, err := s.db.Query(`SELECT id, sqid, name, created_at FROM accounts WHERE deleted_at = 0 ORDER BY name`) if err != nil { return nil, err } defer rows.Close() var out []Account for rows.Next() { var a Account var created int64 if err := rows.Scan(&a.ID, &a.Sqid, &a.Name, &created); err != nil { return nil, err } a.CreatedAt = time.Unix(created, 0).UTC() out = append(out, a) } return out, rows.Err() } // validAccountTiers is the fixed set SetAccountTier accepts — "" (never // assigned) plus economics-v2's fleet-pool tiers. Kept as a package-level // set rather than inlined so a future consumer (Task 5's placement pass) can // range over the same set without duplicating it. var validAccountTiers = map[string]bool{ "": true, "try": true, "pool": true, "hosted": true, "fleet": true, "dedicated": true, } // SetAccountTier records accountID's fleet-pool tier (economics-v2, Task 4) // — Task 5's placement pass reads it back via AccountTiers. Rejects any // value outside validAccountTiers with a clear error rather than writing an // unrecognized string a downstream placement pass would silently ignore. An // accountID matching no row is a silent no-op (the UPDATE simply touches // zero rows) — this store's existing convention for a keyed UPDATE with no // distinguishable "not found" case (see ReapAccount's deleted_at-guarded // UPDATE). func (s *Store) SetAccountTier(accountID int64, tier string) error { if !validAccountTiers[tier] { return fmt.Errorf("console: invalid account tier %q", tier) } _, err := s.db.Exec(`UPDATE accounts SET tier = ? WHERE id = ?`, tier, accountID) return err } // AccountTiers returns every live account's tier keyed by sqid — Task 5's // placement pass input. Filtered by deleted_at like Accounts(): a retired // account carries no tier into fleet-pool placement, same posture as it // carrying no usage into the economics pass. func (s *Store) AccountTiers() (map[string]string, error) { rows, err := s.db.Query(`SELECT sqid, tier FROM accounts WHERE deleted_at = 0`) if err != nil { return nil, err } defer rows.Close() out := map[string]string{} for rows.Next() { var sqid, tier string if err := rows.Scan(&sqid, &tier); err != nil { return nil, err } out[sqid] = tier } return out, rows.Err() } // Role is address's role in the account, "" when they are not a member. It is // the one permission check every account-scoped route makes. func (s *Store) Role(accountID int64, address string) (string, error) { var role string err := s.db.QueryRow(`SELECT role FROM memberships WHERE account_id = ? AND address = ?`, accountID, address).Scan(&role) if err == sql.ErrNoRows { return "", nil } if err != nil { return "", err } return role, nil } // Members lists an account's memberships, owners first then by address. func (s *Store) Members(accountID int64) ([]Account, error) { rows, err := s.db.Query(`SELECT address, role FROM memberships WHERE account_id = ? ORDER BY CASE role WHEN 'owner' THEN 0 ELSE 1 END, address`, accountID) if err != nil { return nil, err } defer rows.Close() var out []Account for rows.Next() { var a Account if err := rows.Scan(&a.Name, &a.Role); err != nil { // Name carries the address here return nil, err } a.ID = accountID out = append(out, a) } return out, rows.Err() } // AddMember adds or re-roles a member. Demoting the LAST owner is refused for // the same reason removing them is: it leaves an account nobody can // administer. Transfer ownership first. func (s *Store) AddMember(accountID int64, address, role string, now time.Time) error { if role != RoleOwner && role != RoleMember { return fmt.Errorf("console: invalid role %q", role) } tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() if role != RoleOwner { if err := lastOwnerGuard(tx, accountID, address); err != nil { return err } } if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at) VALUES (?, ?, ?, ?) ON CONFLICT(account_id, address) DO UPDATE SET role = excluded.role`, accountID, address, role, now.Unix()); err != nil { return err } return tx.Commit() } // RemoveMember drops a membership, refusing to remove the last owner. func (s *Store) RemoveMember(accountID int64, address string) error { tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() if err := lastOwnerGuard(tx, accountID, address); err != nil { return err } if _, err := tx.Exec(`DELETE FROM memberships WHERE account_id = ? AND address = ?`, accountID, address); err != nil { return err } return tx.Commit() } // lastOwnerGuard refuses a change that would leave an account with no owner. // Inside the caller's transaction, so the count and the write cannot be // separated by a concurrent second removal. func lastOwnerGuard(tx *sql.Tx, accountID int64, address string) error { var role string err := tx.QueryRow(`SELECT role FROM memberships WHERE account_id = ? AND address = ?`, accountID, address).Scan(&role) if err == sql.ErrNoRows { return nil // not a member: nothing to protect } if err != nil { return err } if role != RoleOwner { return nil } var owners int if err := tx.QueryRow(`SELECT COUNT(*) FROM memberships WHERE account_id = ? AND role = ?`, accountID, RoleOwner).Scan(&owners); err != nil { return err } if owners <= 1 { return fmt.Errorf("console: %s is the last owner of this account — transfer ownership first", address) } return nil } // SetLastAccount records where a user was last, so signing in lands them // back there. Upserts the users row: an invited address that has never // signed in still gets a landing spot. func (s *Store) SetLastAccount(address string, accountID int64) error { _, err := s.db.Exec(`INSERT INTO users (address, created_at, last_account) VALUES (?, ?, ?) ON CONFLICT(address) DO UPDATE SET last_account = excluded.last_account`, address, time.Now().Unix(), accountID) return err } // LastAccount is the account id a user was last in, 0 when there is no // history (a fresh user, or one whose only account was deleted). func (s *Store) LastAccount(address string) (int64, error) { var id int64 err := s.db.QueryRow(`SELECT last_account FROM users WHERE address = ?`, address).Scan(&id) if err == sql.ErrNoRows { return 0, nil } if err != nil { return 0, err } return id, nil } // CreateInvite mints a single-use, 7-day invitation for address, returning // the token the sealed Keymail link carries. func (s *Store) CreateInvite(accountID int64, address, invitedBy string, now time.Time) (string, error) { tok, err := randomToken() if err != nil { return "", err } _, err = s.db.Exec(`INSERT INTO invites (token, account_id, address, invited_by, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?)`, tok, accountID, address, invitedBy, now.Unix(), now.Add(InviteTTL).Unix()) if err != nil { return "", err } return tok, nil } // PendingInviteFor reads an invite without consuming it, behind the same // three gates AcceptInvite applies: unaccepted, addressed to address, and // unexpired. Any failure is one error, so the confirm page that shows it can // answer every refusal the same way. func (s *Store) PendingInviteFor(token, address string, now time.Time) (*Invite, error) { var i Invite var created, expires, accepted int64 err := s.db.QueryRow(`SELECT token, account_id, address, invited_by, created_at, expires_at, accepted_at FROM invites WHERE token = ?`, token).Scan(&i.Token, &i.AccountID, &i.Address, &i.InvitedBy, &created, &expires, &accepted) if err != nil { return nil, err } if accepted != 0 || i.Address != address || now.Unix() > expires { return nil, fmt.Errorf("console: that invite cannot be used by %s", address) } i.CreatedAt = time.Unix(created, 0).UTC() i.ExpiresAt = time.Unix(expires, 0).UTC() return &i, nil } // PendingInviteAddress is the address an invite was sent to, read without // consuming it, and only while the invite is unaccepted and unexpired: the // signed-out invite page fills it into the sign-in form. func (s *Store) PendingInviteAddress(token string, now time.Time) (string, bool) { var addr string var expires, accepted int64 err := s.db.QueryRow(`SELECT address, expires_at, accepted_at FROM invites WHERE token = ?`, token).Scan(&addr, &expires, &accepted) if err != nil || accepted != 0 || now.Unix() > expires { return "", false } return addr, true } // AcceptInvite consumes an invite and creates the membership, in one // transaction. Three gates, all inside it: the token must be unaccepted, the // address must be the one it was addressed to (an invite is not a bearer // token — the link lands in ONE keymail inbox and only that identity can use // it), and it must not have expired. func (s *Store) AcceptInvite(token, address string, now time.Time) (int64, error) { tx, err := s.db.Begin() if err != nil { return 0, err } defer tx.Rollback() var accountID, expires, accepted int64 var want string err = tx.QueryRow(`SELECT account_id, address, expires_at, accepted_at FROM invites WHERE token = ?`, token).Scan(&accountID, &want, &expires, &accepted) if err == sql.ErrNoRows { return 0, fmt.Errorf("console: no such invite") } if err != nil { return 0, err } if accepted != 0 { return 0, fmt.Errorf("console: that invite has already been used") } if want != address { return 0, fmt.Errorf("console: that invite was sent to a different address") } if now.Unix() > expires { return 0, fmt.Errorf("console: that invite has expired") } if _, err := tx.Exec(`UPDATE invites SET accepted_at = ? WHERE token = ?`, now.Unix(), token); err != nil { return 0, err } // An invite always grants member; owners are promoted deliberately. if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at) VALUES (?, ?, ?, ?) ON CONFLICT(account_id, address) DO NOTHING`, accountID, address, RoleMember, now.Unix()); err != nil { return 0, err } if err := tx.Commit(); err != nil { return 0, err } return accountID, nil } // RevokeInvite deletes a pending invite. Deleting rather than flagging is // deliberate: a revoked invite must be indistinguishable from one that never // existed, so a leaked link reveals nothing about the account. func (s *Store) RevokeInvite(token string) error { _, err := s.db.Exec(`DELETE FROM invites WHERE token = ?`, token) return err } // CountPendingInvites counts an account's unaccepted invitations that have // not expired at now: the ones handleInvite's cap is about. func (s *Store) CountPendingInvites(accountID int64, now time.Time) (int, error) { var n int err := s.db.QueryRow(`SELECT COUNT(*) FROM invites WHERE account_id = ? AND accepted_at = 0 AND expires_at >= ?`, accountID, now.Unix()).Scan(&n) return n, err } // PendingInvites lists an account's unaccepted, unexpired invitations. func (s *Store) PendingInvites(accountID int64) ([]Invite, error) { rows, err := s.db.Query(`SELECT token, account_id, address, invited_by, created_at, expires_at FROM invites WHERE account_id = ? AND accepted_at = 0 ORDER BY created_at, address`, accountID) if err != nil { return nil, err } defer rows.Close() var out []Invite for rows.Next() { var i Invite var created, expires int64 if err := rows.Scan(&i.Token, &i.AccountID, &i.Address, &i.InvitedBy, &created, &expires); err != nil { return nil, err } i.CreatedAt = time.Unix(created, 0).UTC() i.ExpiresAt = time.Unix(expires, 0).UTC() out = append(out, i) } return out, rows.Err() } // ReapAccount is materialization's verb for a tombstoned bucket manifest // found on a LATER pass than the one that first materialized the account // (see MaterializeAccounts §8 of the deletion-policy spec): memberships, // invites and apps rows are deleted outright — this account's authority and // its name-reservations are gone — but the accounts ROW itself is marked, // never removed, for the identical reason DeleteAccount marks it: deleting // the row forgets the sqid was ever seen, and a console that forgets is one // where a rewritten manifest un-tombstoning itself would re-birth the // account, same id, same sqid, with whoever wrote the manifest as owner. A // marked row closes that: AccountAnyBySqid always sees it, and nothing ever // re-materializes over it. // // An account tombstoned while still holding app rows was tombstoned by // something other than the console's own guarded path (DeleteAccount refuses // while apps remain) — so those rows are reaped with it rather than left // stranded pointing at a dead account. // // One transaction: a crash partway through must never leave memberships live // against an account whose row is already marked, which would be access with // nothing left to be a member OF. func (s *Store) ReapAccount(accountID int64, now time.Time) error { if accountID == OpsAccountID { return fmt.Errorf("console: the ops account cannot be reaped") } tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() for _, stmt := range []string{ `DELETE FROM memberships WHERE account_id = ?`, `DELETE FROM invites WHERE account_id = ?`, `DELETE FROM apps WHERE account_id = ?`, } { if _, err := tx.Exec(stmt, accountID); err != nil { return err } } // Email history and settings are keyed by sqid (mailevents.go). var sqid string switch err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); { case err == nil: if err := forgetMailAccount(context.Background(), tx, sqid); err != nil { return err } case err != sql.ErrNoRows: return err } if _, err := tx.Exec(`UPDATE users SET last_account = 0 WHERE last_account = ?`, accountID); err != nil { return err } if _, err := tx.Exec(`UPDATE accounts SET deleted_at = ? WHERE id = ? AND deleted_at = 0`, now.Unix(), accountID); err != nil { return err } return tx.Commit() } // ---- apps (claim-once names) ------------------------------------------------ // ErrTemporaryNameReserved refuses a permanent name in the tmp- namespace // (temporary static sites spec ruling 7). `console grant` prints it to an // operator, so it is written as a sentence. var ErrTemporaryNameReserved = errors.New("names starting with tmp- belong to temporary apps. A grant, a rename or a permanent claim cannot create one") // OpsAccountID is the built-in operator account's id, pinned by registry // migration on every deployment (its sqid is release.OpsAccount). const OpsAccountID = 1 // CreateApp claims an app name in the built-in ops account — the shape the // account-less /apps routes still use. CreateAppIn is the account-aware verb. func (s *Store) CreateApp(app, createdBy string, now time.Time) (created bool, err error) { return s.CreateAppIn(OpsAccountID, app, createdBy, now) } // CreateAppIn claims an app name WITHIN an account, once. created is false // when that account already has the name — another account holding it is not // a conflict, because app names are unique per account, never globally. func (s *Store) CreateAppIn(accountID int64, app, createdBy string, now time.Time) (created bool, err error) { return s.CreateAppInRegion(accountID, app, createdBy, "", "", now) } // CreateAppInRegion claims an app name with its region in the SAME insert: // an app region has no repair verb, so a separate best-effort write that // failed would leave the app at the home for good, silently (spec §5.3). func (s *Store) CreateAppInRegion(accountID int64, app, createdBy, region, source string, now time.Time) (created bool, err error) { res, err := s.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at, region, region_source) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(account_id, app) DO NOTHING`, app, accountID, createdBy, now.Unix(), region, source) if err != nil { return false, err } n, err := res.RowsAffected() return n > 0, err } // AppRegion reads an app's region and its source label. A missing row // reads as the home with no source. func (s *Store) AppRegion(accountID int64, app string) (region, source string, err error) { err = s.db.QueryRow(`SELECT region, region_source FROM apps WHERE account_id = ? AND app = ?`, accountID, app). Scan(®ion, &source) if errors.Is(err, sql.ErrNoRows) { return "", "", nil } return region, source, err } // nonHomeAppRegion is one live app holding a non-home region, for the // roll-forward discrepancy log (spec §5.7). type nonHomeAppRegion struct { Sqid, App, Region string CreatedAt time.Time } func (s *Store) NonHomeAppRegions() ([]nonHomeAppRegion, error) { rows, err := s.db.Query(`SELECT a.sqid, p.app, p.region, p.created_at FROM apps p JOIN accounts a ON a.id = p.account_id WHERE p.region != '' AND p.deleted_at = 0 ORDER BY a.sqid, p.app`) if err != nil { return nil, err } defer rows.Close() var out []nonHomeAppRegion for rows.Next() { var r nonHomeAppRegion var created int64 if err := rows.Scan(&r.Sqid, &r.App, &r.Region, &created); err != nil { return nil, err } r.CreatedAt = time.Unix(created, 0) out = append(out, r) } return out, rows.Err() } // AppsIn lists an account's claimed app names, sorted. Trashed apps are // filtered out — every door this feeds (API resolution, ship claim, account // page listings) is a door a trashed app must vanish from automatically, // without each caller having to remember to ask. TrashedAppsIn is the // dedicated listing for the Trash page itself. func (s *Store) AppsIn(accountID int64) ([]string, error) { rows, err := s.db.Query(`SELECT app FROM apps WHERE account_id = ? AND deleted_at = 0 ORDER BY app`, accountID) if err != nil { return nil, err } defer rows.Close() var out []string for rows.Next() { var a string if err := rows.Scan(&a); err != nil { return nil, err } out = append(out, a) } return out, rows.Err() } // HasAppIn reports whether an account already claims a LIVE app name. // Filtered the same way as AppsIn: a trashed name still occupies the row (see // CreateAppIn / the name-reservation rule) but must read as absent to a caller // asking "is this name in use right now". func (s *Store) HasAppIn(accountID int64, app string) (bool, error) { var n int err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ? AND deleted_at = 0`, accountID, app).Scan(&n) return n > 0, err } // SetAppPlacement stamps an app's placement fact — the store-of-record // fleets.go's appPlacement consults FIRST, ahead of the older route-derived // fallback (2026-08-06 ruling). target "" clears it back to unplaced. The // row is guaranteed to exist here: every caller reaches this only after // apiAccountApp's HasAppIn gate (place) or a just-succeeded CreateAppIn // (create-with-place), so this is a plain UPDATE, never an upsert. func (s *Store) claimRawSetAppPlacement(accountID int64, app, target string) error { _, err := s.db.Exec(`UPDATE apps SET placement = ? WHERE account_id = ? AND app = ?`, target, accountID, app) return err } // AppFlags reads the two per-app deployment facts the control-plane work // added: whether this app IS the control plane, and the path `carlos deploy` // should poll for a build the app's own process reports. // // One query for both because both doors that read them read them together // (the ship gate and the deploy watch), and a second round trip to the same // row buys nothing. func (s *Store) AppFlags(accountID int64, app string) (controlPlane bool, buildProbe string, err error) { err = s.db.QueryRow(`SELECT control_plane, build_probe FROM apps WHERE account_id = ? AND app = ?`, accountID, app).Scan(&controlPlane, &buildProbe) if errors.Is(err, sql.ErrNoRows) { // No row is not an error here: every caller has already been through // apiAccountApp's byte-identical 404, so reaching this with no row // means a race with a purge, and "not the control plane, no probe" is // the right reading of an app that no longer exists. return false, "", nil } return controlPlane, buildProbe, err } // SetAppControlPlane marks (or unmarks) an app as the control plane. Marking // refuses a temporary row (ErrTemporaryApp): the flag refuses destruction, // and a temporary app exists to be destroyed (temporary static sites §4). // The row is guaranteed to exist: both callers reach this straight after a // successful CreateAppIn or behind apiAccountApp's gate. func (s *Store) claimRawSetAppControlPlane(accountID int64, app string, on bool) error { if !on { _, err := s.db.Exec(`UPDATE apps SET control_plane = 0 WHERE account_id = ? AND app = ?`, accountID, app) return err } res, err := s.db.Exec(`UPDATE apps SET control_plane = 1 WHERE account_id = ? AND app = ? AND expires_at = 0`, accountID, app) if err != nil { return err } if n, _ := res.RowsAffected(); n == 0 { if l, err := s.AppLife(accountID, app); err == nil && l != nil && l.Temporary() { return ErrTemporaryApp } } return nil } // SetAppBuildProbe stamps the deploy watch's probe path. "" clears it back to // header-watching. func (s *Store) claimRawSetAppBuildProbe(accountID int64, app, path string) error { _, err := s.db.Exec(`UPDATE apps SET build_probe = ? WHERE account_id = ? AND app = ?`, path, accountID, app) return err } // AppPlacement reads an app's stored placement fact: "" for unplaced (or a // row this column predates — the ALTER above defaults every existing row to // "", indistinguishable from "never placed", which is exactly right: the // caller's route-derived fallback is what recovers a real historical // placement for those rows). A missing row (an app appPlacement is asked // about that this store has no claim for at all) also reads as "", not an // error — the same "absence is un-placed" answer, for a caller that has // already proven the row exists as much as one that hasn't. func (s *Store) AppPlacement(accountID int64, app string) (string, error) { var placement string err := s.db.QueryRow(`SELECT placement FROM apps WHERE account_id = ? AND app = ?`, accountID, app). Scan(&placement) if errors.Is(err, sql.ErrNoRows) { return "", nil } return placement, err } // TrashAppRow marks an app trashed. Trashing twice keeps the FIRST timestamp // (guarded by `deleted_at = 0`) — the cooloff clock starts once, and a second // trash call (racing UI, a retried request) must not push the purge deadline // out again. // // now is the MARKER's clock at every caller. A temporary row (expires_at > 0) // also gets its early-purge window in the same statement: purge_for is the // marker's deleted_at and purge_after is 3 days later (temporary static sites // spec §5). The window is bound to the marker's clock and to whether the row // was temporary when the marker was found; a permanent row gets 0 for both. func (s *Store) claimRawTrashAppRow(accountID int64, app string, now time.Time) error { at := now.Unix() _, err := s.db.Exec(`UPDATE apps SET deleted_at = ?, purge_for = CASE WHEN expires_at > 0 THEN ? ELSE 0 END, purge_after = CASE WHEN expires_at > 0 THEN ? ELSE 0 END WHERE account_id = ? AND app = ? AND deleted_at = 0`, at, at, at+temporaryCooloffSeconds, accountID, app) return err } // RestoreAppRow un-trashes an app and clears everything that only means // something while trashed (restoring, the early-purge window). Restoring // something that was never trashed is harmless. func (s *Store) RestoreAppRow(accountID int64, app string) error { _, err := s.db.Exec(`UPDATE apps SET deleted_at = 0, restoring = 0, purge_after = 0, purge_for = 0 WHERE account_id = ? AND app = ?`, accountID, app) return err } // PurgeAppRow deletes the row outright — the one place trash state actually // ends, and the only irreversible verb in this set. The row IS the name // reservation (see CreateAppIn), so this is also the moment the name frees // for reuse. // // Guarded by `deleted_at != 0`: a live row must be structurally unpurgeable, // because the console row and the bucket marker can diverge (a restore wrote // the row back while a marker delete failed elsewhere, or raced it) and the // auto-purge pass this feeds must never be the thing that frees a serving // app's name out from under it. func (s *Store) PurgeAppRow(accountID int64, app string) error { ctx := context.Background() tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() res, err := tx.ExecContext(ctx, `DELETE FROM apps WHERE account_id = ? AND app = ? AND deleted_at != 0`, accountID, app) if err != nil { return err } if n, _ := res.RowsAffected(); n > 0 { // The name is free from here, so nothing keyed by it may outlive the // row (mailevents.go): a later app of the same name starts clean. var sqid string if err := tx.QueryRowContext(ctx, `SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); err == nil { if err := forgetMailApp(ctx, tx, sqid, app); err != nil { return err } } else if err != sql.ErrNoRows { return err } // The same for the app's credentials (discussion #219, item 1). App // tokens of both kinds are bound (account, name), so a later app of // this name would inherit them unminted; they are DELETED rather than // revoked, because RenameAppIn and MoveApp refuse a name that still // carries any such row, revoked or not. Door keys are revoked rather // than deleted: the revision has to keep climbing past what boxes // already admitted, and the re-sign pass publishes the now-empty // record (apidoorkeys.go). if _, err := tx.ExecContext(ctx, `DELETE FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`, cliKindApp, cliKindService, accountID, app); err != nil { return err } if err := revokeAllDoorKeys(tx, accountID, app); err != nil { return err } } return tx.Commit() } // TrashedApp is one app in an account's Trash: past-tense on purpose, since a // trashed row is inert until restored or purged. type TrashedApp struct { App string DeletedAt time.Time // PurgeAfter is the row's early-purge deadline (Unix seconds); 0 means // the app was not temporary when trashed, so effectivePurgeAt falls // back to the ordinary 30-day cooloff. PurgeAfter int64 } // TrashedAppsIn lists an account's trashed apps, sorted by name — the Trash // page's listing, and the mirror image of AppsIn's "live only" filter. func (s *Store) TrashedAppsIn(accountID int64) ([]TrashedApp, error) { rows, err := s.db.Query(`SELECT app, deleted_at, purge_after FROM apps WHERE account_id = ? AND deleted_at != 0 ORDER BY app`, accountID) if err != nil { return nil, err } defer rows.Close() var out []TrashedApp for rows.Next() { var t TrashedApp var deletedAt int64 if err := rows.Scan(&t.App, &deletedAt, &t.PurgeAfter); err != nil { return nil, err } t.DeletedAt = time.Unix(deletedAt, 0).UTC() out = append(out, t) } return out, rows.Err() } // AppNameState reports, in one read, whether an account holds app live, in // the trash, or not at all — for a caller that must tell the three apart // without a restore or a trash landing between two separate questions. func (s *Store) AppNameState(accountID int64, app string) (live, trashed bool, err error) { var deletedAt int64 err = s.db.QueryRow(`SELECT deleted_at FROM apps WHERE account_id = ? AND app = ?`, accountID, app).Scan(&deletedAt) if errors.Is(err, sql.ErrNoRows) { return false, false, nil } if err != nil { return false, false, err } return deletedAt == 0, deletedAt != 0, nil } // HasTrashedAppIn reports whether an account holds a TRASHED row for app — // the check the claim-once door uses to explain a refusal ("that name is in // the trash") rather than just saying no. func (s *Store) HasTrashedAppIn(accountID int64, app string) (bool, error) { var n int err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ? AND deleted_at != 0`, accountID, app).Scan(&n) return n > 0, err } // AppRow is one apps row carrying its account's SQID — the coordinate the // bucket is keyed by. The console's own tables key apps by account id, but // every object belonging to an app lives under apps///, so a pass // that compares rows against the control plane needs both. type AppRow struct { AccountID int64 Sqid string App string Deleted bool // trashed here (deleted_at != 0), not purged // The lifecycle columns (temporary static sites spec §2), Unix seconds. DeletedAt int64 ExpiresAt int64 // 0 = permanent Restoring bool PurgeAfter int64 // 0 = no early purge PurgeFor int64 } // appRowCols is the SELECT list every AppRow query uses, in scanAppRows' // order: p is apps, a is accounts. const appRowCols = `p.account_id, a.sqid, p.app, p.deleted_at, p.expires_at, p.restoring, p.purge_after, p.purge_for` func scanAppRows(rows *sql.Rows) ([]AppRow, error) { defer rows.Close() var out []AppRow for rows.Next() { var r AppRow if err := rows.Scan(&r.AccountID, &r.Sqid, &r.App, &r.DeletedAt, &r.ExpiresAt, &r.Restoring, &r.PurgeAfter, &r.PurgeFor); err != nil { return nil, err } r.Deleted = r.DeletedAt != 0 out = append(out, r) } return out, rows.Err() } // AppRows lists every app row of every LIVE account, trashed ones included — // the input to MaterializeApps, which is the one caller. // // It has to be row-driven rather than bucket-driven: a purged app has no keys // left to list, so the only evidence it ever existed is the row still holding // its name. The join drops rows belonging to a marked account, which ReapAccount // already deletes — a straggler must never be converged against a dead // account's prefix, and freeing its name is that account's business, not this // pass's. func (s *Store) AppRows() ([]AppRow, error) { rows, err := s.db.Query(`SELECT ` + appRowCols + ` FROM apps p JOIN accounts a ON a.id = p.account_id WHERE a.deleted_at = 0 ORDER BY a.sqid, p.app`) if err != nil { return nil, err } return scanAppRows(rows) } // AppAccounts lists the accounts that claim an app NAME, ascending. Usually // one; several is legitimate (names are unique per account, not globally) and // is what makes MoveApp require an explicit source. func (s *Store) AppAccounts(app string) ([]int64, error) { rows, err := s.db.Query(`SELECT account_id FROM apps WHERE app = ? ORDER BY account_id`, app) if err != nil { return nil, err } defer rows.Close() var out []int64 for rows.Next() { var id int64 if err := rows.Scan(&id); err != nil { return nil, err } out = append(out, id) } return out, rows.Err() } // MoveApp re-homes a claimed app from one account to another — the console // half of `carlos accounts migrate`, which moves the bucket objects and the // registry routes but deliberately never opens this database. // // Idempotent: if the row is already at the destination there is nothing to // do, so an operator who is unsure whether the move landed can simply run it // again. Moving onto a name the destination already claims is REFUSED rather // than merged — those are two different apps, and one of them would vanish. func (s *Store) MoveApp(app string, from, to int64) error { tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() var atDest, atSrc int if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ? AND account_id = ?`, app, to).Scan(&atDest); err != nil { return err } if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&atSrc); err != nil { return err } if atDest > 0 { if atSrc > 0 { return fmt.Errorf("console: account %d already claims %q — moving onto it would merge two different apps", to, app) } return nil // already moved } if atSrc == 0 { return fmt.Errorf("console: account %d does not claim %q", from, app) } var srcExpires int64 if err := tx.QueryRow(`SELECT expires_at FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&srcExpires); err != nil { return err } if srcExpires > 0 { return ErrTemporaryApp } // App-scoped CLI tokens — legacy kind='app' AND kind='service' — are // bound (account, name), so they must follow the app or the running // deployment's credential 404s while retaining authority over the name // in the SOURCE account. Tokens already at the destination under this // name are a purged predecessor's leftovers — re-homing onto them would // hand this app a credential nobody minted for it, so that refuses, // exactly as RenameAppIn refuses. Both kinds must be counted here: a // service principal the guard cannot see is an orphaned credential a // LATER app of the same name inherits unminted (see // TestRenameAppMovesServicePrincipalToo for the RenameAppIn twin of this // hazard). var staleTokens int if err := tx.QueryRow(`SELECT COUNT(*) FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`, cliKindApp, cliKindService, to, app).Scan(&staleTokens); err != nil { return err } if staleTokens > 0 { return fmt.Errorf("console: the destination account still has app credentials for %q from a previous app — revoke them first", app) } // A second, narrower collision the guard above cannot see: // cli_tokens_service_name only requires a live kind='service' row's NAME // to be unique ACCOUNT-WIDE — it does not care whether the row is bound // to this app or to none at all (NewServicePrincipal's own uniqueness // check is the same shape). So a service principal travelling with `app` // can collide with an ACCOUNT-SCOPED credential already sitting at the // destination under the same name, and the guard above cannot catch that // — it only counts rows still bound to `app` there, and the colliding // row is not one of them. Left uncaught, the UPDATE below would trip the // unique index inside this transaction — safe (it rolls back) but with // SQLite's raw constraint-violation text rather than a sentence in this // guard's own voice — and a generic name like "hub" or "ci" makes it // reachable in practice. var collidingName string err = tx.QueryRow(`SELECT dst.name FROM cli_tokens dst WHERE dst.kind = ? AND dst.account_id = ? AND dst.revoked_at = 0 AND dst.name IN ( SELECT src.name FROM cli_tokens src WHERE src.kind = ? AND src.account_id = ? AND src.app = ? AND src.revoked_at = 0 ) LIMIT 1`, cliKindService, to, cliKindService, from, app).Scan(&collidingName) if err != nil && err != sql.ErrNoRows { return err } if err == nil { return fmt.Errorf("console: the destination account already holds a live service credential named %q — rename or revoke it first", collidingName) } var createdAt int64 if err := tx.QueryRow(`SELECT created_at FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&createdAt); err != nil { return err } if _, err := tx.Exec(`UPDATE apps SET account_id = ? WHERE app = ? AND account_id = ?`, to, app, from); err != nil { return err } if _, err := tx.Exec(`UPDATE cli_tokens SET account_id = ? WHERE kind IN (?, ?) AND account_id = ? AND app = ?`, to, cliKindApp, cliKindService, from, app); err != nil { return err } // Door keys cannot follow the app: each key names its account's sqid // (doorkey.Token), and the edge admits it only for routes of that account, // so the moved app's processes need new keys either way. What the move // must not leave behind is a LIVE key on either side (discussion #219, // item 1): the source's would be inherited by a later app of this name // there, and any at the destination are a purged predecessor's, which the // moved-in app would otherwise answer to. The destination has no row for // this name (checked above), so nothing live owns those keys. if err := revokeAllDoorKeys(tx, from, app); err != nil { return err } if err := revokeAllDoorKeys(tx, to, app); err != nil { return err } // Email history is keyed by the account's sqid, not its id, so it is // carried by sqid (mailevents.go). var fromSqid, toSqid string if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, from).Scan(&fromSqid); err != nil { return err } if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, to).Scan(&toSqid); err != nil { return err } if err := carryMailApp(context.Background(), tx, fromSqid, app, toSqid, app, createdAt); err != nil { return err } return tx.Commit() } // RenameAppIn renames an app that has never deployed. The CALLER holds the // wider refusals — nothing under the app's bucket prefix, no registry route // wearing the name — because an app name is a literal key segment in both, // and this database is the only store a rename may touch. Here the refusals // are this database's own: // // - The new name must be free in this account, live or trashed — a trashed // row still owns its name until purged, exactly as CreateAppIn treats it. // - Neither name may appear in the legacy owners table. Those grants are // keyed by bare name across ALL accounts, so rewriting them could hand // one account's grant to another, and renaming ONTO one would let the new // name inherit a grant nobody issued it. Claim-proof stays claim-proof. // - The new name must carry no app-scoped CLI tokens — legacy kind='app' // OR kind='service'. A purged app's tokens outlive its row, and a // credential that suddenly binds to a NEW app is authority nobody // minted. // // What follows the app: its audit rows — but only those written since THIS // app's row was created, because a purged predecessor of the same name left // its rows behind and its history is not this app's; and its app-scoped CLI // tokens (both kinds), which are bound (account, name) and would otherwise // 404 the running app while retaining authority over the freed name. The // rename itself is the caller's audit line. func (s *Store) RenameAppIn(accountID int64, from, to string) error { return s.renameAppIn(accountID, from, to, false) } // UndoRenameAppIn reverses a rename that just landed (handleAppRename's // post-commit re-check). It is the one path that may rename INTO the tmp- // namespace, because it only ever puts back the name the app already had: a // kept temporary app keeps its tmp- name, and renaming it away and then // undoing must be able to land. func (s *Store) UndoRenameAppIn(accountID int64, renamed, original string) error { return s.renameAppIn(accountID, renamed, original, true) } func (s *Store) renameAppIn(accountID int64, from, to string, undo bool) error { if !undo && release.IsTemporaryName(to) { return ErrTemporaryNameReserved } tx, err := s.db.Begin() if err != nil { return err } defer tx.Rollback() var createdAt, expiresAt int64 err = tx.QueryRow(`SELECT created_at, expires_at FROM apps WHERE account_id = ? AND app = ? AND deleted_at = 0`, accountID, from).Scan(&createdAt, &expiresAt) if err == sql.ErrNoRows { return fmt.Errorf("console: this account does not claim %q", from) } else if err != nil { return err } if expiresAt > 0 { return ErrTemporaryApp } var legacy int if err := tx.QueryRow(`SELECT COUNT(*) FROM owners WHERE app IN (?, ?)`, from, to).Scan(&legacy); err != nil { return err } if legacy > 0 { return fmt.Errorf("console: %q or %q carries a legacy owner grant — renaming across it is an operator act", from, to) } var taken int if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ?`, accountID, to).Scan(&taken); err != nil { return err } if taken > 0 { return fmt.Errorf("console: this account already has an app (or a trashed app) named %q", to) } var staleTokens int if err := tx.QueryRow(`SELECT COUNT(*) FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`, cliKindApp, cliKindService, accountID, to).Scan(&staleTokens); err != nil { return err } if staleTokens > 0 { return fmt.Errorf("console: %q still has app credentials from a previous app of that name — revoke them first", to) } if _, err := tx.Exec(`UPDATE apps SET app = ? WHERE account_id = ? AND app = ? AND deleted_at = 0`, to, accountID, from); err != nil { return err } if _, err := tx.Exec(`UPDATE audit SET app = ? WHERE account_id = ? AND app = ? AND created_at >= ?`, to, accountID, from, createdAt); err != nil { return err } if _, err := tx.Exec(`UPDATE cli_tokens SET app = ? WHERE kind IN (?, ?) AND account_id = ? AND app = ?`, to, cliKindApp, cliKindService, accountID, from); err != nil { return err } var sqid string if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); err != nil { return err } if err := carryMailApp(context.Background(), tx, sqid, from, sqid, to, createdAt); err != nil { return err } return tx.Commit() } // HasApp reports whether an app name is claimed in the apps table. func (s *Store) HasApp(app string) (bool, error) { var n int err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ?`, app).Scan(&n) return n > 0, err } // HasOwners reports whether ANY owner exists for app — CLI-granted apps // predate the apps table and must stay claim-proof. func (s *Store) HasOwners(app string) (bool, error) { var n int err := s.db.QueryRow(`SELECT COUNT(*) FROM owners WHERE app = ?`, app).Scan(&n) return n > 0, err } // ---- users ------------------------------------------------------------------ // EnsureUser records address as a console user if it isn't one yet. created // reports whether this call was the sign-up. func (s *Store) EnsureUser(address string, now time.Time) (created bool, err error) { res, err := s.db.Exec(`INSERT INTO users (address, created_at) VALUES (?, ?) ON CONFLICT(address) DO NOTHING`, address, now.Unix()) if err != nil { return false, err } n, err := res.RowsAffected() return n > 0, err } // SetOperator flips address's fleet-operator bit — box-side only, exactly // like an app grant: it works whether or not address has ever signed in, // upserting a users row if none exists yet (leaving created_at at "now" for // a brand-new row, untouched for an existing one). func (s *Store) SetOperator(address string, on bool) error { v := 0 if on { v = 1 } _, err := s.db.Exec(`INSERT INTO users (address, created_at, operator) VALUES (?, ?, ?) ON CONFLICT(address) DO UPDATE SET operator = excluded.operator`, address, time.Now().Unix(), v) return err } // IsOperator reports whether address holds the fleet-operator bit. An // address with no users row (never signed in) is not an operator. func (s *Store) IsOperator(address string) (bool, error) { var v int err := s.db.QueryRow(`SELECT operator FROM users WHERE address = ?`, address).Scan(&v) if err == sql.ErrNoRows { return false, nil } if err != nil { return false, err } return v != 0, nil } // Operators lists every address holding the fleet-operator bit, sorted — // the alert watcher's recipient list. Sorted so a pass mails people in a // stable order and a test can assert on it. func (s *Store) Operators() ([]string, error) { rows, err := s.db.Query(`SELECT address FROM users WHERE operator != 0 ORDER BY address`) if err != nil { return nil, err } defer rows.Close() var out []string for rows.Next() { var a string if err := rows.Scan(&a); err != nil { return nil, err } out = append(out, a) } return out, rows.Err() } // ---- alert watcher state (alertwatch.go) ------------------------------------ // AlertSeen reports the firing state the console last NOTIFIED address // about for (box, rule), and whether it has ever told them anything about // that rule. known=false is not the same as firing=false: a rule this // operator has never been told about fires as news, while one they were // last told was quiet stays quiet. // // The empty address is the watcher's synthetic "nobody was mailable" // recipient (alertwatch.go), not a real user. func (s *Store) AlertSeen(box, rule, address string) (firing bool, known bool, err error) { var v int err = s.db.QueryRow(`SELECT firing FROM alert_seen WHERE box = ? AND rule = ? AND address = ?`, box, rule, address).Scan(&v) if err == sql.ErrNoRows { return false, false, nil } if err != nil { return false, false, err } return v != 0, true, nil } // SetAlertSeen records that address has been told (box, rule) is in this // state. Written per recipient, only after THAT recipient's send succeeded, // so one dead grant leaves one row unrecorded and the next pass retries // that operator alone. func (s *Store) SetAlertSeen(box, rule, address string, firing bool) error { v := 0 if firing { v = 1 } _, err := s.db.Exec(`INSERT INTO alert_seen (box, rule, address, firing) VALUES (?, ?, ?, ?) ON CONFLICT(box, rule, address) DO UPDATE SET firing = excluded.firing`, box, rule, address, v) return err } // ---- move pass pages (movesrecovery.go) ------------------------------------- // MovePageAttempt is when the move pass last attempted the page for rule, // to the second, and whether it ever did. func (s *Store) MovePageAttempt(rule string) (time.Time, bool, error) { var at int64 err := s.db.QueryRow(`SELECT attempted_at FROM move_pages WHERE rule = ?`, rule).Scan(&at) if err == sql.ErrNoRows { return time.Time{}, false, nil } if err != nil { return time.Time{}, false, err } return time.Unix(at, 0).UTC(), true, nil } // SetMovePageAttempt records an attempt at the page for rule at. func (s *Store) SetMovePageAttempt(rule string, at time.Time) error { _, err := s.db.Exec(`INSERT INTO move_pages (rule, attempted_at) VALUES (?, ?) ON CONFLICT(rule) DO UPDATE SET attempted_at = excluded.attempted_at`, rule, at.Unix()) return err } // MoveLogPendingEntry is one move log entry the move pass owes. type MoveLogPendingEntry struct { Host, Op, Name, Incarnation string Body []byte QueuedAt time.Time Confirmed, Warned, Held bool Attempts int } // QueueMoveLog keeps an entry for the drainer. An entry already queued under // the name is kept as it was. An unconfirmed entry is not delivered until // ConfirmMoveLog. func (s *Store) QueueMoveLog(host, op, name, incarnation string, body []byte, confirmed bool, at time.Time) error { _, err := s.db.Exec(`INSERT INTO move_log_outbox (host, op, name, incarnation, body, queued_at, confirmed) VALUES (?, ?, ?, ?, ?, ?, ?) ON CONFLICT(host, op, name) DO NOTHING`, host, op, name, incarnation, body, at.Unix(), confirmed) return err } // ConfirmMoveLog marks a queued entry deliverable, due at once. func (s *Store) ConfirmMoveLog(host, op, name string) error { _, err := s.db.Exec(`UPDATE move_log_outbox SET confirmed = 1, next_at = 0 WHERE host = ? AND op = ? AND name = ?`, host, op, name) return err } func scanMoveLogRows(rows *sql.Rows) ([]MoveLogPendingEntry, error) { defer rows.Close() var out []MoveLogPendingEntry for rows.Next() { var e MoveLogPendingEntry var at int64 if err := rows.Scan(&e.Host, &e.Op, &e.Name, &e.Incarnation, &e.Body, &at, &e.Confirmed, &e.Attempts, &e.Warned, &e.Held); err != nil { return nil, err } e.QueuedAt = time.Unix(at, 0).UTC() out = append(out, e) } return out, rows.Err() } const moveLogColumns = `host, op, name, incarnation, body, queued_at, confirmed, attempts, warned, held` // MoveLogPending is one move's queued entries, oldest first. func (s *Store) MoveLogPending(host, op string) ([]MoveLogPendingEntry, error) { rows, err := s.db.Query(`SELECT `+moveLogColumns+` FROM move_log_outbox WHERE host = ? AND op = ? ORDER BY rowid`, host, op) if err != nil { return nil, err } return scanMoveLogRows(rows) } // MoveLogDue is up to limit queued entries whose next attempt is due at now, // the longest due first: every attempt that does not settle a row moves its // next attempt on, so due rows rotate through the batch. func (s *Store) MoveLogDue(now time.Time, limit int) ([]MoveLogPendingEntry, error) { rows, err := s.db.Query(`SELECT `+moveLogColumns+` FROM move_log_outbox WHERE held = 0 AND next_at <= ? ORDER BY next_at, rowid LIMIT ?`, now.Unix(), limit) if err != nil { return nil, err } return scanMoveLogRows(rows) } // RetryMoveLog records a failed attempt: the attempt count, the next // attempt's time and whether the row's warning has been given. func (s *Store) RetryMoveLog(host, op, name string, attempts int, next time.Time, warned bool) error { _, err := s.db.Exec(`UPDATE move_log_outbox SET attempts = ?, next_at = ?, warned = ? WHERE host = ? AND op = ? AND name = ?`, attempts, next.Unix(), warned, host, op, name) return err } // HoldMoveLog stops a row's delivery for good, keeping its bytes: the writer // refused it (it can never be written as it stands), and only its // incarnation's purge removes it. Its warning has been given. func (s *Store) HoldMoveLog(host, op, name string) error { _, err := s.db.Exec(`UPDATE move_log_outbox SET held = 1, warned = 1 WHERE host = ? AND op = ? AND name = ?`, host, op, name) return err } // PurgeMoveLogIncarnation drops every row of one instance incarnation: the // incarnation is retired or purged, so none of them can ever be written. func (s *Store) PurgeMoveLogIncarnation(host, incarnation string) error { _, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND incarnation = ?`, host, incarnation) return err } // SettleMoveLog drops an entry once it is written, found written, or can // never be. func (s *Store) SettleMoveLog(host, op, name string) error { _, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND op = ? AND name = ?`, host, op, name) return err } // DropUnconfirmedMoveLog deletes a queued entry only while it is still // unconfirmed: a confirmation that landed since the caller read the row // keeps it. func (s *Store) DropUnconfirmedMoveLog(host, op, name string) error { _, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND op = ? AND name = ? AND confirmed = 0`, host, op, name) return err } // ---- grants (the console's Keymail access tokens) --------------------------- // SetGrantToken stores the Keymail access token the user's OAuth approval // minted for the console — the instrument of future confirmation-link // sign-ins. Server-side only; it never reaches a browser. func (s *Store) SetGrantToken(address, token string, now time.Time) error { _, err := s.db.Exec(`INSERT INTO grants (address, token, created_at, updated_at) VALUES (?, ?, ?, ?) ON CONFLICT(address) DO UPDATE SET token = excluded.token, updated_at = excluded.updated_at`, address, token, now.Unix(), now.Unix()) return err } // GrantToken returns the stored Keymail token for address, "" when none. func (s *Store) GrantToken(address string) (string, error) { var tok string err := s.db.QueryRow(`SELECT token FROM grants WHERE address = ?`, address).Scan(&tok) if err == sql.ErrNoRows { return "", nil } return tok, err } // ---- sessions --------------------------------------------------------------- func hashToken(t string) string { h := sha256.Sum256([]byte(t)) return hex.EncodeToString(h[:]) } func randomToken() (string, error) { b := make([]byte, 32) if _, err := rand.Read(b); err != nil { return "", err } return base64.RawURLEncoding.EncodeToString(b), nil } // CreateSession mints a console session for address, returning the raw // token the cookie carries. Only the sha256 of it is stored. func (s *Store) CreateSession(address, keymailToken string, now time.Time) (string, error) { return s.CreateSessionMethod(address, keymailToken, MethodKeymail, now) } // CreateSessionMethod is CreateSession with the way the session was proved // recorded: MethodKeymail or MethodEmail. func (s *Store) CreateSessionMethod(address, keymailToken, method string, now time.Time) (string, error) { raw, err := randomToken() if err != nil { return "", err } _, err = s.db.Exec(`INSERT INTO sessions (token_hash, address, keymail_token, method, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?)`, hashToken(raw), address, keymailToken, method, now.Unix(), now.Add(SessionTTL).Unix()) if err != nil { return "", err } return raw, nil } // SessionAddress resolves a raw session token to its address; ok is false // for unknown or expired tokens. func (s *Store) SessionAddress(rawToken string, now time.Time) (string, bool) { var addr string var exp int64 err := s.db.QueryRow(`SELECT address, expires_at FROM sessions WHERE token_hash = ?`, hashToken(rawToken)).Scan(&addr, &exp) if err != nil || now.Unix() > exp { return "", false } return addr, true } // SessionInfo resolves a raw session token to its address and the method that // proved it; ok is false for unknown or expired tokens. func (s *Store) SessionInfo(rawToken string, now time.Time) (address, method string, ok bool) { var exp int64 err := s.db.QueryRow(`SELECT address, method, expires_at FROM sessions WHERE token_hash = ?`, hashToken(rawToken)).Scan(&address, &method, &exp) if err != nil || now.Unix() > exp { return "", "", false } return address, method, true } // DeleteSession removes a session by its raw token. func (s *Store) DeleteSession(rawToken string) error { _, err := s.db.Exec(`DELETE FROM sessions WHERE token_hash = ?`, hashToken(rawToken)) return err } // PurgeExpired drops expired sessions, confirmation tokens and email challenges — and, on the // same pass, the CLI login requests and tokens that have aged out (throttled // inside PurgeCLIAuth, which is also called from the login path). func (s *Store) PurgeExpired(now time.Time) error { if _, err := s.db.Exec(`DELETE FROM sessions WHERE expires_at < ?`, now.Unix()); err != nil { return err } if _, err := s.db.Exec(`DELETE FROM confirmations WHERE expires_at < ?`, now.Unix()); err != nil { return err } if _, err := s.db.Exec(`DELETE FROM email_challenges WHERE expires_at < ?`, now.Unix()); err != nil { return err } s.PurgeCLIAuth(now) return nil } // ---- confirmations (returning sign-in links) -------------------------------- // CreateConfirmation mints a single-use sign-in confirmation token for // address (15 min TTL), returning the raw token for the emailed link. Only // its hash is stored. func (s *Store) CreateConfirmation(address string, now time.Time) (string, error) { raw, err := randomToken() if err != nil { return "", err } _, err = s.db.Exec(`INSERT INTO confirmations (token_hash, address, created_at, expires_at) VALUES (?, ?, ?, ?)`, hashToken(raw), address, now.Unix(), now.Add(ConfirmationTTL).Unix()) if err != nil { return "", err } return raw, nil } // TakeConfirmation consumes a confirmation token: the row is deleted before // the expiry verdict (burn-then-check, keymail takeOAuthCode manners), so a // token can never be retried into validity. func (s *Store) TakeConfirmation(rawToken string, now time.Time) (string, bool) { tx, err := s.db.Begin() if err != nil { return "", false } defer tx.Rollback() var addr string var exp int64 err = tx.QueryRow(`SELECT address, expires_at FROM confirmations WHERE token_hash = ?`, hashToken(rawToken)).Scan(&addr, &exp) if err != nil { return "", false } if _, err := tx.Exec(`DELETE FROM confirmations WHERE token_hash = ?`, hashToken(rawToken)); err != nil { return "", false } if err := tx.Commit(); err != nil { return "", false } if now.Unix() > exp { return "", false } return addr, true } // ---- audit (recent activity) ------------------------------------------------ // Event is one audit row: something an owner did to an app through the // console, in the browser or through its API. Platform state stays in the // registry and the bucket; direct-bucket mutations do not enter this trail. type Event struct { App, Address, Action, TargetType, Target, Message, Detail string TargetURL string CreatedAt time.Time } // Activity is the structured half of an audit row. Action and target are // chosen by the server after it has authorised and applied the mutation; // Message is optional context supplied by the person making it. Detail is // retained for rows and callers which predate the structured shape. type Activity struct { Action, TargetType, Target, Message, Detail string } // RecordEvent appends one audit row. Callers treat it as best-effort: // losing an audit line must never fail the action it records. // // Keyed on (account, app), never the app name alone: two accounts can both // own a "blog", and a shared activity stream would show each of them the // other's actions — including the other account's member addresses, which is // a membership leak dressed up as a convenience. func (s *Store) claimRawRecordEvent(accountID int64, app, address, action, detail string, now time.Time) error { return s.RecordActivity(accountID, app, address, Activity{Action: action, Detail: detail}, now) } // RecordActivity appends a structured audit row. The account and app remain // separate arguments so callers cannot accidentally move either into // user-controlled metadata. func (s *Store) claimRawRecordActivity(accountID int64, app, address string, activity Activity, now time.Time) error { _, err := s.db.Exec(`INSERT INTO audit (account_id, app, address, action, target_type, target, message, detail, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, accountID, app, address, activity.Action, activity.TargetType, activity.Target, activity.Message, activity.Detail, now.Unix()) return err } // Events returns one account's audit rows for an app, newest first, capped at // limit. func (s *Store) Events(accountID int64, app string, limit int) ([]Event, error) { rows, err := s.db.Query(`SELECT app, address, action, target_type, target, message, detail, created_at FROM audit WHERE account_id = ? AND app = ? ORDER BY id DESC LIMIT ?`, accountID, app, limit) if err != nil { return nil, err } defer rows.Close() var out []Event for rows.Next() { var e Event var ts int64 if err := rows.Scan(&e.App, &e.Address, &e.Action, &e.TargetType, &e.Target, &e.Message, &e.Detail, &ts); err != nil { return nil, err } e.CreatedAt = time.Unix(ts, 0).UTC() out = append(out, e) } return out, rows.Err() } // AppRef names one app by its account's sqid, as the bucket keys it. type AppRef struct{ Sqid, App string } // AppsWithActionSince answers, once each, every (account, app) with an audit // row of action stamped at or after since. The run sweep's orphan // housekeeping lists runs/ only for apps with a recent run-start (S3 // change-index design §4.6). A plain read, like Events. func (s *Store) AppsWithActionSince(action string, since time.Time) ([]AppRef, error) { rows, err := s.db.Query(`SELECT DISTINCT accounts.sqid, audit.app FROM audit JOIN accounts ON accounts.id = audit.account_id WHERE audit.action = ? AND audit.created_at >= ? ORDER BY accounts.sqid, audit.app`, action, since.Unix()) if err != nil { return nil, err } defer rows.Close() var out []AppRef for rows.Next() { var r AppRef if err := rows.Scan(&r.Sqid, &r.App); err != nil { return nil, err } out = append(out, r) } return out, rows.Err() } // ---- keys ------------------------------------------------------------------- // SigningKey returns the console's long-lived envelope-signing key (ECDSA // P-256 — the curve keymail's app API pins in a grant), generating and // persisting it on first use. func (s *Store) SigningKey() (*ecdsa.PrivateKey, error) { load := func() (*ecdsa.PrivateKey, error) { var b64 string if err := s.db.QueryRow(`SELECT pkcs8 FROM keys WHERE name = 'keymail-sign'`).Scan(&b64); err != nil { return nil, err } der, err := base64.StdEncoding.DecodeString(b64) if err != nil { return nil, fmt.Errorf("console: stored signing key: %v", err) } key, err := x509.ParsePKCS8PrivateKey(der) if err != nil { return nil, fmt.Errorf("console: stored signing key: %v", err) } ec, ok := key.(*ecdsa.PrivateKey) if !ok { return nil, fmt.Errorf("console: stored signing key is not ECDSA") } return ec, nil } if k, err := load(); err == nil { return k, nil } else if err != sql.ErrNoRows { return nil, err } priv, err := ecdsa.GenerateKey(elliptic.P256(), rand.Reader) if err != nil { return nil, err } der, err := x509.MarshalPKCS8PrivateKey(priv) if err != nil { return nil, err } // INSERT OR IGNORE + re-load: if a racing opener won, everyone converges // on the row that stuck. if _, err := s.db.Exec(`INSERT OR IGNORE INTO keys (name, pkcs8, created_at) VALUES ('keymail-sign', ?, ?)`, base64.StdEncoding.EncodeToString(der), time.Now().Unix()); err != nil { return nil, err } return load() } func (s *Store) RecordActivity(accountID int64, app, address string, activity Activity, now time.Time) (claimResult0 error) { return s.RecordActivityContext(context.Background(), accountID, app, address, activity, now) } func (s *Store) RecordActivityContext(claimContext context.Context, accountID int64, app, address string, activity Activity, now time.Time) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.RecordActivity", func(claimView *Store) error { claimResult0 = claimView.claimRawRecordActivity(accountID, app, address, activity, now) return claimResult0 }) return } func (s *Store) RecordEvent(accountID int64, app, address, action, detail string, now time.Time) (claimResult0 error) { return s.RecordEventContext(context.Background(), accountID, app, address, action, detail, now) } func (s *Store) RecordEventContext(claimContext context.Context, accountID int64, app, address, action, detail string, now time.Time) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.RecordEvent", func(claimView *Store) error { claimResult0 = claimView.claimRawRecordEvent(accountID, app, address, action, detail, now) return claimResult0 }) return } func (s *Store) SetAppBuildProbe(accountID int64, app, path string) (claimResult0 error) { return s.SetAppBuildProbeContext(context.Background(), accountID, app, path) } func (s *Store) SetAppBuildProbeContext(claimContext context.Context, accountID int64, app, path string) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppBuildProbe", func(claimView *Store) error { claimResult0 = claimView.claimRawSetAppBuildProbe(accountID, app, path) return claimResult0 }) return } func (s *Store) SetAppControlPlane(accountID int64, app string, on bool) (claimResult0 error) { return s.SetAppControlPlaneContext(context.Background(), accountID, app, on) } func (s *Store) SetAppControlPlaneContext(claimContext context.Context, accountID int64, app string, on bool) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppControlPlane", func(claimView *Store) error { claimResult0 = claimView.claimRawSetAppControlPlane(accountID, app, on) return claimResult0 }) return } func (s *Store) SetAppPlacement(accountID int64, app, target string) (claimResult0 error) { return s.SetAppPlacementContext(context.Background(), accountID, app, target) } func (s *Store) SetAppPlacementContext(claimContext context.Context, accountID int64, app, target string) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppPlacement", func(claimView *Store) error { claimResult0 = claimView.claimRawSetAppPlacement(accountID, app, target) return claimResult0 }) return } func (s *Store) TrashAppRow(accountID int64, app string, now time.Time) (claimResult0 error) { return s.TrashAppRowContext(context.Background(), accountID, app, now) } func (s *Store) TrashAppRowContext(claimContext context.Context, accountID int64, app string, now time.Time) (claimResult0 error) { claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.TrashAppRow", func(claimView *Store) error { claimResult0 = claimView.claimRawTrashAppRow(accountID, app, now) return claimResult0 }) return }