carlos / platform Public

Download this file

1	// Package console is the platform console behind
2	// https://platform.carlosframework.com: sign up / sign in with Keymail, see
3	// your apps and their live platform state. Slice 1 is strictly READ-ONLY
4	// against platform state — the console reads the registry and the release
5	// bucket, and mutates nothing but its own little SQLite.
6	package console
7
8	import (
9	"context"
10	"crypto/ecdsa"
11	"crypto/elliptic"
12	"crypto/rand"
13	"crypto/sha256"
14	"crypto/x509"
15	"database/sql"
16	"encoding/base64"
17	"encoding/hex"
18	"errors"
19	"fmt"
20	"log"
21	"os"
22	"path/filepath"
23	"strings"
24	"sync/atomic"
25	"time"
26
27	"github.com/carlosframework/platform/internal/release"
28	_ "modernc.org/sqlite"
29	)
30
31	const (
32	// SessionTTL is how long a console session cookie stays valid.
33	SessionTTL = 30 * 24 * time.Hour
34	// ConfirmationTTL bounds a returning-sign-in confirmation link.
35	ConfirmationTTL = 15 * time.Minute
36	)
37
38	// Store is the console's own SQLite: who exists, who owns what, who is
39	// signed in, and the console's Keymail credentials. Platform state (routes,
40	// channels) is never in here — that is read live from the registry and the
41	// deployment bucket.
42	type Store struct {
43	claimBucket release.Store
44	claimSQLAdmit func(context.Context, string, string, string, func(context.Context) error) error
45
46	// db is what every query runs on: the plain pool, or, inside
47	// WithAppMutation, the bound view (store_gate.go).
48	db sqlHandle
49	// rawPool and rawBoot are the raw handles, private to Open, Close,
50	// withClaimSQL and the boot seam (claim_sql_census_test.go keeps them
51	// there). Flag-off, both are db itself.
52	rawPool *sql.DB
53	rawBoot *sql.DB
54	// gate is the structural SQL gate; nil flag-off. bound is set on the
55	// narrow view withClaimSQL hands its callback.
56	gate *claimGate
57	bound *claimBinding
58
59	// mailEventsPruned is when mail_events was last trimmed to its
60	// retention window, as unix seconds (mailevents.go). Zero means not yet
61	// this process, which is what makes the first event after a restart
62	// prune. The stamps are pointers so a Store view shares them with the
63	// Store it was cut from.
64	mailEventsPruned *atomic.Int64
65	// mailPushPruned is the same stamp for the push queue (mailpush.go).
66	mailPushPruned *atomic.Int64
67
68	// codeKey is the in-memory HMAC key for emailed sign-in codes
69	// (emailsignin_store.go). Minted per process and never written down, so a
70	// database read cannot recover a code. A Store view shares it.
71	codeKey []byte
72	// codeEpoch is a random per-process label stored in front of each
73	// code_mac, so a code minted by an earlier process is recognised as such.
74	codeEpoch string
75	}
76
77	func newStore(db *sql.DB) *Store {
78	key := make([]byte, 32)
79	// crypto/rand.Read does not return an error (it aborts the process if the
80	// system source fails), so there is no weaker key to fall back to.
81	_, _ = rand.Read(key)
82	ep := make([]byte, 8)
83	_, _ = rand.Read(ep)
84	return &Store{codeEpoch: hex.EncodeToString(ep), db: db, rawPool: db, rawBoot: db, mailEventsPruned: new(atomic.Int64), mailPushPruned: new(atomic.Int64), codeKey: key}
85	}
86
87	// Open opens (creating if needed) the console DB at path.
88	func Open(path string) (*Store, error) {
89	if dir := filepath.Dir(path); dir != "." && dir != "" {
90	if err := os.MkdirAll(dir, 0o755); err != nil {
91	return nil, err
92	}
93	}
94	db, err := sql.Open("sqlite", path)
95	if err != nil {
96	return nil, err
97	}
98	// One writer at a time keeps WAL happy.
99	db.SetMaxOpenConns(1)
100	// busy_timeout FIRST: journal_mode=WAL takes a lock, and with no timeout
101	// set yet a concurrent opener fails immediately with SQLITE_BUSY.
102	for _, p := range []string{
103	"PRAGMA busy_timeout=5000",
104	"PRAGMA journal_mode=WAL",
105	"PRAGMA synchronous=NORMAL",
106	} {
107	if _, err := db.Exec(p); err != nil {
108	db.Close()
109	return nil, err
110	}
111	}
112	s := newStore(db)
113	if err := s.migrate(); err != nil {
114	db.Close()
115	return nil, err
116	}
117	return s, nil
118	}
119
120	// Close releases the underlying database handles. A WithAppMutation view
121	// cannot close the store it was cut from.
122	func (s *Store) Close() error {
123	if s.bound != nil {
124	return fmt.Errorf("%w: a claim SQL view cannot close the store", ErrClaimSQLScope)
125	}
126	err := s.rawPool.Close()
127	if s.rawBoot != s.rawPool {
128	if berr := s.rawBoot.Close(); err == nil {
129	err = berr
130	}
131	}
132	return err
133	}
134
135	// migrate is additive-only (CREATE TABLE IF NOT EXISTS); future columns go
136	// in the ALTER list, whose "duplicate column" error is swallowed so the list
137	// stays idempotent.
138	func (s *Store) migrate() error {
139	for _, stmt := range []string{
140	`CREATE TABLE IF NOT EXISTS owners (
141	address TEXT NOT NULL,
142	app TEXT NOT NULL,
143	created_at INTEGER NOT NULL,
144	UNIQUE(address, app)
145	);`,
146	`CREATE TABLE IF NOT EXISTS sessions (
147	token_hash TEXT PRIMARY KEY,
148	address TEXT NOT NULL,
149	keymail_token TEXT NOT NULL DEFAULT '',
150	created_at INTEGER NOT NULL,
151	expires_at INTEGER NOT NULL
152	);`,
153	`CREATE TABLE IF NOT EXISTS users (
154	address TEXT PRIMARY KEY,
155	created_at INTEGER NOT NULL
156	);`,
157	`CREATE TABLE IF NOT EXISTS grants (
158	address TEXT PRIMARY KEY,
159	token TEXT NOT NULL,
160	created_at INTEGER NOT NULL,
161	updated_at INTEGER NOT NULL
162	);`,
163	`CREATE TABLE IF NOT EXISTS confirmations (
164	token_hash TEXT PRIMARY KEY,
165	address TEXT NOT NULL,
166	created_at INTEGER NOT NULL,
167	expires_at INTEGER NOT NULL
168	);`,
169	`CREATE TABLE IF NOT EXISTS keys (
170	name TEXT PRIMARY KEY,
171	pkcs8 TEXT NOT NULL,
172	created_at INTEGER NOT NULL
173	);`,
174	`CREATE TABLE IF NOT EXISTS apps (
175	app TEXT PRIMARY KEY,
176	created_by TEXT NOT NULL,
177	created_at INTEGER NOT NULL
178	);`,
179	`CREATE TABLE IF NOT EXISTS audit (
180	id INTEGER PRIMARY KEY AUTOINCREMENT,
181	app TEXT NOT NULL,
182	address TEXT NOT NULL,
183	action TEXT NOT NULL,
184	detail TEXT NOT NULL DEFAULT '',
185	created_at INTEGER NOT NULL
186	);`,
187	// ---- multi-account -------------------------------------------------
188	// The account is the tenancy primitive: it owns apps and has members.
189	// id is MINTED into the bucket (domains.MintAccount) and materialized
190	// here, never autoincrement — see account.go.
191	// name is deliberately NOT unique: identity is the sqid, so there is
192	// nothing to squat.
193	`CREATE TABLE IF NOT EXISTS accounts (
194	id INTEGER PRIMARY KEY,
195	sqid TEXT NOT NULL UNIQUE,
196	name TEXT NOT NULL,
197	created_at INTEGER NOT NULL
198	);`,
199	// Membership replaces the per-app owners grant: app access follows
200	// from account membership. Exactly two roles, owner and member.
201	`CREATE TABLE IF NOT EXISTS memberships (
202	account_id INTEGER NOT NULL,
203	address TEXT NOT NULL,
204	role TEXT NOT NULL,
205	added_at INTEGER NOT NULL,
206	PRIMARY KEY (account_id, address)
207	);`,
208	// Invites are single-use, expiring, revocable. The token is stored in
209	// the clear (unlike sessions/confirmations): it is delivered into the
210	// addressee's own Keymail inbox and is worthless without also being
211	// signed in as that address, and an owner has to be able to LIST and
212	// revoke a specific pending invite.
213	`CREATE TABLE IF NOT EXISTS invites (
214	token TEXT PRIMARY KEY,
215	account_id INTEGER NOT NULL,
216	address TEXT NOT NULL,
217	invited_by TEXT NOT NULL,
218	created_at INTEGER NOT NULL,
219	expires_at INTEGER NOT NULL,
220	accepted_at INTEGER NOT NULL DEFAULT 0
221	);`,
222	// The alert watcher's memory (alertwatch.go): the firing state the
223	// console last told ONE operator about, per (box, rule, address). A
224	// row's absence means "never told them", which is why AlertSeen
225	// reports known separately — a first sighting of a firing rule is
226	// news, a first sighting of a quiet one is not.
227	//
228	// Per-recipient rather than per-rule because delivery fails per
229	// recipient: one operator's revoked keymail grant must not re-page
230	// every healthy operator on the next pass. The empty address is a
231	// synthetic row meaning "nobody was mailable when this transition
232	// happened" (see alertwatch.go).
233	`CREATE TABLE IF NOT EXISTS alert_seen (
234	box TEXT NOT NULL,
235	rule TEXT NOT NULL,
236	address TEXT NOT NULL,
237	firing INTEGER NOT NULL,
238	PRIMARY KEY (box, rule, address)
239	);`,
240	// Per-tenant SES send/bounce/complaint/reject counts, one row per
241	// (account, app, day) — the abuse signal and the only source of a
242	// per-tenant send count (tenant-email spec §9, Task 16). Keyed by
243	// account (the sqid, matching mail.Record's own acct string), not
244	// account_id: this table is written from handleSESEvents, which
245	// resolves straight off mail.ListDomains and never looks up a
246	// console account row to get one.
247	`CREATE TABLE IF NOT EXISTS mail_counts (
248	account TEXT NOT NULL,
249	app TEXT NOT NULL,
250	day TEXT NOT NULL, -- UTC YYYY-MM-DD
251	sends INTEGER NOT NULL DEFAULT 0,
252	bounces INTEGER NOT NULL DEFAULT 0,
253	complaints INTEGER NOT NULL DEFAULT 0,
254	rejects INTEGER NOT NULL DEFAULT 0,
255	PRIMARY KEY (account, app, day)
256	);`,
257	// Per-SENDER counts (senders design §8). A new table rather than a
258	// widened key, because SQLite cannot ALTER a primary key. Every count
259	// is written here from now on; mail_counts stays, unwritten, and is
260	// dropped a release later by DropLegacyMailCounts.
261	//
262	// day is a UTC date string, not a timestamp, exactly as mail_counts'
263	// is — which is why this table cannot join mailAppTables blindly: the
264	// rename, purge and reap sweeps key on mailAppTimeColumn and there is
265	// no such column here.
266	`CREATE TABLE IF NOT EXISTS mail_sender_counts (
267	account TEXT NOT NULL,
268	app TEXT NOT NULL,
269	sender TEXT NOT NULL,
270	day TEXT NOT NULL, -- UTC YYYY-MM-DD
271	sends INTEGER NOT NULL DEFAULT 0,
272	bounces INTEGER NOT NULL DEFAULT 0,
273	complaints INTEGER NOT NULL DEFAULT 0,
274	rejects INTEGER NOT NULL DEFAULT 0,
275	deliveries INTEGER NOT NULL DEFAULT 0,
276	PRIMARY KEY (account, app, sender, day)
277	);`,
278	// What happened to each message, per (account, app) — one row per
279	// recipient per SES notification (mailevents.go). The counts above
280	// say how many bounced today; this says which message, to whom, and
281	// what the receiving server answered, so that a member can find out
282	// and an app can be told. Keyed like mail_counts, by the account
283	// sqid, for the same reason.
284	//
285	// notice is SNS's own MessageId for the notification: SNS delivers
286	// at least once, and a notification delivered twice is one row, not
287	// two. by_sender records HOW the row was attributed — 1 when SES
288	// named this app's own sending identity, 0 when the sending domain
289	// was all there was to go on and every app sharing it got a copy.
290	// It is kept because the difference matters to anything that hands
291	// a row on: only a row SES attributed to the app itself is that
292	// app's to be told about.
293	//
294	// Recipient addresses are personal data, so the table is bounded in
295	// time (mailEventsRetention) rather than kept for ever.
296	`CREATE TABLE IF NOT EXISTS mail_events (
297	account TEXT NOT NULL,
298	app TEXT NOT NULL,
299	notice TEXT NOT NULL,
300	recipient TEXT NOT NULL,
301	message_id TEXT NOT NULL,
302	type TEXT NOT NULL,
303	status TEXT NOT NULL DEFAULT '',
304	diagnostic TEXT NOT NULL DEFAULT '',
305	detail TEXT NOT NULL DEFAULT '',
306	by_sender INTEGER NOT NULL DEFAULT 0,
307	at INTEGER NOT NULL,
308	received_at INTEGER NOT NULL,
309	PRIMARY KEY (account, app, notice, recipient)
310	);`,
311	`CREATE INDEX IF NOT EXISTS mail_events_by_time ON mail_events (account, app, at);`,
312	`CREATE INDEX IF NOT EXISTS mail_events_by_message ON mail_events (account, app, message_id);`,
313	`CREATE INDEX IF NOT EXISTS mail_events_by_age ON mail_events (received_at);`,
314	// Where an app wants to be told about its email (mailpush.go): one
315	// path per app, POSTed on the app's own platform host. Absent row =
316	// the app is not told, which is every app until it asks.
317	//
318	// failing_since is when the app's current run of failed deliveries
319	// began (0: none), and suspended_at when the push was suspended for
320	// a day of them (0: not suspended) — see mailPushSuspendAfter. Both
321	// clear when the push is turned on again.
322	`CREATE TABLE IF NOT EXISTS mail_event_hooks (
323	account TEXT NOT NULL,
324	app TEXT NOT NULL,
325	path TEXT NOT NULL,
326	set_by TEXT NOT NULL,
327	set_at INTEGER NOT NULL,
328	failing_since INTEGER NOT NULL DEFAULT 0,
329	suspended_at INTEGER NOT NULL DEFAULT 0,
330	suspended_why TEXT NOT NULL DEFAULT '',
331	PRIMARY KEY (account, app)
332	);`,
333	// The queue of those notifications, one row per SES notification per
334	// app, retried until delivered or given up on. The payload carries
335	// recipients' addresses, so the row lives no longer than the log it
336	// was cut from (mailEventsRetention), delivered or not. notice is
337	// UNIQUE with the app so SNS redelivering a notification queues it
338	// once.
339	`CREATE TABLE IF NOT EXISTS mail_event_deliveries (
340	id INTEGER PRIMARY KEY,
341	account TEXT NOT NULL,
342	app TEXT NOT NULL,
343	notice TEXT NOT NULL,
344	payload TEXT NOT NULL,
345	attempts INTEGER NOT NULL DEFAULT 0,
346	next_at INTEGER NOT NULL,
347	created_at INTEGER NOT NULL,
348	delivered_at INTEGER NOT NULL DEFAULT 0,
349	abandoned_at INTEGER NOT NULL DEFAULT 0,
350	last_attempt_at INTEGER NOT NULL DEFAULT 0,
351	last_status INTEGER NOT NULL DEFAULT 0,
352	last_error TEXT NOT NULL DEFAULT '',
353	abandoned_why TEXT NOT NULL DEFAULT '',
354	UNIQUE (account, app, notice)
355	);`,
356	// A pass seeks each app's oldest due rows through this index, so its
357	// cost follows the number of apps asking, not the size of anyone's
358	// backlog.
359	`CREATE INDEX IF NOT EXISTS mail_event_deliveries_app_due ON mail_event_deliveries (account, app, delivered_at, abandoned_at, next_at);`,
360	`CREATE INDEX IF NOT EXISTS mail_event_deliveries_by_age ON mail_event_deliveries (created_at);`,
361	// One row, written the first time a request arrives carrying a valid
362	// edge forwarded-for proof (edgeproof.go). From then on a loopback
363	// peer's X-Forwarded-For is believed only with a proof beside it.
364	`CREATE TABLE IF NOT EXISTS edge_proof_seen (
365	id INTEGER PRIMARY KEY CHECK (id = 1),
366	seen_at INTEGER NOT NULL
367	);`,
368	// The console's move pass pages (movesrecovery.go): the last
369	// attempted page per finding, so a repeating page keeps a full
370	// hour between attempts across restarts, and a once-only page is
371	// never repeated.
372	`CREATE TABLE IF NOT EXISTS move_pages (
373	rule TEXT PRIMARY KEY,
374	attempted_at INTEGER NOT NULL
375	);`,
376	// The move pass's outbox (movesrecovery.go, Plan 6 Task 3): move log
377	// entries the pass owes and has not yet written, with their exact
378	// bytes and the instance incarnation their move's evidence carried,
379	// until the recovery pass's drainer writes them, finds them written,
380	// or finds the incarnation gone. An unconfirmed row waits for the
381	// write it records to be confirmed; next_at is the earliest next
382	// attempt, and warned marks the one journal warning an hour-old row
383	// gets. This is the table's only definition: it exists only on this
384	// unmerged branch, so no console database has an older shape.
385	`CREATE TABLE IF NOT EXISTS move_log_outbox (
386	host TEXT NOT NULL,
387	op TEXT NOT NULL,
388	name TEXT NOT NULL,
389	incarnation TEXT NOT NULL,
390	body BLOB NOT NULL,
391	queued_at INTEGER NOT NULL,
392	confirmed INTEGER NOT NULL,
393	attempts INTEGER NOT NULL DEFAULT 0,
394	next_at INTEGER NOT NULL DEFAULT 0,
395	warned INTEGER NOT NULL DEFAULT 0,
396	held INTEGER NOT NULL DEFAULT 0,
397	PRIMARY KEY (host, op, name)
398	);`,
399	// Plans (plans-caps-packs spec §4, §7): one row per account naming
400	// its plan and packs. revision moves on every change;
401	// published_revision/published_catalog say which revision, under
402	// which catalog, the bucket's limits record already carries.
403	`CREATE TABLE IF NOT EXISTS account_plans (
404	account_id INTEGER PRIMARY KEY,
405	plan TEXT NOT NULL,
406	packs TEXT NOT NULL DEFAULT '{}',
407	source TEXT NOT NULL,
408	sponsored TEXT NOT NULL DEFAULT '',
409	reason TEXT NOT NULL DEFAULT '',
410	revision INTEGER NOT NULL,
411	published_revision INTEGER NOT NULL DEFAULT 0,
412	published_catalog TEXT NOT NULL DEFAULT '',
413	updated_by TEXT NOT NULL,
414	updated_at INTEGER NOT NULL
415	);`,
416	} {
417	if _, err := s.db.Exec(stmt); err != nil {
418	return err
419	}
420	}
421	// Additive column changes land here, forgiving "duplicate column" so
422	// re-running is always safe.
423	for _, stmt := range []string{
424	`ALTER TABLE users ADD COLUMN operator INTEGER NOT NULL DEFAULT 0;`,
425	// deliveries (tenant-email, ruling 2a): SES already publishes
426	// Delivery to the event destination and the ingest already
427	// verifies and parses it — this stops throwing the answer away.
428	// Display-only: the cap and the bounce/complaint thresholds are
429	// deliberately NOT rebased on it, because SES's own account-review
430	// math is bounces over SENDS, and a limiter that measures something
431	// else from what SES reviews on is a limiter that trips at the
432	// wrong time.
433	`ALTER TABLE mail_counts ADD COLUMN deliveries INTEGER NOT NULL DEFAULT 0;`,
434	// Where a returning user lands: the account they were last in.
435	`ALTER TABLE users ADD COLUMN last_account INTEGER NOT NULL DEFAULT 0;`,
436	// Apps belong to an account. The column lands with default 1 (ops), so
437	// every already-claimed app keeps working; the PRIMARY KEY stays `app`
438	// until the migration verb rebuilds the table, because changing a PK
439	// is not an additive migration.
440	`ALTER TABLE apps ADD COLUMN account_id INTEGER NOT NULL DEFAULT 1;`,
441	// The audit trail is per (account, app) too. Existing rows default to
442	// ops, which is where every pre-accounts app lived — so no history is
443	// lost and none of it lands in somebody else's account.
444	`ALTER TABLE audit ADD COLUMN account_id INTEGER NOT NULL DEFAULT 1;`,
445	// Structured activity is additive to the original prose-only detail.
446	// Empty fields mean an old row; Detail remains its lossless fallback.
447	`ALTER TABLE audit ADD COLUMN target_type TEXT NOT NULL DEFAULT '';`,
448	`ALTER TABLE audit ADD COLUMN target TEXT NOT NULL DEFAULT '';`,
449	`ALTER TABLE audit ADD COLUMN message TEXT NOT NULL DEFAULT '';`,
450	// Trash state for apps (30-day cooloff; the row IS the name
451	// reservation, so it survives trash and dies at purge) and tombstone
452	// MEMORY for accounts: a marked account row is never re-materialized,
453	// which is what stops a rewritten bucket manifest resurrecting a
454	// deleted account — see MaterializeAccounts.
455	`ALTER TABLE apps ADD COLUMN deleted_at INTEGER NOT NULL DEFAULT 0;`,
456	`ALTER TABLE accounts ADD COLUMN deleted_at INTEGER NOT NULL DEFAULT 0;`,
457	// Placement (customer-fleets, 2026-08-06 ruling): the STORE OF RECORD
458	// for which fleet (if any) an app's routes/instances should run on —
459	// "" = unplaced. Route-derivation (fleets.go's appPlacement) used to
460	// be the only source, which left a freshly-created app with zero
461	// routes unable to carry a placement at all (`carlos apps create
462	// -place` would be a silent no-op). This column is what `carlos apps
463	// place` and apps-create's optional place field both write; every
464	// existing row defaults to "", identical to "never placed" under the
465	// old route-only reading.
466	`ALTER TABLE apps ADD COLUMN placement TEXT NOT NULL DEFAULT '';`,
467	// Fleet pool assignment (economics-v2, Task 4): the operator-set
468	// pricing/capacity tier driving Task 5's placement into a fleet pool.
469	// "" means "never assigned" — every existing row defaults to it,
470	// identical to "unassigned" under the old tier-less reading. See
471	// SetAccountTier for the fixed set of non-empty values.
472	`ALTER TABLE accounts ADD COLUMN tier TEXT NOT NULL DEFAULT '';`,
473	// Control-plane apps (console-as-an-app spec §6): the flag REFUSES
474	// destruction — trash, purge, rename, transfer — and refuses a
475	// service principal's promote. It deliberately does NOT gate a
476	// person's promote: `stable`'s fresh-auth 403 has no API-side
477	// step-up and `carlos deploy` log.Fatalfs on a failed promote AFTER
478	// the ship has landed (deploy.go:206-208), so gating promotion would
479	// break `carlos deploy -app console` — the command the flag exists to
480	// make safe. Deploying the console should be easy; destroying it
481	// should be hard.
482	`ALTER TABLE apps ADD COLUMN control_plane INTEGER NOT NULL DEFAULT 0;`,
483	// The path `carlos deploy` polls for the build the app's own PROCESS
484	// reports, instead of X-Carlos-Version — which the edge stamps from
485	// the route row (edge.go:1336) and which therefore lies from the
486	// moment adoption swaps the symlink until something restarts the
487	// process. "" keeps today's header behaviour byte for byte.
488	`ALTER TABLE apps ADD COLUMN build_probe TEXT NOT NULL DEFAULT '';`,
489	// Temporary apps (temporary static sites spec §2). The lifecycle lives
490	// HERE, never in the bucket, so bucket write access cannot make an app
491	// temporary. expires_at 0 = permanent. restoring 1 = between a
492	// restore's "about to delete the marker" and "row is live" (§6), and
493	// counts against the cap. purge_after/purge_for are fixed at trash
494	// time from whether the row was temporary then; nothing re-derives
495	// them.
496	`ALTER TABLE apps ADD COLUMN expires_at INTEGER NOT NULL DEFAULT 0;`,
497	`ALTER TABLE apps ADD COLUMN restoring INTEGER NOT NULL DEFAULT 0;`,
498	`ALTER TABLE apps ADD COLUMN purge_after INTEGER NOT NULL DEFAULT 0;`,
499	`ALTER TABLE apps ADD COLUMN purge_for INTEGER NOT NULL DEFAULT 0;`,
500	// App region (nearest-region spec §5.1): the placement code new
501	// instances default to, in the RECORD vocabulary — "" is the
502	// deployment's home, which is also what every existing row reads, so
503	// the column default IS the backfill (§5.4). region_source is a label
504	// for people and logs: chosen | timezone | ip | default | "" (the row
505	// predates the feature). No timezone, IP or coordinate is stored.
506	`ALTER TABLE apps ADD COLUMN region TEXT NOT NULL DEFAULT '';`,
507	`ALTER TABLE apps ADD COLUMN region_source TEXT NOT NULL DEFAULT '';`,
508	// How a session was proved (email sign-in spec §5.4). Every session
509	// that predates the column was made through Keymail, so the default
510	// is the backfill.
511	`ALTER TABLE sessions ADD COLUMN method TEXT NOT NULL DEFAULT 'keymail';`,
512	} {
513	if _, err := s.db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") {
514	return err
515	}
516	}
517	// The one-shot backfill of mail_sender_counts from mail_counts (senders
518	// design §8). It runs AFTER the ALTER list because mail_counts.deliveries
519	// is added there, and it runs at most once for the life of the database:
520	// a backfill repeated at every start would resurrect rows a purge or a
521	// rename had since removed, since mail_counts is not swept by anything.
522	// PRAGMA user_version is the marker because the console has no other
523	// place to keep one and this is the first thing that has needed it.
524	if err := s.backfillMailSenderCounts(); err != nil {
525	return err
526	}
527	if err := s.migrateAppsKey(); err != nil {
528	return err
529	}
530	// CLI identity's own tables (authstore.go), kept out of the list above so
531	// the feature is one file to read and one file to remove.
532	if err := s.migrateCLIAuth(); err != nil {
533	return err
534	}
535	// Passkey step-up's own tables (stepup.go), for the same reason.
536	if err := s.migrateStepUp(); err != nil {
537	return err
538	}
539	// Email sign-in's challenge table (emailsignin_store.go), for the same reason.
540	if err := s.migrateEmailSignin(); err != nil {
541	return err
542	}
543	// Pending-change quorum's own tables (pending.go), for the same reason.
544	if err := s.migratePendingChanges(); err != nil {
545	return err
546	}
547	// Door keys' own tables (doorkeys_store.go), for the same reason.
548	return s.migrateDoorKeys()
549	}
550
551	// mailCountsBackfillVersion is the user_version this migration stamps. The
552	// next migration that needs a one-shot marker takes 2 and reads this
553	// constant rather than the literal.
554	const mailCountsBackfillVersion = 1
555
556	func (s *Store) backfillMailSenderCounts() error {
557	var version int
558	if err := s.db.QueryRow(`PRAGMA user_version`).Scan(&version); err != nil {
559	return err
560	}
561	if version >= mailCountsBackfillVersion {
562	return nil
563	}
564	if _, err := s.db.Exec(`INSERT OR IGNORE INTO mail_sender_counts
565	(account, app, sender, day, sends, bounces, complaints, rejects, deliveries)
566	SELECT account, app, 'app', day, sends, bounces, complaints, rejects, deliveries FROM mail_counts`); err != nil {
567	return err
568	}
569	// PRAGMA takes no bound parameter, and the value is a constant in this
570	// file rather than anything a caller supplies.
571	_, err := s.db.Exec(fmt.Sprintf(`PRAGMA user_version = %d`, mailCountsBackfillVersion))
572	return err
573	}
574
575	// DropLegacyMailCounts removes the pre-senders counts table. Guarded by a
576	// flag that defaults off and driven by an operator a release after the
577	// backfill, for the same reason mail.SweepLegacyRecords is: the rollback
578	// window has to close before the old shape goes, and dropping it early would
579	// leave a rolled-back console counting into a table that is not there.
580	func (s *Store) DropLegacyMailCounts(enabled bool) error {
581	if !enabled {
582	return nil
583	}
584	_, err := s.db.Exec(`DROP TABLE IF EXISTS mail_counts`)
585	return err
586	}
587
588	// migrateAppsKey moves the apps table from PRIMARY KEY(app) — a GLOBAL name
589	// land-grab — to PRIMARY KEY(account_id, app), which is what "app names are
590	// unique within an account, never globally" actually means. SQLite cannot
591	// ALTER a primary key, so this is the one place in the console that rebuilds
592	// a table rather than appending to the ALTER list.
593	//
594	// It is safe to do here rather than behind an operator verb because every row
595	// is carried across unchanged (account_id already defaulted to 1 = ops), the
596	// table is small, and the whole thing runs in one transaction. Leaving it to
597	// a verb would mean a console that silently refuses a second account's "blog"
598	// until someone remembers to run it.
599	//
600	// Idempotent: it does nothing once the new key is in place.
601	func (s *Store) migrateAppsKey() error {
602	var ddl string
603	err := s.db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='apps'`).Scan(&ddl)
604	if err != nil {
605	return err
606	}
607	if strings.Contains(ddl, "PRIMARY KEY (account_id, app)") {
608	return nil // already rebuilt
609	}
610	tx, err := s.db.Begin()
611	if err != nil {
612	return err
613	}
614	defer tx.Rollback()
615	for _, stmt := range []string{
616	`CREATE TABLE apps_new (
617	app TEXT NOT NULL,
618	account_id INTEGER NOT NULL DEFAULT 1,
619	created_by TEXT NOT NULL,
620	created_at INTEGER NOT NULL,
621	deleted_at INTEGER NOT NULL DEFAULT 0,
622	placement TEXT NOT NULL DEFAULT '',
623	control_plane INTEGER NOT NULL DEFAULT 0,
624	build_probe TEXT NOT NULL DEFAULT '',
625	region TEXT NOT NULL DEFAULT '',
626	region_source TEXT NOT NULL DEFAULT '',
627	expires_at INTEGER NOT NULL DEFAULT 0,
628	restoring INTEGER NOT NULL DEFAULT 0,
629	purge_after INTEGER NOT NULL DEFAULT 0,
630	purge_for INTEGER NOT NULL DEFAULT 0,
631	PRIMARY KEY (account_id, app)
632	);`,
633	// The ALTERs above run before migrateAppsKey in migrate(), so
634	// deleted_at, placement, control_plane, build_probe, region,
635	// region_source and the temporary columns already exist on the old
636	// table by the time this rebuild copies them — a row already
637	// trashed, already placed, already the control plane, already
638	// carrying a build probe or an app region, or already temporary,
639	// when the rebuild runs must not have that state silently dropped
640	// (nearest-region spec §5.2, temporary static sites spec §2).
641	`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)
642	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;`,
643	`DROP TABLE apps;`,
644	`ALTER TABLE apps_new RENAME TO apps;`,
645	} {
646	if _, err := tx.Exec(stmt); err != nil {
647	return err
648	}
649	}
650	return tx.Commit()
651	}
652
653	// ---- owners -----------------------------------------------------------------
654
655	// Grant makes address an owner of app (idempotent).
656	func (s *Store) Grant(address, app string) error {
657	return s.GrantAt(address, app, time.Now())
658	}
659
660	// GrantAt is Grant with an explicit timestamp — the backfill promotes the
661	// EARLIEST grant holder when there is no operator, so that ordering has to be
662	// settable by a test rather than by sleeping.
663	func (s *Store) GrantAt(address, app string, at time.Time) error {
664	if release.IsTemporaryName(app) {
665	return ErrTemporaryNameReserved
666	}
667	_, err := s.db.Exec(`INSERT OR IGNORE INTO owners (address, app, created_at) VALUES (?, ?, ?)`,
668	address, app, at.Unix())
669	return err
670	}
671
672	// Revoke removes address's ownership of app.
673	func (s *Store) Revoke(address, app string) error {
674	_, err := s.db.Exec(`DELETE FROM owners WHERE address = ? AND app = ?`, address, app)
675	return err
676	}
677
678	// AppsFor returns the apps address owns, sorted.
679	func (s *Store) AppsFor(address string) ([]string, error) {
680	rows, err := s.db.Query(`SELECT app FROM owners WHERE address = ? ORDER BY app`, address)
681	if err != nil {
682	return nil, err
683	}
684	defer rows.Close()
685	var out []string
686	for rows.Next() {
687	var a string
688	if err := rows.Scan(&a); err != nil {
689	return nil, err
690	}
691	out = append(out, a)
692	}
693	return out, rows.Err()
694	}
695
696	// Owners returns every grant, keyed by owner address, apps sorted.
697	func (s *Store) Owners() (map[string][]string, error) {
698	rows, err := s.db.Query(`SELECT address, app FROM owners ORDER BY address, app`)
699	if err != nil {
700	return nil, err
701	}
702	defer rows.Close()
703	out := map[string][]string{}
704	for rows.Next() {
705	var addr, app string
706	if err := rows.Scan(&addr, &app); err != nil {
707	return nil, err
708	}
709	out[addr] = append(out[addr], app)
710	}
711	return out, rows.Err()
712	}
713
714	// ---- accounts, memberships, invites -----------------------------------------
715
716	// GrantHolder is one pre-accounts app-grant holder that the backfill
717	// deliberately left OUT of ops, with the apps they held grants for. The
718	// backfill cannot decide where these people belong — that is an account per
719	// person or per project, which is an operator's judgement — so it reports them
720	// instead of guessing.
721	type GrantHolder struct {
722	Address string
723	Apps []string
724	}
725
726	// BackfillOpsMemberships maps the pre-accounts world onto the new one: the
727	// built-in ops account exists, every OPERATOR is an owner of it, and every
728	// granted app name is claimed in it (before accounts, every app was in ops by
729	// definition). It returns the grant holders it left out, so the caller can say
730	// who still needs an account of their own.
731	//
732	// WHY non-operators are excluded — this widened access in production once
733	// already, and the reasoning has to survive the next person to read it:
734	//
735	// Authorization is "Role(account, address) != "" AND HasAppIn(account, app)".
736	// A membership is therefore access to EVERY app in the account, not to the app
737	// somebody once held a grant for. The first version of this function swept all
738	// grant holders into ops as members, on the theory that a member is the
739	// harmless role. It is not harmless in ops: ops holds console, platform-site,
740	// status, and every app that has not yet been migrated out. On the flagship
741	// that handed a user who held ONE grant — for a toy app — the ability to
742	// promote and roll back kass, keymail and the console itself. Nobody granted
743	// them that; the migration invented it, and it took hand-edited SQL to undo.
744	//
745	// A per-app grant was never deployment-wide authority. What IS deployment-wide
746	// authority is the operator flag, so that — and only that — makes an ops
747	// membership here, as owner.
748	//
749	// There is deliberately no "promote somebody so ops has an owner" fallback any
750	// more. The old code promoted the earliest grant holder when no operator
751	// existed, reasoning that an unadministrable account is worse than an
752	// unexpected owner. But that is the same widening by a quieter route, and it
753	// is no longer the only way out: an ownerless ops is recoverable box-side with
754	// `console member -add <addr> bab -role owner` — the tool that did not exist
755	// when that fallback was written.
756	//
757	// Strictly ADDITIVE and idempotent, so it can run on every console start: it
758	// only ever INSERT OR IGNOREs, so no existing membership is ever demoted,
759	// re-roled or removed. That matters concretely — the flagship has already run
760	// this once under the old semantics and been cleaned up by hand, and a re-run
761	// must not resurrect what was removed. It also deliberately does NOT delete the
762	// owners table: the grants stay readable while the migration is verified.
763	func (s *Store) BackfillOpsMemberships(now time.Time) ([]GrantHolder, error) {
764	tx, err := s.db.Begin()
765	if err != nil {
766	return nil, err
767	}
768	defer tx.Rollback()
769	if _, err := tx.Exec(`INSERT OR IGNORE INTO accounts (id, sqid, name, created_at)
770	VALUES (?, ?, 'ops', ?)`, OpsAccountID, opsSqid, now.Unix()); err != nil {
771	return nil, err
772	}
773	// OR IGNORE, never OR REPLACE or UPDATE: an address that is already a
774	// member keeps whatever role it has. This is the only membership write in
775	// the whole function.
776	if _, err := tx.Exec(`INSERT OR IGNORE INTO memberships (account_id, address, role, added_at)
777	SELECT ?, address, ?, ? FROM users WHERE operator != 0`,
778	OpsAccountID, RoleOwner, now.Unix()); err != nil {
779	return nil, err
780	}
781	// The app NAMES are still claimed in ops: that is where every pre-accounts
782	// app physically lives, and claiming them keeps two accounts from minting
783	// the same name before the migration moves them. `console migrate-app` is
784	// what re-homes them afterwards.
785	//
786	// The NOT IN is not a tidy-up — it is the fix for a bug that was live on the
787	// flagship, and it was found by running the migration in production, not by
788	// reading this code. `carlos accounts migrate` + `console migrate-app` moved
789	// jelly out of ops into account bac. The migration deliberately leaves the
790	// legacy grant readable (see the note above about not deleting the owners
791	// table). The next console RESTART ran this statement, found a grant for
792	// "jelly", found no jelly row in OPS — because it had just been moved out —
793	// and inserted one. The same app then existed in two accounts at once, and
794	// kass and keymail were lined up for the same on the following restart. The
795	// migration was being undone, indefinitely, by restarting the console; it
796	// took a hand-deleted row and hand-revoked grants to stop it.
797	//
798	// ON CONFLICT could never have caught this: the primary key is
799	// (account_id, app), so an ops row and a bac row for the same app do not
800	// conflict — they are, by the schema's own rules, two different apps. The
801	// claim has to be conditioned on the app name being unclaimed EVERYWHERE,
802	// which is what the backfill actually means: take the names nothing has
803	// placed yet. An app that already has a row somewhere has been placed, and
804	// the row that placed it is the authority — never this sweep.
805	if _, err := tx.Exec(`INSERT OR IGNORE INTO apps (app, account_id, created_by, created_at)
806	SELECT o.app, ?, MIN(o.address), ? FROM owners o
807	WHERE o.app NOT IN (SELECT app FROM apps) AND o.app NOT LIKE 'tmp-%'
808	GROUP BY o.app`, OpsAccountID, now.Unix()); err != nil {
809	return nil, err
810	}
811	// A tmp- grant never becomes a row: the namespace is reserved for
812	// temporary apps, and only /new-temporary may create one (spec §3,
813	// astra 7.2). Said at every start, because only an operator can revoke it.
814	skipped, err := tx.Query(`SELECT DISTINCT app FROM owners WHERE app LIKE 'tmp-%' AND app NOT IN (SELECT app FROM apps) ORDER BY app`)
815	if err != nil {
816	return nil, err
817	}
818	for skipped.Next() {
819	var app string
820	if err := skipped.Scan(&app); err != nil {
821	skipped.Close()
822	return nil, err
823	}
824	log.Printf("console: not claiming %s for ops: names starting with tmp- belong to temporary apps. Revoke the grant with: console revoke <address> %s", app, app)
825	}
826	skipped.Close()
827	if err := skipped.Err(); err != nil {
828	return nil, err
829	}
830	// Who is left over: grant holders who are not operators and hold no ops
831	// membership. The membership exclusion is what makes the report quiet once
832	// an operator has dealt with someone — deliberately admitting them, or
833	// giving them their own account — rather than nagging on every restart.
834	rows, err := tx.Query(`SELECT o.address, o.app FROM owners o
835	WHERE o.address NOT IN (SELECT address FROM users WHERE operator != 0)
836	AND o.address NOT IN (SELECT address FROM memberships WHERE account_id = ?)
837	ORDER BY o.address, o.app`, OpsAccountID)
838	if err != nil {
839	return nil, err
840	}
841	var out []GrantHolder
842	for rows.Next() {
843	var addr, app string
844	if err := rows.Scan(&addr, &app); err != nil {
845	rows.Close()
846	return nil, err
847	}
848	if n := len(out); n > 0 && out[n-1].Address == addr {
849	out[n-1].Apps = append(out[n-1].Apps, app)
850	continue
851	}
852	out = append(out, GrantHolder{Address: addr, Apps: []string{app}})
853	}
854	rows.Close()
855	if err := rows.Err(); err != nil {
856	return nil, err
857	}
858	if err := tx.Commit(); err != nil {
859	return nil, err
860	}
861	return out, nil
862	}
863
864	// grantHolderAdvice renders the leftover grant holders as something an
865	// operator can act on: who, which apps, and the exact three commands that give
866	// one of them an account. A count and a list would be a to-do; this is a
867	// runbook, and the difference decides whether it gets done.
868	//
869	// Returns "" when there is nobody, so the caller logs nothing rather than a
870	// line saying nothing happened.
871	func grantHolderAdvice(holders []GrantHolder) string {
872	if len(holders) == 0 {
873	return ""
874	}
875	var b strings.Builder
876	fmt.Fprintf(&b, "console: %d grant holders need accounts of their own — they hold pre-accounts "+
877	"app grants and were deliberately NOT made members of ops (a membership there is access to "+
878	"console, platform-site and every unmigrated app): ", len(holders))
879	for i, h := range holders {
880	if i > 0 {
881	b.WriteString("; ")
882	}
883	fmt.Fprintf(&b, "%s (%s)", h.Address, strings.Join(h.Apps, " "))
884	}
885	// Two commands, not three: `accounts create -owner` now records the owner in
886	// the account manifest and the console materializes it into an owner
887	// membership, so the `console member -add … -role owner` step this advice
888	// used to carry is gone. It was never optional — an operator who skipped it
889	// got an account nobody could open, which is exactly what happened on the
890	// flagship — and the surest way to stop a step being skipped is to not need
891	// it. The -owner flag is REQUIRED, so it has to appear here or the copied
892	// command fails.
893	b.WriteString(". Give each one their own with: carlos accounts create --name <name> --owner <address> " +
894	"&& console migrate-app --app <app> --to <sqid>")
895	return b.String()
896	}
897
898	// Roles. Two, not a matrix: enough authority to make invites safe, without
899	// inventing a permissions model before there is a second user to test it
900	// against.
901	const (
902	RoleOwner = "owner"
903	RoleMember = "member"
904	)
905
906	// InviteTTL bounds a pending invite.
907	const InviteTTL = 7 * 24 * time.Hour
908
909	// opsSqid is the ops account's frozen public identity. Spelled out rather
910	// than imported from release so the store layer stays dependency-free; the
911	// two are pinned equal by TestMintedSqidIsTheFrozenEncoding.
912	const opsSqid = "bab"
913
914	// Account is one tenancy root as the console sees it. Role is filled only by
915	// AccountsFor (it is the viewer's role, not a property of the account).
916	type Account struct {
917	ID int64
918	Sqid string
919	Name string
920	Role string
921	CreatedAt time.Time
922
923	// Seg is the path segment the console generates for this account in the
924	// viewer's chrome (preferredSeg) — display state, never stored.
925	Seg string
926	}
927
928	// Invite is one pending invitation.
929	type Invite struct {
930	Token string
931	AccountID int64
932	Address string
933	InvitedBy string
934	CreatedAt time.Time
935	ExpiresAt time.Time
936	}
937
938	// CreateAccount records a minted account and makes its creator the first
939	// owner, in ONE transaction: an account with no owner is unadministrable —
940	// nobody left who can invite, rename, or delete it — so the two writes must
941	// never be separable by a crash.
942	func (s *Store) CreateAccount(id int64, sqid, name, ownerAddress string, now time.Time) error {
943	tx, err := s.db.Begin()
944	if err != nil {
945	return err
946	}
947	defer tx.Rollback()
948	if _, err := tx.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (?, ?, ?, ?)`,
949	id, sqid, name, now.Unix()); err != nil {
950	return err
951	}
952	if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at) VALUES (?, ?, ?, ?)`,
953	id, ownerAddress, RoleOwner, now.Unix()); err != nil {
954	return err
955	}
956	return tx.Commit()
957	}
958
959	// EnsureAccount records an account the console did NOT mint — one that already
960	// exists in the bucket (MaterializeAccounts). added reports whether this call
961	// created the row.
962	//
963	// Unlike CreateAccount it deliberately creates no membership, and that is not
964	// an oversight: this call knows only what a manifest's identity fields say.
965	// Inventing an owner — the invoking operator, the first user, anyone — would be
966	// minting authority out of thin air over an account this box knows nothing
967	// about. An ownerless row is honest and harmless (no membership means no
968	// access).
969	//
970	// Where the owner DOES come from is the manifest's own `owner` field, applied
971	// separately by AdoptManifestOwner, so identity and authority stay two distinct
972	// decisions: a manifest with a broken owner still materializes an account, and
973	// an old manifest with no owner at all still materializes exactly as it did
974	// before the field existed.
975	//
976	// INSERT OR IGNORE, never REPLACE: an account the console already knows must
977	// keep its id, its name and its members.
978	func (s *Store) EnsureAccount(id int64, sqid, name string, created time.Time) (added bool, err error) {
979	res, err := s.db.Exec(`INSERT OR IGNORE INTO accounts (id, sqid, name, created_at) VALUES (?, ?, ?, ?)`,
980	id, sqid, name, created.Unix())
981	if err != nil {
982	return false, err
983	}
984	n, err := res.RowsAffected()
985	return n > 0, err
986	}
987
988	// AdoptManifestOwner turns an account manifest's `owner` field into the owner
989	// membership — the console half of the fix for a PRODUCTION failure, found by
990	// running the migration on the flagship and not by reading this code.
991	//
992	// What happened: `carlos accounts create` recorded an owner nowhere at all. The
993	// bucket manifest and the registry row both landed, and the account had no
994	// members. Membership IS authorization here, so after kass and keymail were
995	// migrated into fresh accounts the operator could not open either of them in
996	// the console — the account existed, held the apps, and had no door. The only
997	// way in was a `console member -add … -role owner` that nothing told anybody to
998	// run.
999	//
1000	// The carlos binary still does not open this database — that boundary is
1001	// deliberate and was enforced in an earlier review. Instead the manifest, which
1002	// is already the one place account identity crosses between the CLI, the
1003	// console and every box, now carries the intended owner, and this is where the
1004	// console reads it.
1005	//
1006	// It fills a VACANCY and nothing else. The manifest is re-read on every console
1007	// start, so treating it as an instruction would make it a standing grant: an
1008	// address removed by an operator would come back on the next restart, which is
1009	// precisely the shape of the app-backfill bug this same production run turned
1010	// up. So: an account that already has ANY owner is left completely alone, and
1011	// only an ownerless one is filled. Returns whether it wrote anything.
1012	//
1013	// This is the SECOND of two independent guards, and deliberately not the load
1014	// bearing one. The manifest is not write-once — it can be rewritten by anyone
1015	// with bucket write access — so the caller only ever invokes this on the pass
1016	// that first creates the account row (see materializeOwner). That structural
1017	// rule is what stops a rewritten manifest granting authority; this vacancy
1018	// check is what stops a race between the row insert and this write, and what
1019	// keeps the function safe to reason about on its own.
1020	//
1021	// The named address may already be a plain member (an accepted invite always
1022	// grants member) — then they are promoted, rather than the account gaining a
1023	// second row for the same person.
1024	func (s *Store) AdoptManifestOwner(accountID int64, address string, now time.Time) (bool, error) {
1025	tx, err := s.db.Begin()
1026	if err != nil {
1027	return false, err
1028	}
1029	defer tx.Rollback()
1030	// Re-counted INSIDE the transaction rather than trusting HasOwner: that one
1031	// is an optimization, this one is the rule, and a concurrent `console member
1032	// -add … -role owner` must not be able to slip between the two.
1033	var owners int
1034	if err := tx.QueryRow(`SELECT COUNT(*) FROM memberships WHERE account_id = ? AND role = ?`,
1035	accountID, RoleOwner).Scan(&owners); err != nil {
1036	return false, err
1037	}
1038	if owners > 0 {
1039	return false, nil
1040	}
1041	if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at)
1042	VALUES (?, ?, ?, ?)
1043	ON CONFLICT(account_id, address) DO UPDATE SET role = excluded.role`,
1044	accountID, address, RoleOwner, now.Unix()); err != nil {
1045	return false, err
1046	}
1047	if err := tx.Commit(); err != nil {
1048	return false, err
1049	}
1050	return true, nil
1051	}
1052
1053	// AccountBySqid returns the account with that public identity, or (nil, nil)
1054	// when there is none — "no such account" is an answer, not an error, and the
1055	// caller renders it as the same 404 a nonexistent page gets.
1056	//
1057	// A marked (deleted) row is filtered out here: a tombstoned account must be
1058	// invisible everywhere a console door looks one up, exactly like a row that
1059	// was never inserted. AccountAnyBySqid is the one exception, and it exists
1060	// for a different question — "has this sqid ever been seen" — not this one.
1061	func (s *Store) AccountBySqid(sqid string) (*Account, error) {
1062	var a Account
1063	var created int64
1064	err := s.db.QueryRow(`SELECT id, sqid, name, created_at FROM accounts WHERE sqid = ? AND deleted_at = 0`, sqid).
1065	Scan(&a.ID, &a.Sqid, &a.Name, &created)
1066	if err == sql.ErrNoRows {
1067	return nil, nil
1068	}
1069	if err != nil {
1070	return nil, err
1071	}
1072	a.CreatedAt = time.Unix(created, 0).UTC()
1073	return &a, nil
1074	}
1075
1076	// AccountByID is AccountBySqid keyed the other way, for the MRU landing.
1077	func (s *Store) AccountByID(id int64) (*Account, error) {
1078	var a Account
1079	var created int64
1080	err := s.db.QueryRow(`SELECT id, sqid, name, created_at FROM accounts WHERE id = ? AND deleted_at = 0`, id).
1081	Scan(&a.ID, &a.Sqid, &a.Name, &created)
1082	if err == sql.ErrNoRows {
1083	return nil, nil
1084	}
1085	if err != nil {
1086	return nil, err
1087	}
1088	a.CreatedAt = time.Unix(created, 0).UTC()
1089	return &a, nil
1090	}
1091
1092	// AccountAnyBySqid is AccountBySqid WITHOUT the deleted_at filter — it sees a
1093	// marked row instead of pretending it does not exist. deleted reports which
1094	// case this was, so the account struct itself needs no new field for it.
1095	//
1096	// The callers are materialization and the /domains doors' namespace check
1097	// (otherAccountsNamespace, which reserves a deleted account's namespace too).
1098	// Materialization is why it exists: the whole point of keeping a tombstoned
1099	// row (see DeleteAccount) is that the pass which decides whether to re-birth
1100	// an account from its bucket manifest must be able to tell "never seen" from
1101	// "seen, and deleted". Every other door in the console asks the filtered
1102	// question instead.
1103	func (s *Store) AccountAnyBySqid(sqid string) (*Account, bool, error) {
1104	var a Account
1105	var created, deletedAt int64
1106	err := s.db.QueryRow(`SELECT id, sqid, name, created_at, deleted_at FROM accounts WHERE sqid = ?`, sqid).
1107	Scan(&a.ID, &a.Sqid, &a.Name, &created, &deletedAt)
1108	if err == sql.ErrNoRows {
1109	return nil, false, nil
1110	}
1111	if err != nil {
1112	return nil, false, err
1113	}
1114	a.CreatedAt = time.Unix(created, 0).UTC()
1115	return &a, deletedAt != 0, nil
1116	}
1117
1118	// AccountsFor returns the accounts address is a member of, in creation order
1119	// — the switcher's list, and the "your first membership" fallback when there
1120	// is no MRU history. Ordered by created_at then id so two accounts created in
1121	// the same second still order deterministically.
1122	func (s *Store) AccountsFor(address string) ([]Account, error) {
1123	rows, err := s.db.Query(`SELECT a.id, a.sqid, a.name, a.created_at, m.role
1124	FROM accounts a JOIN memberships m ON m.account_id = a.id
1125	WHERE m.address = ? AND a.deleted_at = 0 ORDER BY a.created_at, a.id`, address)
1126	if err != nil {
1127	return nil, err
1128	}
1129	defer rows.Close()
1130	var out []Account
1131	for rows.Next() {
1132	var a Account
1133	var created int64
1134	if err := rows.Scan(&a.ID, &a.Sqid, &a.Name, &created, &a.Role); err != nil {
1135	return nil, err
1136	}
1137	a.CreatedAt = time.Unix(created, 0).UTC()
1138	out = append(out, a)
1139	}
1140	return out, rows.Err()
1141	}
1142
1143	// Accounts lists every live account — the economics pass's iteration set (an
1144	// account with no statements yet must still appear in the census). Filtered
1145	// by deleted_at like every other account reader (AccountBySqid, AccountsFor):
1146	// a retired account stops accruing usage the same tick it stops billing.
1147	func (s *Store) Accounts() ([]Account, error) {
1148	rows, err := s.db.Query(`SELECT id, sqid, name, created_at FROM accounts
1149	WHERE deleted_at = 0 ORDER BY name`)
1150	if err != nil {
1151	return nil, err
1152	}
1153	defer rows.Close()
1154	var out []Account
1155	for rows.Next() {
1156	var a Account
1157	var created int64
1158	if err := rows.Scan(&a.ID, &a.Sqid, &a.Name, &created); err != nil {
1159	return nil, err
1160	}
1161	a.CreatedAt = time.Unix(created, 0).UTC()
1162	out = append(out, a)
1163	}
1164	return out, rows.Err()
1165	}
1166
1167	// validAccountTiers is the fixed set SetAccountTier accepts — "" (never
1168	// assigned) plus economics-v2's fleet-pool tiers. Kept as a package-level
1169	// set rather than inlined so a future consumer (Task 5's placement pass) can
1170	// range over the same set without duplicating it.
1171	var validAccountTiers = map[string]bool{
1172	"": true,
1173	"try": true,
1174	"pool": true,
1175	"hosted": true,
1176	"fleet": true,
1177	"dedicated": true,
1178	}
1179
1180	// SetAccountTier records accountID's fleet-pool tier (economics-v2, Task 4)
1181	// — Task 5's placement pass reads it back via AccountTiers. Rejects any
1182	// value outside validAccountTiers with a clear error rather than writing an
1183	// unrecognized string a downstream placement pass would silently ignore. An
1184	// accountID matching no row is a silent no-op (the UPDATE simply touches
1185	// zero rows) — this store's existing convention for a keyed UPDATE with no
1186	// distinguishable "not found" case (see ReapAccount's deleted_at-guarded
1187	// UPDATE).
1188	func (s *Store) SetAccountTier(accountID int64, tier string) error {
1189	if !validAccountTiers[tier] {
1190	return fmt.Errorf("console: invalid account tier %q", tier)
1191	}
1192	_, err := s.db.Exec(`UPDATE accounts SET tier = ? WHERE id = ?`, tier, accountID)
1193	return err
1194	}
1195
1196	// AccountTiers returns every live account's tier keyed by sqid — Task 5's
1197	// placement pass input. Filtered by deleted_at like Accounts(): a retired
1198	// account carries no tier into fleet-pool placement, same posture as it
1199	// carrying no usage into the economics pass.
1200	func (s *Store) AccountTiers() (map[string]string, error) {
1201	rows, err := s.db.Query(`SELECT sqid, tier FROM accounts WHERE deleted_at = 0`)
1202	if err != nil {
1203	return nil, err
1204	}
1205	defer rows.Close()
1206	out := map[string]string{}
1207	for rows.Next() {
1208	var sqid, tier string
1209	if err := rows.Scan(&sqid, &tier); err != nil {
1210	return nil, err
1211	}
1212	out[sqid] = tier
1213	}
1214	return out, rows.Err()
1215	}
1216
1217	// Role is address's role in the account, "" when they are not a member. It is
1218	// the one permission check every account-scoped route makes.
1219	func (s *Store) Role(accountID int64, address string) (string, error) {
1220	var role string
1221	err := s.db.QueryRow(`SELECT role FROM memberships WHERE account_id = ? AND address = ?`,
1222	accountID, address).Scan(&role)
1223	if err == sql.ErrNoRows {
1224	return "", nil
1225	}
1226	if err != nil {
1227	return "", err
1228	}
1229	return role, nil
1230	}
1231
1232	// Members lists an account's memberships, owners first then by address.
1233	func (s *Store) Members(accountID int64) ([]Account, error) {
1234	rows, err := s.db.Query(`SELECT address, role FROM memberships WHERE account_id = ?
1235	ORDER BY CASE role WHEN 'owner' THEN 0 ELSE 1 END, address`, accountID)
1236	if err != nil {
1237	return nil, err
1238	}
1239	defer rows.Close()
1240	var out []Account
1241	for rows.Next() {
1242	var a Account
1243	if err := rows.Scan(&a.Name, &a.Role); err != nil { // Name carries the address here
1244	return nil, err
1245	}
1246	a.ID = accountID
1247	out = append(out, a)
1248	}
1249	return out, rows.Err()
1250	}
1251
1252	// AddMember adds or re-roles a member. Demoting the LAST owner is refused for
1253	// the same reason removing them is: it leaves an account nobody can
1254	// administer. Transfer ownership first.
1255	func (s *Store) AddMember(accountID int64, address, role string, now time.Time) error {
1256	if role != RoleOwner && role != RoleMember {
1257	return fmt.Errorf("console: invalid role %q", role)
1258	}
1259	tx, err := s.db.Begin()
1260	if err != nil {
1261	return err
1262	}
1263	defer tx.Rollback()
1264	if role != RoleOwner {
1265	if err := lastOwnerGuard(tx, accountID, address); err != nil {
1266	return err
1267	}
1268	}
1269	if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at)
1270	VALUES (?, ?, ?, ?)
1271	ON CONFLICT(account_id, address) DO UPDATE SET role = excluded.role`,
1272	accountID, address, role, now.Unix()); err != nil {
1273	return err
1274	}
1275	return tx.Commit()
1276	}
1277
1278	// RemoveMember drops a membership, refusing to remove the last owner.
1279	func (s *Store) RemoveMember(accountID int64, address string) error {
1280	tx, err := s.db.Begin()
1281	if err != nil {
1282	return err
1283	}
1284	defer tx.Rollback()
1285	if err := lastOwnerGuard(tx, accountID, address); err != nil {
1286	return err
1287	}
1288	if _, err := tx.Exec(`DELETE FROM memberships WHERE account_id = ? AND address = ?`,
1289	accountID, address); err != nil {
1290	return err
1291	}
1292	return tx.Commit()
1293	}
1294
1295	// lastOwnerGuard refuses a change that would leave an account with no owner.
1296	// Inside the caller's transaction, so the count and the write cannot be
1297	// separated by a concurrent second removal.
1298	func lastOwnerGuard(tx *sql.Tx, accountID int64, address string) error {
1299	var role string
1300	err := tx.QueryRow(`SELECT role FROM memberships WHERE account_id = ? AND address = ?`,
1301	accountID, address).Scan(&role)
1302	if err == sql.ErrNoRows {
1303	return nil // not a member: nothing to protect
1304	}
1305	if err != nil {
1306	return err
1307	}
1308	if role != RoleOwner {
1309	return nil
1310	}
1311	var owners int
1312	if err := tx.QueryRow(`SELECT COUNT(*) FROM memberships WHERE account_id = ? AND role = ?`,
1313	accountID, RoleOwner).Scan(&owners); err != nil {
1314	return err
1315	}
1316	if owners <= 1 {
1317	return fmt.Errorf("console: %s is the last owner of this account — transfer ownership first", address)
1318	}
1319	return nil
1320	}
1321
1322	// SetLastAccount records where a user was last, so signing in lands them
1323	// back there. Upserts the users row: an invited address that has never
1324	// signed in still gets a landing spot.
1325	func (s *Store) SetLastAccount(address string, accountID int64) error {
1326	_, err := s.db.Exec(`INSERT INTO users (address, created_at, last_account) VALUES (?, ?, ?)
1327	ON CONFLICT(address) DO UPDATE SET last_account = excluded.last_account`,
1328	address, time.Now().Unix(), accountID)
1329	return err
1330	}
1331
1332	// LastAccount is the account id a user was last in, 0 when there is no
1333	// history (a fresh user, or one whose only account was deleted).
1334	func (s *Store) LastAccount(address string) (int64, error) {
1335	var id int64
1336	err := s.db.QueryRow(`SELECT last_account FROM users WHERE address = ?`, address).Scan(&id)
1337	if err == sql.ErrNoRows {
1338	return 0, nil
1339	}
1340	if err != nil {
1341	return 0, err
1342	}
1343	return id, nil
1344	}
1345
1346	// CreateInvite mints a single-use, 7-day invitation for address, returning
1347	// the token the sealed Keymail link carries.
1348	func (s *Store) CreateInvite(accountID int64, address, invitedBy string, now time.Time) (string, error) {
1349	tok, err := randomToken()
1350	if err != nil {
1351	return "", err
1352	}
1353	_, err = s.db.Exec(`INSERT INTO invites (token, account_id, address, invited_by, created_at, expires_at)
1354	VALUES (?, ?, ?, ?, ?, ?)`,
1355	tok, accountID, address, invitedBy, now.Unix(), now.Add(InviteTTL).Unix())
1356	if err != nil {
1357	return "", err
1358	}
1359	return tok, nil
1360	}
1361
1362	// PendingInviteFor reads an invite without consuming it, behind the same
1363	// three gates AcceptInvite applies: unaccepted, addressed to address, and
1364	// unexpired. Any failure is one error, so the confirm page that shows it can
1365	// answer every refusal the same way.
1366	func (s *Store) PendingInviteFor(token, address string, now time.Time) (*Invite, error) {
1367	var i Invite
1368	var created, expires, accepted int64
1369	err := s.db.QueryRow(`SELECT token, account_id, address, invited_by, created_at, expires_at, accepted_at
1370	FROM invites WHERE token = ?`, token).Scan(&i.Token, &i.AccountID, &i.Address, &i.InvitedBy, &created, &expires, &accepted)
1371	if err != nil {
1372	return nil, err
1373	}
1374	if accepted != 0 || i.Address != address || now.Unix() > expires {
1375	return nil, fmt.Errorf("console: that invite cannot be used by %s", address)
1376	}
1377	i.CreatedAt = time.Unix(created, 0).UTC()
1378	i.ExpiresAt = time.Unix(expires, 0).UTC()
1379	return &i, nil
1380	}
1381
1382	// PendingInviteAddress is the address an invite was sent to, read without
1383	// consuming it, and only while the invite is unaccepted and unexpired: the
1384	// signed-out invite page fills it into the sign-in form.
1385	func (s *Store) PendingInviteAddress(token string, now time.Time) (string, bool) {
1386	var addr string
1387	var expires, accepted int64
1388	err := s.db.QueryRow(`SELECT address, expires_at, accepted_at FROM invites WHERE token = ?`,
1389	token).Scan(&addr, &expires, &accepted)
1390	if err != nil || accepted != 0 || now.Unix() > expires {
1391	return "", false
1392	}
1393	return addr, true
1394	}
1395
1396	// AcceptInvite consumes an invite and creates the membership, in one
1397	// transaction. Three gates, all inside it: the token must be unaccepted, the
1398	// address must be the one it was addressed to (an invite is not a bearer
1399	// token — the link lands in ONE keymail inbox and only that identity can use
1400	// it), and it must not have expired.
1401	func (s *Store) AcceptInvite(token, address string, now time.Time) (int64, error) {
1402	tx, err := s.db.Begin()
1403	if err != nil {
1404	return 0, err
1405	}
1406	defer tx.Rollback()
1407	var accountID, expires, accepted int64
1408	var want string
1409	err = tx.QueryRow(`SELECT account_id, address, expires_at, accepted_at FROM invites WHERE token = ?`,
1410	token).Scan(&accountID, &want, &expires, &accepted)
1411	if err == sql.ErrNoRows {
1412	return 0, fmt.Errorf("console: no such invite")
1413	}
1414	if err != nil {
1415	return 0, err
1416	}
1417	if accepted != 0 {
1418	return 0, fmt.Errorf("console: that invite has already been used")
1419	}
1420	if want != address {
1421	return 0, fmt.Errorf("console: that invite was sent to a different address")
1422	}
1423	if now.Unix() > expires {
1424	return 0, fmt.Errorf("console: that invite has expired")
1425	}
1426	if _, err := tx.Exec(`UPDATE invites SET accepted_at = ? WHERE token = ?`, now.Unix(), token); err != nil {
1427	return 0, err
1428	}
1429	// An invite always grants member; owners are promoted deliberately.
1430	if _, err := tx.Exec(`INSERT INTO memberships (account_id, address, role, added_at)
1431	VALUES (?, ?, ?, ?) ON CONFLICT(account_id, address) DO NOTHING`,
1432	accountID, address, RoleMember, now.Unix()); err != nil {
1433	return 0, err
1434	}
1435	if err := tx.Commit(); err != nil {
1436	return 0, err
1437	}
1438	return accountID, nil
1439	}
1440
1441	// RevokeInvite deletes a pending invite. Deleting rather than flagging is
1442	// deliberate: a revoked invite must be indistinguishable from one that never
1443	// existed, so a leaked link reveals nothing about the account.
1444	func (s *Store) RevokeInvite(token string) error {
1445	_, err := s.db.Exec(`DELETE FROM invites WHERE token = ?`, token)
1446	return err
1447	}
1448
1449	// CountPendingInvites counts an account's unaccepted invitations that have
1450	// not expired at now: the ones handleInvite's cap is about.
1451	func (s *Store) CountPendingInvites(accountID int64, now time.Time) (int, error) {
1452	var n int
1453	err := s.db.QueryRow(`SELECT COUNT(*) FROM invites WHERE account_id = ? AND accepted_at = 0 AND expires_at >= ?`,
1454	accountID, now.Unix()).Scan(&n)
1455	return n, err
1456	}
1457
1458	// PendingInvites lists an account's unaccepted, unexpired invitations.
1459	func (s *Store) PendingInvites(accountID int64) ([]Invite, error) {
1460	rows, err := s.db.Query(`SELECT token, account_id, address, invited_by, created_at, expires_at
1461	FROM invites WHERE account_id = ? AND accepted_at = 0 ORDER BY created_at, address`, accountID)
1462	if err != nil {
1463	return nil, err
1464	}
1465	defer rows.Close()
1466	var out []Invite
1467	for rows.Next() {
1468	var i Invite
1469	var created, expires int64
1470	if err := rows.Scan(&i.Token, &i.AccountID, &i.Address, &i.InvitedBy, &created, &expires); err != nil {
1471	return nil, err
1472	}
1473	i.CreatedAt = time.Unix(created, 0).UTC()
1474	i.ExpiresAt = time.Unix(expires, 0).UTC()
1475	out = append(out, i)
1476	}
1477	return out, rows.Err()
1478	}
1479
1480	// ReapAccount is materialization's verb for a tombstoned bucket manifest
1481	// found on a LATER pass than the one that first materialized the account
1482	// (see MaterializeAccounts §8 of the deletion-policy spec): memberships,
1483	// invites and apps rows are deleted outright — this account's authority and
1484	// its name-reservations are gone — but the accounts ROW itself is marked,
1485	// never removed, for the identical reason DeleteAccount marks it: deleting
1486	// the row forgets the sqid was ever seen, and a console that forgets is one
1487	// where a rewritten manifest un-tombstoning itself would re-birth the
1488	// account, same id, same sqid, with whoever wrote the manifest as owner. A
1489	// marked row closes that: AccountAnyBySqid always sees it, and nothing ever
1490	// re-materializes over it.
1491	//
1492	// An account tombstoned while still holding app rows was tombstoned by
1493	// something other than the console's own guarded path (DeleteAccount refuses
1494	// while apps remain) — so those rows are reaped with it rather than left
1495	// stranded pointing at a dead account.
1496	//
1497	// One transaction: a crash partway through must never leave memberships live
1498	// against an account whose row is already marked, which would be access with
1499	// nothing left to be a member OF.
1500	func (s *Store) ReapAccount(accountID int64, now time.Time) error {
1501	if accountID == OpsAccountID {
1502	return fmt.Errorf("console: the ops account cannot be reaped")
1503	}
1504	tx, err := s.db.Begin()
1505	if err != nil {
1506	return err
1507	}
1508	defer tx.Rollback()
1509	for _, stmt := range []string{
1510	`DELETE FROM memberships WHERE account_id = ?`,
1511	`DELETE FROM invites WHERE account_id = ?`,
1512	`DELETE FROM apps WHERE account_id = ?`,
1513	} {
1514	if _, err := tx.Exec(stmt, accountID); err != nil {
1515	return err
1516	}
1517	}
1518	// Email history and settings are keyed by sqid (mailevents.go).
1519	var sqid string
1520	switch err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); {
1521	case err == nil:
1522	if err := forgetMailAccount(context.Background(), tx, sqid); err != nil {
1523	return err
1524	}
1525	case err != sql.ErrNoRows:
1526	return err
1527	}
1528	if _, err := tx.Exec(`UPDATE users SET last_account = 0 WHERE last_account = ?`, accountID); err != nil {
1529	return err
1530	}
1531	if _, err := tx.Exec(`UPDATE accounts SET deleted_at = ? WHERE id = ? AND deleted_at = 0`,
1532	now.Unix(), accountID); err != nil {
1533	return err
1534	}
1535	return tx.Commit()
1536	}
1537
1538	// ---- apps (claim-once names) ------------------------------------------------
1539
1540	// ErrTemporaryNameReserved refuses a permanent name in the tmp- namespace
1541	// (temporary static sites spec ruling 7). `console grant` prints it to an
1542	// operator, so it is written as a sentence.
1543	var ErrTemporaryNameReserved = errors.New("names starting with tmp- belong to temporary apps. A grant, a rename or a permanent claim cannot create one")
1544
1545	// OpsAccountID is the built-in operator account's id, pinned by registry
1546	// migration on every deployment (its sqid is release.OpsAccount).
1547	const OpsAccountID = 1
1548
1549	// CreateApp claims an app name in the built-in ops account — the shape the
1550	// account-less /apps routes still use. CreateAppIn is the account-aware verb.
1551	func (s *Store) CreateApp(app, createdBy string, now time.Time) (created bool, err error) {
1552	return s.CreateAppIn(OpsAccountID, app, createdBy, now)
1553	}
1554
1555	// CreateAppIn claims an app name WITHIN an account, once. created is false
1556	// when that account already has the name — another account holding it is not
1557	// a conflict, because app names are unique per account, never globally.
1558	func (s *Store) CreateAppIn(accountID int64, app, createdBy string, now time.Time) (created bool, err error) {
1559	return s.CreateAppInRegion(accountID, app, createdBy, "", "", now)
1560	}
1561
1562	// CreateAppInRegion claims an app name with its region in the SAME insert:
1563	// an app region has no repair verb, so a separate best-effort write that
1564	// failed would leave the app at the home for good, silently (spec §5.3).
1565	func (s *Store) CreateAppInRegion(accountID int64, app, createdBy, region, source string, now time.Time) (created bool, err error) {
1566	res, err := s.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at, region, region_source)
1567	VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(account_id, app) DO NOTHING`,
1568	app, accountID, createdBy, now.Unix(), region, source)
1569	if err != nil {
1570	return false, err
1571	}
1572	n, err := res.RowsAffected()
1573	return n > 0, err
1574	}
1575
1576	// AppRegion reads an app's region and its source label. A missing row
1577	// reads as the home with no source.
1578	func (s *Store) AppRegion(accountID int64, app string) (region, source string, err error) {
1579	err = s.db.QueryRow(`SELECT region, region_source FROM apps WHERE account_id = ? AND app = ?`, accountID, app).
1580	Scan(&region, &source)
1581	if errors.Is(err, sql.ErrNoRows) {
1582	return "", "", nil
1583	}
1584	return region, source, err
1585	}
1586
1587	// nonHomeAppRegion is one live app holding a non-home region, for the
1588	// roll-forward discrepancy log (spec §5.7).
1589	type nonHomeAppRegion struct {
1590	Sqid, App, Region string
1591	CreatedAt time.Time
1592	}
1593
1594	func (s *Store) NonHomeAppRegions() ([]nonHomeAppRegion, error) {
1595	rows, err := s.db.Query(`SELECT a.sqid, p.app, p.region, p.created_at FROM apps p
1596	JOIN accounts a ON a.id = p.account_id
1597	WHERE p.region != '' AND p.deleted_at = 0 ORDER BY a.sqid, p.app`)
1598	if err != nil {
1599	return nil, err
1600	}
1601	defer rows.Close()
1602	var out []nonHomeAppRegion
1603	for rows.Next() {
1604	var r nonHomeAppRegion
1605	var created int64
1606	if err := rows.Scan(&r.Sqid, &r.App, &r.Region, &created); err != nil {
1607	return nil, err
1608	}
1609	r.CreatedAt = time.Unix(created, 0)
1610	out = append(out, r)
1611	}
1612	return out, rows.Err()
1613	}
1614
1615	// AppsIn lists an account's claimed app names, sorted. Trashed apps are
1616	// filtered out — every door this feeds (API resolution, ship claim, account
1617	// page listings) is a door a trashed app must vanish from automatically,
1618	// without each caller having to remember to ask. TrashedAppsIn is the
1619	// dedicated listing for the Trash page itself.
1620	func (s *Store) AppsIn(accountID int64) ([]string, error) {
1621	rows, err := s.db.Query(`SELECT app FROM apps WHERE account_id = ? AND deleted_at = 0 ORDER BY app`, accountID)
1622	if err != nil {
1623	return nil, err
1624	}
1625	defer rows.Close()
1626	var out []string
1627	for rows.Next() {
1628	var a string
1629	if err := rows.Scan(&a); err != nil {
1630	return nil, err
1631	}
1632	out = append(out, a)
1633	}
1634	return out, rows.Err()
1635	}
1636
1637	// HasAppIn reports whether an account already claims a LIVE app name.
1638	// Filtered the same way as AppsIn: a trashed name still occupies the row (see
1639	// CreateAppIn / the name-reservation rule) but must read as absent to a caller
1640	// asking "is this name in use right now".
1641	func (s *Store) HasAppIn(accountID int64, app string) (bool, error) {
1642	var n int
1643	err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ? AND deleted_at = 0`,
1644	accountID, app).Scan(&n)
1645	return n > 0, err
1646	}
1647
1648	// SetAppPlacement stamps an app's placement fact — the store-of-record
1649	// fleets.go's appPlacement consults FIRST, ahead of the older route-derived
1650	// fallback (2026-08-06 ruling). target "" clears it back to unplaced. The
1651	// row is guaranteed to exist here: every caller reaches this only after
1652	// apiAccountApp's HasAppIn gate (place) or a just-succeeded CreateAppIn
1653	// (create-with-place), so this is a plain UPDATE, never an upsert.
1654	func (s *Store) claimRawSetAppPlacement(accountID int64, app, target string) error {
1655	_, err := s.db.Exec(`UPDATE apps SET placement = ? WHERE account_id = ? AND app = ?`,
1656	target, accountID, app)
1657	return err
1658	}
1659
1660	// AppFlags reads the two per-app deployment facts the control-plane work
1661	// added: whether this app IS the control plane, and the path `carlos deploy`
1662	// should poll for a build the app's own process reports.
1663	//
1664	// One query for both because both doors that read them read them together
1665	// (the ship gate and the deploy watch), and a second round trip to the same
1666	// row buys nothing.
1667	func (s *Store) AppFlags(accountID int64, app string) (controlPlane bool, buildProbe string, err error) {
1668	err = s.db.QueryRow(`SELECT control_plane, build_probe FROM apps WHERE account_id = ? AND app = ?`,
1669	accountID, app).Scan(&controlPlane, &buildProbe)
1670	if errors.Is(err, sql.ErrNoRows) {
1671	// No row is not an error here: every caller has already been through
1672	// apiAccountApp's byte-identical 404, so reaching this with no row
1673	// means a race with a purge, and "not the control plane, no probe" is
1674	// the right reading of an app that no longer exists.
1675	return false, "", nil
1676	}
1677	return controlPlane, buildProbe, err
1678	}
1679
1680	// SetAppControlPlane marks (or unmarks) an app as the control plane. Marking
1681	// refuses a temporary row (ErrTemporaryApp): the flag refuses destruction,
1682	// and a temporary app exists to be destroyed (temporary static sites §4).
1683	// The row is guaranteed to exist: both callers reach this straight after a
1684	// successful CreateAppIn or behind apiAccountApp's gate.
1685	func (s *Store) claimRawSetAppControlPlane(accountID int64, app string, on bool) error {
1686	if !on {
1687	_, err := s.db.Exec(`UPDATE apps SET control_plane = 0 WHERE account_id = ? AND app = ?`, accountID, app)
1688	return err
1689	}
1690	res, err := s.db.Exec(`UPDATE apps SET control_plane = 1 WHERE account_id = ? AND app = ? AND expires_at = 0`, accountID, app)
1691	if err != nil {
1692	return err
1693	}
1694	if n, _ := res.RowsAffected(); n == 0 {
1695	if l, err := s.AppLife(accountID, app); err == nil && l != nil && l.Temporary() {
1696	return ErrTemporaryApp
1697	}
1698	}
1699	return nil
1700	}
1701
1702	// SetAppBuildProbe stamps the deploy watch's probe path. "" clears it back to
1703	// header-watching.
1704	func (s *Store) claimRawSetAppBuildProbe(accountID int64, app, path string) error {
1705	_, err := s.db.Exec(`UPDATE apps SET build_probe = ? WHERE account_id = ? AND app = ?`,
1706	path, accountID, app)
1707	return err
1708	}
1709
1710	// AppPlacement reads an app's stored placement fact: "" for unplaced (or a
1711	// row this column predates — the ALTER above defaults every existing row to
1712	// "", indistinguishable from "never placed", which is exactly right: the
1713	// caller's route-derived fallback is what recovers a real historical
1714	// placement for those rows). A missing row (an app appPlacement is asked
1715	// about that this store has no claim for at all) also reads as "", not an
1716	// error — the same "absence is un-placed" answer, for a caller that has
1717	// already proven the row exists as much as one that hasn't.
1718	func (s *Store) AppPlacement(accountID int64, app string) (string, error) {
1719	var placement string
1720	err := s.db.QueryRow(`SELECT placement FROM apps WHERE account_id = ? AND app = ?`, accountID, app).
1721	Scan(&placement)
1722	if errors.Is(err, sql.ErrNoRows) {
1723	return "", nil
1724	}
1725	return placement, err
1726	}
1727
1728	// TrashAppRow marks an app trashed. Trashing twice keeps the FIRST timestamp
1729	// (guarded by `deleted_at = 0`) — the cooloff clock starts once, and a second
1730	// trash call (racing UI, a retried request) must not push the purge deadline
1731	// out again.
1732	//
1733	// now is the MARKER's clock at every caller. A temporary row (expires_at > 0)
1734	// also gets its early-purge window in the same statement: purge_for is the
1735	// marker's deleted_at and purge_after is 3 days later (temporary static sites
1736	// spec §5). The window is bound to the marker's clock and to whether the row
1737	// was temporary when the marker was found; a permanent row gets 0 for both.
1738	func (s *Store) claimRawTrashAppRow(accountID int64, app string, now time.Time) error {
1739	at := now.Unix()
1740	_, err := s.db.Exec(`UPDATE apps SET deleted_at = ?,
1741	purge_for = CASE WHEN expires_at > 0 THEN ? ELSE 0 END,
1742	purge_after = CASE WHEN expires_at > 0 THEN ? ELSE 0 END
1743	WHERE account_id = ? AND app = ? AND deleted_at = 0`,
1744	at, at, at+temporaryCooloffSeconds, accountID, app)
1745	return err
1746	}
1747
1748	// RestoreAppRow un-trashes an app and clears everything that only means
1749	// something while trashed (restoring, the early-purge window). Restoring
1750	// something that was never trashed is harmless.
1751	func (s *Store) RestoreAppRow(accountID int64, app string) error {
1752	_, err := s.db.Exec(`UPDATE apps SET deleted_at = 0, restoring = 0, purge_after = 0, purge_for = 0
1753	WHERE account_id = ? AND app = ?`, accountID, app)
1754	return err
1755	}
1756
1757	// PurgeAppRow deletes the row outright — the one place trash state actually
1758	// ends, and the only irreversible verb in this set. The row IS the name
1759	// reservation (see CreateAppIn), so this is also the moment the name frees
1760	// for reuse.
1761	//
1762	// Guarded by `deleted_at != 0`: a live row must be structurally unpurgeable,
1763	// because the console row and the bucket marker can diverge (a restore wrote
1764	// the row back while a marker delete failed elsewhere, or raced it) and the
1765	// auto-purge pass this feeds must never be the thing that frees a serving
1766	// app's name out from under it.
1767	func (s *Store) PurgeAppRow(accountID int64, app string) error {
1768	ctx := context.Background()
1769	tx, err := s.db.BeginTx(ctx, nil)
1770	if err != nil {
1771	return err
1772	}
1773	defer tx.Rollback()
1774	res, err := tx.ExecContext(ctx, `DELETE FROM apps WHERE account_id = ? AND app = ? AND deleted_at != 0`, accountID, app)
1775	if err != nil {
1776	return err
1777	}
1778	if n, _ := res.RowsAffected(); n > 0 {
1779	// The name is free from here, so nothing keyed by it may outlive the
1780	// row (mailevents.go): a later app of the same name starts clean.
1781	var sqid string
1782	if err := tx.QueryRowContext(ctx, `SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); err == nil {
1783	if err := forgetMailApp(ctx, tx, sqid, app); err != nil {
1784	return err
1785	}
1786	} else if err != sql.ErrNoRows {
1787	return err
1788	}
1789	// The same for the app's credentials (discussion #219, item 1). App
1790	// tokens of both kinds are bound (account, name), so a later app of
1791	// this name would inherit them unminted; they are DELETED rather than
1792	// revoked, because RenameAppIn and MoveApp refuse a name that still
1793	// carries any such row, revoked or not. Door keys are revoked rather
1794	// than deleted: the revision has to keep climbing past what boxes
1795	// already admitted, and the re-sign pass publishes the now-empty
1796	// record (apidoorkeys.go).
1797	if _, err := tx.ExecContext(ctx, `DELETE FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`,
1798	cliKindApp, cliKindService, accountID, app); err != nil {
1799	return err
1800	}
1801	if err := revokeAllDoorKeys(tx, accountID, app); err != nil {
1802	return err
1803	}
1804	}
1805	return tx.Commit()
1806	}
1807
1808	// TrashedApp is one app in an account's Trash: past-tense on purpose, since a
1809	// trashed row is inert until restored or purged.
1810	type TrashedApp struct {
1811	App string
1812	DeletedAt time.Time
1813	// PurgeAfter is the row's early-purge deadline (Unix seconds); 0 means
1814	// the app was not temporary when trashed, so effectivePurgeAt falls
1815	// back to the ordinary 30-day cooloff.
1816	PurgeAfter int64
1817	}
1818
1819	// TrashedAppsIn lists an account's trashed apps, sorted by name — the Trash
1820	// page's listing, and the mirror image of AppsIn's "live only" filter.
1821	func (s *Store) TrashedAppsIn(accountID int64) ([]TrashedApp, error) {
1822	rows, err := s.db.Query(`SELECT app, deleted_at, purge_after FROM apps WHERE account_id = ? AND deleted_at != 0 ORDER BY app`,
1823	accountID)
1824	if err != nil {
1825	return nil, err
1826	}
1827	defer rows.Close()
1828	var out []TrashedApp
1829	for rows.Next() {
1830	var t TrashedApp
1831	var deletedAt int64
1832	if err := rows.Scan(&t.App, &deletedAt, &t.PurgeAfter); err != nil {
1833	return nil, err
1834	}
1835	t.DeletedAt = time.Unix(deletedAt, 0).UTC()
1836	out = append(out, t)
1837	}
1838	return out, rows.Err()
1839	}
1840
1841	// AppNameState reports, in one read, whether an account holds app live, in
1842	// the trash, or not at all — for a caller that must tell the three apart
1843	// without a restore or a trash landing between two separate questions.
1844	func (s *Store) AppNameState(accountID int64, app string) (live, trashed bool, err error) {
1845	var deletedAt int64
1846	err = s.db.QueryRow(`SELECT deleted_at FROM apps WHERE account_id = ? AND app = ?`, accountID, app).Scan(&deletedAt)
1847	if errors.Is(err, sql.ErrNoRows) {
1848	return false, false, nil
1849	}
1850	if err != nil {
1851	return false, false, err
1852	}
1853	return deletedAt == 0, deletedAt != 0, nil
1854	}
1855
1856	// HasTrashedAppIn reports whether an account holds a TRASHED row for app —
1857	// the check the claim-once door uses to explain a refusal ("that name is in
1858	// the trash") rather than just saying no.
1859	func (s *Store) HasTrashedAppIn(accountID int64, app string) (bool, error) {
1860	var n int
1861	err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ? AND deleted_at != 0`,
1862	accountID, app).Scan(&n)
1863	return n > 0, err
1864	}
1865
1866	// AppRow is one apps row carrying its account's SQID — the coordinate the
1867	// bucket is keyed by. The console's own tables key apps by account id, but
1868	// every object belonging to an app lives under apps/<sqid>/<app>/, so a pass
1869	// that compares rows against the control plane needs both.
1870	type AppRow struct {
1871	AccountID int64
1872	Sqid string
1873	App string
1874	Deleted bool // trashed here (deleted_at != 0), not purged
1875	// The lifecycle columns (temporary static sites spec §2), Unix seconds.
1876	DeletedAt int64
1877	ExpiresAt int64 // 0 = permanent
1878	Restoring bool
1879	PurgeAfter int64 // 0 = no early purge
1880	PurgeFor int64
1881	}
1882
1883	// appRowCols is the SELECT list every AppRow query uses, in scanAppRows'
1884	// order: p is apps, a is accounts.
1885	const appRowCols = `p.account_id, a.sqid, p.app, p.deleted_at, p.expires_at, p.restoring, p.purge_after, p.purge_for`
1886
1887	func scanAppRows(rows *sql.Rows) ([]AppRow, error) {
1888	defer rows.Close()
1889	var out []AppRow
1890	for rows.Next() {
1891	var r AppRow
1892	if err := rows.Scan(&r.AccountID, &r.Sqid, &r.App, &r.DeletedAt, &r.ExpiresAt, &r.Restoring, &r.PurgeAfter, &r.PurgeFor); err != nil {
1893	return nil, err
1894	}
1895	r.Deleted = r.DeletedAt != 0
1896	out = append(out, r)
1897	}
1898	return out, rows.Err()
1899	}
1900
1901	// AppRows lists every app row of every LIVE account, trashed ones included —
1902	// the input to MaterializeApps, which is the one caller.
1903	//
1904	// It has to be row-driven rather than bucket-driven: a purged app has no keys
1905	// left to list, so the only evidence it ever existed is the row still holding
1906	// its name. The join drops rows belonging to a marked account, which ReapAccount
1907	// already deletes — a straggler must never be converged against a dead
1908	// account's prefix, and freeing its name is that account's business, not this
1909	// pass's.
1910	func (s *Store) AppRows() ([]AppRow, error) {
1911	rows, err := s.db.Query(`SELECT ` + appRowCols + `
1912	FROM apps p JOIN accounts a ON a.id = p.account_id
1913	WHERE a.deleted_at = 0 ORDER BY a.sqid, p.app`)
1914	if err != nil {
1915	return nil, err
1916	}
1917	return scanAppRows(rows)
1918	}
1919
1920	// AppAccounts lists the accounts that claim an app NAME, ascending. Usually
1921	// one; several is legitimate (names are unique per account, not globally) and
1922	// is what makes MoveApp require an explicit source.
1923	func (s *Store) AppAccounts(app string) ([]int64, error) {
1924	rows, err := s.db.Query(`SELECT account_id FROM apps WHERE app = ? ORDER BY account_id`, app)
1925	if err != nil {
1926	return nil, err
1927	}
1928	defer rows.Close()
1929	var out []int64
1930	for rows.Next() {
1931	var id int64
1932	if err := rows.Scan(&id); err != nil {
1933	return nil, err
1934	}
1935	out = append(out, id)
1936	}
1937	return out, rows.Err()
1938	}
1939
1940	// MoveApp re-homes a claimed app from one account to another — the console
1941	// half of `carlos accounts migrate`, which moves the bucket objects and the
1942	// registry routes but deliberately never opens this database.
1943	//
1944	// Idempotent: if the row is already at the destination there is nothing to
1945	// do, so an operator who is unsure whether the move landed can simply run it
1946	// again. Moving onto a name the destination already claims is REFUSED rather
1947	// than merged — those are two different apps, and one of them would vanish.
1948	func (s *Store) MoveApp(app string, from, to int64) error {
1949	tx, err := s.db.Begin()
1950	if err != nil {
1951	return err
1952	}
1953	defer tx.Rollback()
1954	var atDest, atSrc int
1955	if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ? AND account_id = ?`, app, to).Scan(&atDest); err != nil {
1956	return err
1957	}
1958	if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&atSrc); err != nil {
1959	return err
1960	}
1961	if atDest > 0 {
1962	if atSrc > 0 {
1963	return fmt.Errorf("console: account %d already claims %q — moving onto it would merge two different apps", to, app)
1964	}
1965	return nil // already moved
1966	}
1967	if atSrc == 0 {
1968	return fmt.Errorf("console: account %d does not claim %q", from, app)
1969	}
1970	var srcExpires int64
1971	if err := tx.QueryRow(`SELECT expires_at FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&srcExpires); err != nil {
1972	return err
1973	}
1974	if srcExpires > 0 {
1975	return ErrTemporaryApp
1976	}
1977	// App-scoped CLI tokens — legacy kind='app' AND kind='service' — are
1978	// bound (account, name), so they must follow the app or the running
1979	// deployment's credential 404s while retaining authority over the name
1980	// in the SOURCE account. Tokens already at the destination under this
1981	// name are a purged predecessor's leftovers — re-homing onto them would
1982	// hand this app a credential nobody minted for it, so that refuses,
1983	// exactly as RenameAppIn refuses. Both kinds must be counted here: a
1984	// service principal the guard cannot see is an orphaned credential a
1985	// LATER app of the same name inherits unminted (see
1986	// TestRenameAppMovesServicePrincipalToo for the RenameAppIn twin of this
1987	// hazard).
1988	var staleTokens int
1989	if err := tx.QueryRow(`SELECT COUNT(*) FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`,
1990	cliKindApp, cliKindService, to, app).Scan(&staleTokens); err != nil {
1991	return err
1992	}
1993	if staleTokens > 0 {
1994	return fmt.Errorf("console: the destination account still has app credentials for %q from a previous app — revoke them first", app)
1995	}
1996	// A second, narrower collision the guard above cannot see:
1997	// cli_tokens_service_name only requires a live kind='service' row's NAME
1998	// to be unique ACCOUNT-WIDE — it does not care whether the row is bound
1999	// to this app or to none at all (NewServicePrincipal's own uniqueness
2000	// check is the same shape). So a service principal travelling with `app`
2001	// can collide with an ACCOUNT-SCOPED credential already sitting at the
2002	// destination under the same name, and the guard above cannot catch that
2003	// — it only counts rows still bound to `app` there, and the colliding
2004	// row is not one of them. Left uncaught, the UPDATE below would trip the
2005	// unique index inside this transaction — safe (it rolls back) but with
2006	// SQLite's raw constraint-violation text rather than a sentence in this
2007	// guard's own voice — and a generic name like "hub" or "ci" makes it
2008	// reachable in practice.
2009	var collidingName string
2010	err = tx.QueryRow(`SELECT dst.name FROM cli_tokens dst
2011	WHERE dst.kind = ? AND dst.account_id = ? AND dst.revoked_at = 0
2012	AND dst.name IN (
2013	SELECT src.name FROM cli_tokens src
2014	WHERE src.kind = ? AND src.account_id = ? AND src.app = ? AND src.revoked_at = 0
2015	)
2016	LIMIT 1`, cliKindService, to, cliKindService, from, app).Scan(&collidingName)
2017	if err != nil && err != sql.ErrNoRows {
2018	return err
2019	}
2020	if err == nil {
2021	return fmt.Errorf("console: the destination account already holds a live service credential named %q — rename or revoke it first", collidingName)
2022	}
2023	var createdAt int64
2024	if err := tx.QueryRow(`SELECT created_at FROM apps WHERE app = ? AND account_id = ?`, app, from).Scan(&createdAt); err != nil {
2025	return err
2026	}
2027	if _, err := tx.Exec(`UPDATE apps SET account_id = ? WHERE app = ? AND account_id = ?`,
2028	to, app, from); err != nil {
2029	return err
2030	}
2031	if _, err := tx.Exec(`UPDATE cli_tokens SET account_id = ? WHERE kind IN (?, ?) AND account_id = ? AND app = ?`,
2032	to, cliKindApp, cliKindService, from, app); err != nil {
2033	return err
2034	}
2035	// Door keys cannot follow the app: each key names its account's sqid
2036	// (doorkey.Token), and the edge admits it only for routes of that account,
2037	// so the moved app's processes need new keys either way. What the move
2038	// must not leave behind is a LIVE key on either side (discussion #219,
2039	// item 1): the source's would be inherited by a later app of this name
2040	// there, and any at the destination are a purged predecessor's, which the
2041	// moved-in app would otherwise answer to. The destination has no row for
2042	// this name (checked above), so nothing live owns those keys.
2043	if err := revokeAllDoorKeys(tx, from, app); err != nil {
2044	return err
2045	}
2046	if err := revokeAllDoorKeys(tx, to, app); err != nil {
2047	return err
2048	}
2049	// Email history is keyed by the account's sqid, not its id, so it is
2050	// carried by sqid (mailevents.go).
2051	var fromSqid, toSqid string
2052	if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, from).Scan(&fromSqid); err != nil {
2053	return err
2054	}
2055	if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, to).Scan(&toSqid); err != nil {
2056	return err
2057	}
2058	if err := carryMailApp(context.Background(), tx, fromSqid, app, toSqid, app, createdAt); err != nil {
2059	return err
2060	}
2061	return tx.Commit()
2062	}
2063
2064	// RenameAppIn renames an app that has never deployed. The CALLER holds the
2065	// wider refusals — nothing under the app's bucket prefix, no registry route
2066	// wearing the name — because an app name is a literal key segment in both,
2067	// and this database is the only store a rename may touch. Here the refusals
2068	// are this database's own:
2069	//
2070	// - The new name must be free in this account, live or trashed — a trashed
2071	// row still owns its name until purged, exactly as CreateAppIn treats it.
2072	// - Neither name may appear in the legacy owners table. Those grants are
2073	// keyed by bare name across ALL accounts, so rewriting them could hand
2074	// one account's grant to another, and renaming ONTO one would let the new
2075	// name inherit a grant nobody issued it. Claim-proof stays claim-proof.
2076	// - The new name must carry no app-scoped CLI tokens — legacy kind='app'
2077	// OR kind='service'. A purged app's tokens outlive its row, and a
2078	// credential that suddenly binds to a NEW app is authority nobody
2079	// minted.
2080	//
2081	// What follows the app: its audit rows — but only those written since THIS
2082	// app's row was created, because a purged predecessor of the same name left
2083	// its rows behind and its history is not this app's; and its app-scoped CLI
2084	// tokens (both kinds), which are bound (account, name) and would otherwise
2085	// 404 the running app while retaining authority over the freed name. The
2086	// rename itself is the caller's audit line.
2087	func (s *Store) RenameAppIn(accountID int64, from, to string) error {
2088	return s.renameAppIn(accountID, from, to, false)
2089	}
2090
2091	// UndoRenameAppIn reverses a rename that just landed (handleAppRename's
2092	// post-commit re-check). It is the one path that may rename INTO the tmp-
2093	// namespace, because it only ever puts back the name the app already had: a
2094	// kept temporary app keeps its tmp- name, and renaming it away and then
2095	// undoing must be able to land.
2096	func (s *Store) UndoRenameAppIn(accountID int64, renamed, original string) error {
2097	return s.renameAppIn(accountID, renamed, original, true)
2098	}
2099
2100	func (s *Store) renameAppIn(accountID int64, from, to string, undo bool) error {
2101	if !undo && release.IsTemporaryName(to) {
2102	return ErrTemporaryNameReserved
2103	}
2104	tx, err := s.db.Begin()
2105	if err != nil {
2106	return err
2107	}
2108	defer tx.Rollback()
2109	var createdAt, expiresAt int64
2110	err = tx.QueryRow(`SELECT created_at, expires_at FROM apps WHERE account_id = ? AND app = ? AND deleted_at = 0`,
2111	accountID, from).Scan(&createdAt, &expiresAt)
2112	if err == sql.ErrNoRows {
2113	return fmt.Errorf("console: this account does not claim %q", from)
2114	} else if err != nil {
2115	return err
2116	}
2117	if expiresAt > 0 {
2118	return ErrTemporaryApp
2119	}
2120	var legacy int
2121	if err := tx.QueryRow(`SELECT COUNT(*) FROM owners WHERE app IN (?, ?)`, from, to).Scan(&legacy); err != nil {
2122	return err
2123	}
2124	if legacy > 0 {
2125	return fmt.Errorf("console: %q or %q carries a legacy owner grant — renaming across it is an operator act", from, to)
2126	}
2127	var taken int
2128	if err := tx.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND app = ?`, accountID, to).Scan(&taken); err != nil {
2129	return err
2130	}
2131	if taken > 0 {
2132	return fmt.Errorf("console: this account already has an app (or a trashed app) named %q", to)
2133	}
2134	var staleTokens int
2135	if err := tx.QueryRow(`SELECT COUNT(*) FROM cli_tokens WHERE kind IN (?, ?) AND account_id = ? AND app = ?`,
2136	cliKindApp, cliKindService, accountID, to).Scan(&staleTokens); err != nil {
2137	return err
2138	}
2139	if staleTokens > 0 {
2140	return fmt.Errorf("console: %q still has app credentials from a previous app of that name — revoke them first", to)
2141	}
2142	if _, err := tx.Exec(`UPDATE apps SET app = ? WHERE account_id = ? AND app = ? AND deleted_at = 0`,
2143	to, accountID, from); err != nil {
2144	return err
2145	}
2146	if _, err := tx.Exec(`UPDATE audit SET app = ? WHERE account_id = ? AND app = ? AND created_at >= ?`,
2147	to, accountID, from, createdAt); err != nil {
2148	return err
2149	}
2150	if _, err := tx.Exec(`UPDATE cli_tokens SET app = ? WHERE kind IN (?, ?) AND account_id = ? AND app = ?`,
2151	to, cliKindApp, cliKindService, accountID, from); err != nil {
2152	return err
2153	}
2154	var sqid string
2155	if err := tx.QueryRow(`SELECT sqid FROM accounts WHERE id = ?`, accountID).Scan(&sqid); err != nil {
2156	return err
2157	}
2158	if err := carryMailApp(context.Background(), tx, sqid, from, sqid, to, createdAt); err != nil {
2159	return err
2160	}
2161	return tx.Commit()
2162	}
2163
2164	// HasApp reports whether an app name is claimed in the apps table.
2165	func (s *Store) HasApp(app string) (bool, error) {
2166	var n int
2167	err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE app = ?`, app).Scan(&n)
2168	return n > 0, err
2169	}
2170
2171	// HasOwners reports whether ANY owner exists for app — CLI-granted apps
2172	// predate the apps table and must stay claim-proof.
2173	func (s *Store) HasOwners(app string) (bool, error) {
2174	var n int
2175	err := s.db.QueryRow(`SELECT COUNT(*) FROM owners WHERE app = ?`, app).Scan(&n)
2176	return n > 0, err
2177	}
2178
2179	// ---- users ------------------------------------------------------------------
2180
2181	// EnsureUser records address as a console user if it isn't one yet. created
2182	// reports whether this call was the sign-up.
2183	func (s *Store) EnsureUser(address string, now time.Time) (created bool, err error) {
2184	res, err := s.db.Exec(`INSERT INTO users (address, created_at) VALUES (?, ?)
2185	ON CONFLICT(address) DO NOTHING`, address, now.Unix())
2186	if err != nil {
2187	return false, err
2188	}
2189	n, err := res.RowsAffected()
2190	return n > 0, err
2191	}
2192
2193	// SetOperator flips address's fleet-operator bit — box-side only, exactly
2194	// like an app grant: it works whether or not address has ever signed in,
2195	// upserting a users row if none exists yet (leaving created_at at "now" for
2196	// a brand-new row, untouched for an existing one).
2197	func (s *Store) SetOperator(address string, on bool) error {
2198	v := 0
2199	if on {
2200	v = 1
2201	}
2202	_, err := s.db.Exec(`INSERT INTO users (address, created_at, operator) VALUES (?, ?, ?)
2203	ON CONFLICT(address) DO UPDATE SET operator = excluded.operator`, address, time.Now().Unix(), v)
2204	return err
2205	}
2206
2207	// IsOperator reports whether address holds the fleet-operator bit. An
2208	// address with no users row (never signed in) is not an operator.
2209	func (s *Store) IsOperator(address string) (bool, error) {
2210	var v int
2211	err := s.db.QueryRow(`SELECT operator FROM users WHERE address = ?`, address).Scan(&v)
2212	if err == sql.ErrNoRows {
2213	return false, nil
2214	}
2215	if err != nil {
2216	return false, err
2217	}
2218	return v != 0, nil
2219	}
2220
2221	// Operators lists every address holding the fleet-operator bit, sorted —
2222	// the alert watcher's recipient list. Sorted so a pass mails people in a
2223	// stable order and a test can assert on it.
2224	func (s *Store) Operators() ([]string, error) {
2225	rows, err := s.db.Query(`SELECT address FROM users WHERE operator != 0 ORDER BY address`)
2226	if err != nil {
2227	return nil, err
2228	}
2229	defer rows.Close()
2230	var out []string
2231	for rows.Next() {
2232	var a string
2233	if err := rows.Scan(&a); err != nil {
2234	return nil, err
2235	}
2236	out = append(out, a)
2237	}
2238	return out, rows.Err()
2239	}
2240
2241	// ---- alert watcher state (alertwatch.go) ------------------------------------
2242
2243	// AlertSeen reports the firing state the console last NOTIFIED address
2244	// about for (box, rule), and whether it has ever told them anything about
2245	// that rule. known=false is not the same as firing=false: a rule this
2246	// operator has never been told about fires as news, while one they were
2247	// last told was quiet stays quiet.
2248	//
2249	// The empty address is the watcher's synthetic "nobody was mailable"
2250	// recipient (alertwatch.go), not a real user.
2251	func (s *Store) AlertSeen(box, rule, address string) (firing bool, known bool, err error) {
2252	var v int
2253	err = s.db.QueryRow(`SELECT firing FROM alert_seen WHERE box = ? AND rule = ? AND address = ?`,
2254	box, rule, address).Scan(&v)
2255	if err == sql.ErrNoRows {
2256	return false, false, nil
2257	}
2258	if err != nil {
2259	return false, false, err
2260	}
2261	return v != 0, true, nil
2262	}
2263
2264	// SetAlertSeen records that address has been told (box, rule) is in this
2265	// state. Written per recipient, only after THAT recipient's send succeeded,
2266	// so one dead grant leaves one row unrecorded and the next pass retries
2267	// that operator alone.
2268	func (s *Store) SetAlertSeen(box, rule, address string, firing bool) error {
2269	v := 0
2270	if firing {
2271	v = 1
2272	}
2273	_, err := s.db.Exec(`INSERT INTO alert_seen (box, rule, address, firing) VALUES (?, ?, ?, ?)
2274	ON CONFLICT(box, rule, address) DO UPDATE SET firing = excluded.firing`, box, rule, address, v)
2275	return err
2276	}
2277
2278	// ---- move pass pages (movesrecovery.go) -------------------------------------
2279
2280	// MovePageAttempt is when the move pass last attempted the page for rule,
2281	// to the second, and whether it ever did.
2282	func (s *Store) MovePageAttempt(rule string) (time.Time, bool, error) {
2283	var at int64
2284	err := s.db.QueryRow(`SELECT attempted_at FROM move_pages WHERE rule = ?`, rule).Scan(&at)
2285	if err == sql.ErrNoRows {
2286	return time.Time{}, false, nil
2287	}
2288	if err != nil {
2289	return time.Time{}, false, err
2290	}
2291	return time.Unix(at, 0).UTC(), true, nil
2292	}
2293
2294	// SetMovePageAttempt records an attempt at the page for rule at.
2295	func (s *Store) SetMovePageAttempt(rule string, at time.Time) error {
2296	_, err := s.db.Exec(`INSERT INTO move_pages (rule, attempted_at) VALUES (?, ?)
2297	ON CONFLICT(rule) DO UPDATE SET attempted_at = excluded.attempted_at`, rule, at.Unix())
2298	return err
2299	}
2300
2301	// MoveLogPendingEntry is one move log entry the move pass owes.
2302	type MoveLogPendingEntry struct {
2303	Host, Op, Name, Incarnation string
2304	Body []byte
2305	QueuedAt time.Time
2306	Confirmed, Warned, Held bool
2307	Attempts int
2308	}
2309
2310	// QueueMoveLog keeps an entry for the drainer. An entry already queued under
2311	// the name is kept as it was. An unconfirmed entry is not delivered until
2312	// ConfirmMoveLog.
2313	func (s *Store) QueueMoveLog(host, op, name, incarnation string, body []byte, confirmed bool, at time.Time) error {
2314	_, err := s.db.Exec(`INSERT INTO move_log_outbox (host, op, name, incarnation, body, queued_at, confirmed) VALUES (?, ?, ?, ?, ?, ?, ?)
2315	ON CONFLICT(host, op, name) DO NOTHING`, host, op, name, incarnation, body, at.Unix(), confirmed)
2316	return err
2317	}
2318
2319	// ConfirmMoveLog marks a queued entry deliverable, due at once.
2320	func (s *Store) ConfirmMoveLog(host, op, name string) error {
2321	_, err := s.db.Exec(`UPDATE move_log_outbox SET confirmed = 1, next_at = 0 WHERE host = ? AND op = ? AND name = ?`, host, op, name)
2322	return err
2323	}
2324
2325	func scanMoveLogRows(rows *sql.Rows) ([]MoveLogPendingEntry, error) {
2326	defer rows.Close()
2327	var out []MoveLogPendingEntry
2328	for rows.Next() {
2329	var e MoveLogPendingEntry
2330	var at int64
2331	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 {
2332	return nil, err
2333	}
2334	e.QueuedAt = time.Unix(at, 0).UTC()
2335	out = append(out, e)
2336	}
2337	return out, rows.Err()
2338	}
2339
2340	const moveLogColumns = `host, op, name, incarnation, body, queued_at, confirmed, attempts, warned, held`
2341
2342	// MoveLogPending is one move's queued entries, oldest first.
2343	func (s *Store) MoveLogPending(host, op string) ([]MoveLogPendingEntry, error) {
2344	rows, err := s.db.Query(`SELECT `+moveLogColumns+` FROM move_log_outbox WHERE host = ? AND op = ? ORDER BY rowid`, host, op)
2345	if err != nil {
2346	return nil, err
2347	}
2348	return scanMoveLogRows(rows)
2349	}
2350
2351	// MoveLogDue is up to limit queued entries whose next attempt is due at now,
2352	// the longest due first: every attempt that does not settle a row moves its
2353	// next attempt on, so due rows rotate through the batch.
2354	func (s *Store) MoveLogDue(now time.Time, limit int) ([]MoveLogPendingEntry, error) {
2355	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)
2356	if err != nil {
2357	return nil, err
2358	}
2359	return scanMoveLogRows(rows)
2360	}
2361
2362	// RetryMoveLog records a failed attempt: the attempt count, the next
2363	// attempt's time and whether the row's warning has been given.
2364	func (s *Store) RetryMoveLog(host, op, name string, attempts int, next time.Time, warned bool) error {
2365	_, err := s.db.Exec(`UPDATE move_log_outbox SET attempts = ?, next_at = ?, warned = ? WHERE host = ? AND op = ? AND name = ?`,
2366	attempts, next.Unix(), warned, host, op, name)
2367	return err
2368	}
2369
2370	// HoldMoveLog stops a row's delivery for good, keeping its bytes: the writer
2371	// refused it (it can never be written as it stands), and only its
2372	// incarnation's purge removes it. Its warning has been given.
2373	func (s *Store) HoldMoveLog(host, op, name string) error {
2374	_, err := s.db.Exec(`UPDATE move_log_outbox SET held = 1, warned = 1 WHERE host = ? AND op = ? AND name = ?`, host, op, name)
2375	return err
2376	}
2377
2378	// PurgeMoveLogIncarnation drops every row of one instance incarnation: the
2379	// incarnation is retired or purged, so none of them can ever be written.
2380	func (s *Store) PurgeMoveLogIncarnation(host, incarnation string) error {
2381	_, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND incarnation = ?`, host, incarnation)
2382	return err
2383	}
2384
2385	// SettleMoveLog drops an entry once it is written, found written, or can
2386	// never be.
2387	func (s *Store) SettleMoveLog(host, op, name string) error {
2388	_, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND op = ? AND name = ?`, host, op, name)
2389	return err
2390	}
2391
2392	// DropUnconfirmedMoveLog deletes a queued entry only while it is still
2393	// unconfirmed: a confirmation that landed since the caller read the row
2394	// keeps it.
2395	func (s *Store) DropUnconfirmedMoveLog(host, op, name string) error {
2396	_, err := s.db.Exec(`DELETE FROM move_log_outbox WHERE host = ? AND op = ? AND name = ? AND confirmed = 0`, host, op, name)
2397	return err
2398	}
2399
2400	// ---- grants (the console's Keymail access tokens) ---------------------------
2401
2402	// SetGrantToken stores the Keymail access token the user's OAuth approval
2403	// minted for the console — the instrument of future confirmation-link
2404	// sign-ins. Server-side only; it never reaches a browser.
2405	func (s *Store) SetGrantToken(address, token string, now time.Time) error {
2406	_, err := s.db.Exec(`INSERT INTO grants (address, token, created_at, updated_at)
2407	VALUES (?, ?, ?, ?)
2408	ON CONFLICT(address) DO UPDATE SET token = excluded.token, updated_at = excluded.updated_at`,
2409	address, token, now.Unix(), now.Unix())
2410	return err
2411	}
2412
2413	// GrantToken returns the stored Keymail token for address, "" when none.
2414	func (s *Store) GrantToken(address string) (string, error) {
2415	var tok string
2416	err := s.db.QueryRow(`SELECT token FROM grants WHERE address = ?`, address).Scan(&tok)
2417	if err == sql.ErrNoRows {
2418	return "", nil
2419	}
2420	return tok, err
2421	}
2422
2423	// ---- sessions ---------------------------------------------------------------
2424
2425	func hashToken(t string) string {
2426	h := sha256.Sum256([]byte(t))
2427	return hex.EncodeToString(h[:])
2428	}
2429
2430	func randomToken() (string, error) {
2431	b := make([]byte, 32)
2432	if _, err := rand.Read(b); err != nil {
2433	return "", err
2434	}
2435	return base64.RawURLEncoding.EncodeToString(b), nil
2436	}
2437
2438	// CreateSession mints a console session for address, returning the raw
2439	// token the cookie carries. Only the sha256 of it is stored.
2440	func (s *Store) CreateSession(address, keymailToken string, now time.Time) (string, error) {
2441	return s.CreateSessionMethod(address, keymailToken, MethodKeymail, now)
2442	}
2443
2444	// CreateSessionMethod is CreateSession with the way the session was proved
2445	// recorded: MethodKeymail or MethodEmail.
2446	func (s *Store) CreateSessionMethod(address, keymailToken, method string, now time.Time) (string, error) {
2447	raw, err := randomToken()
2448	if err != nil {
2449	return "", err
2450	}
2451	_, err = s.db.Exec(`INSERT INTO sessions (token_hash, address, keymail_token, method, created_at, expires_at)
2452	VALUES (?, ?, ?, ?, ?, ?)`,
2453	hashToken(raw), address, keymailToken, method, now.Unix(), now.Add(SessionTTL).Unix())
2454	if err != nil {
2455	return "", err
2456	}
2457	return raw, nil
2458	}
2459
2460	// SessionAddress resolves a raw session token to its address; ok is false
2461	// for unknown or expired tokens.
2462	func (s *Store) SessionAddress(rawToken string, now time.Time) (string, bool) {
2463	var addr string
2464	var exp int64
2465	err := s.db.QueryRow(`SELECT address, expires_at FROM sessions WHERE token_hash = ?`,
2466	hashToken(rawToken)).Scan(&addr, &exp)
2467	if err != nil || now.Unix() > exp {
2468	return "", false
2469	}
2470	return addr, true
2471	}
2472
2473	// SessionInfo resolves a raw session token to its address and the method that
2474	// proved it; ok is false for unknown or expired tokens.
2475	func (s *Store) SessionInfo(rawToken string, now time.Time) (address, method string, ok bool) {
2476	var exp int64
2477	err := s.db.QueryRow(`SELECT address, method, expires_at FROM sessions WHERE token_hash = ?`,
2478	hashToken(rawToken)).Scan(&address, &method, &exp)
2479	if err != nil || now.Unix() > exp {
2480	return "", "", false
2481	}
2482	return address, method, true
2483	}
2484
2485	// DeleteSession removes a session by its raw token.
2486	func (s *Store) DeleteSession(rawToken string) error {
2487	_, err := s.db.Exec(`DELETE FROM sessions WHERE token_hash = ?`, hashToken(rawToken))
2488	return err
2489	}
2490
2491	// PurgeExpired drops expired sessions, confirmation tokens and email challenges — and, on the
2492	// same pass, the CLI login requests and tokens that have aged out (throttled
2493	// inside PurgeCLIAuth, which is also called from the login path).
2494	func (s *Store) PurgeExpired(now time.Time) error {
2495	if _, err := s.db.Exec(`DELETE FROM sessions WHERE expires_at < ?`, now.Unix()); err != nil {
2496	return err
2497	}
2498	if _, err := s.db.Exec(`DELETE FROM confirmations WHERE expires_at < ?`, now.Unix()); err != nil {
2499	return err
2500	}
2501	if _, err := s.db.Exec(`DELETE FROM email_challenges WHERE expires_at < ?`, now.Unix()); err != nil {
2502	return err
2503	}
2504	s.PurgeCLIAuth(now)
2505	return nil
2506	}
2507
2508	// ---- confirmations (returning sign-in links) --------------------------------
2509
2510	// CreateConfirmation mints a single-use sign-in confirmation token for
2511	// address (15 min TTL), returning the raw token for the emailed link. Only
2512	// its hash is stored.
2513	func (s *Store) CreateConfirmation(address string, now time.Time) (string, error) {
2514	raw, err := randomToken()
2515	if err != nil {
2516	return "", err
2517	}
2518	_, err = s.db.Exec(`INSERT INTO confirmations (token_hash, address, created_at, expires_at)
2519	VALUES (?, ?, ?, ?)`,
2520	hashToken(raw), address, now.Unix(), now.Add(ConfirmationTTL).Unix())
2521	if err != nil {
2522	return "", err
2523	}
2524	return raw, nil
2525	}
2526
2527	// TakeConfirmation consumes a confirmation token: the row is deleted before
2528	// the expiry verdict (burn-then-check, keymail takeOAuthCode manners), so a
2529	// token can never be retried into validity.
2530	func (s *Store) TakeConfirmation(rawToken string, now time.Time) (string, bool) {
2531	tx, err := s.db.Begin()
2532	if err != nil {
2533	return "", false
2534	}
2535	defer tx.Rollback()
2536	var addr string
2537	var exp int64
2538	err = tx.QueryRow(`SELECT address, expires_at FROM confirmations WHERE token_hash = ?`,
2539	hashToken(rawToken)).Scan(&addr, &exp)
2540	if err != nil {
2541	return "", false
2542	}
2543	if _, err := tx.Exec(`DELETE FROM confirmations WHERE token_hash = ?`, hashToken(rawToken)); err != nil {
2544	return "", false
2545	}
2546	if err := tx.Commit(); err != nil {
2547	return "", false
2548	}
2549	if now.Unix() > exp {
2550	return "", false
2551	}
2552	return addr, true
2553	}
2554
2555	// ---- audit (recent activity) ------------------------------------------------
2556
2557	// Event is one audit row: something an owner did to an app through the
2558	// console, in the browser or through its API. Platform state stays in the
2559	// registry and the bucket; direct-bucket mutations do not enter this trail.
2560	type Event struct {
2561	App, Address, Action, TargetType, Target, Message, Detail string
2562	TargetURL string
2563	CreatedAt time.Time
2564	}
2565
2566	// Activity is the structured half of an audit row. Action and target are
2567	// chosen by the server after it has authorised and applied the mutation;
2568	// Message is optional context supplied by the person making it. Detail is
2569	// retained for rows and callers which predate the structured shape.
2570	type Activity struct {
2571	Action, TargetType, Target, Message, Detail string
2572	}
2573
2574	// RecordEvent appends one audit row. Callers treat it as best-effort:
2575	// losing an audit line must never fail the action it records.
2576	//
2577	// Keyed on (account, app), never the app name alone: two accounts can both
2578	// own a "blog", and a shared activity stream would show each of them the
2579	// other's actions — including the other account's member addresses, which is
2580	// a membership leak dressed up as a convenience.
2581	func (s *Store) claimRawRecordEvent(accountID int64, app, address, action, detail string, now time.Time) error {
2582	return s.RecordActivity(accountID, app, address, Activity{Action: action, Detail: detail}, now)
2583	}
2584
2585	// RecordActivity appends a structured audit row. The account and app remain
2586	// separate arguments so callers cannot accidentally move either into
2587	// user-controlled metadata.
2588	func (s *Store) claimRawRecordActivity(accountID int64, app, address string, activity Activity, now time.Time) error {
2589	_, err := s.db.Exec(`INSERT INTO audit
2590	(account_id, app, address, action, target_type, target, message, detail, created_at)
2591	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, accountID, app, address, activity.Action,
2592	activity.TargetType, activity.Target, activity.Message, activity.Detail, now.Unix())
2593	return err
2594	}
2595
2596	// Events returns one account's audit rows for an app, newest first, capped at
2597	// limit.
2598	func (s *Store) Events(accountID int64, app string, limit int) ([]Event, error) {
2599	rows, err := s.db.Query(`SELECT app, address, action, target_type, target, message, detail, created_at
2600	FROM audit WHERE account_id = ? AND app = ? ORDER BY id DESC LIMIT ?`, accountID, app, limit)
2601	if err != nil {
2602	return nil, err
2603	}
2604	defer rows.Close()
2605	var out []Event
2606	for rows.Next() {
2607	var e Event
2608	var ts int64
2609	if err := rows.Scan(&e.App, &e.Address, &e.Action, &e.TargetType, &e.Target,
2610	&e.Message, &e.Detail, &ts); err != nil {
2611	return nil, err
2612	}
2613	e.CreatedAt = time.Unix(ts, 0).UTC()
2614	out = append(out, e)
2615	}
2616	return out, rows.Err()
2617	}
2618
2619	// AppRef names one app by its account's sqid, as the bucket keys it.
2620	type AppRef struct{ Sqid, App string }
2621
2622	// AppsWithActionSince answers, once each, every (account, app) with an audit
2623	// row of action stamped at or after since. The run sweep's orphan
2624	// housekeeping lists runs/ only for apps with a recent run-start (S3
2625	// change-index design §4.6). A plain read, like Events.
2626	func (s *Store) AppsWithActionSince(action string, since time.Time) ([]AppRef, error) {
2627	rows, err := s.db.Query(`SELECT DISTINCT accounts.sqid, audit.app
2628	FROM audit JOIN accounts ON accounts.id = audit.account_id
2629	WHERE audit.action = ? AND audit.created_at >= ?
2630	ORDER BY accounts.sqid, audit.app`, action, since.Unix())
2631	if err != nil {
2632	return nil, err
2633	}
2634	defer rows.Close()
2635	var out []AppRef
2636	for rows.Next() {
2637	var r AppRef
2638	if err := rows.Scan(&r.Sqid, &r.App); err != nil {
2639	return nil, err
2640	}
2641	out = append(out, r)
2642	}
2643	return out, rows.Err()
2644	}
2645
2646	// ---- keys -------------------------------------------------------------------
2647
2648	// SigningKey returns the console's long-lived envelope-signing key (ECDSA
2649	// P-256 — the curve keymail's app API pins in a grant), generating and
2650	// persisting it on first use.
2651	func (s *Store) SigningKey() (*ecdsa.PrivateKey, error) {
2652	load := func() (*ecdsa.PrivateKey, error) {
2653	var b64 string
2654	if err := s.db.QueryRow(`SELECT pkcs8 FROM keys WHERE name = 'keymail-sign'`).Scan(&b64); err != nil {
2655	return nil, err
2656	}
2657	der, err := base64.StdEncoding.DecodeString(b64)
2658	if err != nil {
2659	return nil, fmt.Errorf("console: stored signing key: %v", err)
2660	}
2661	key, err := x509.ParsePKCS8PrivateKey(der)
2662	if err != nil {
2663	return nil, fmt.Errorf("console: stored signing key: %v", err)
2664	}
2665	ec, ok := key.(*ecdsa.PrivateKey)
2666	if !ok {
2667	return nil, fmt.Errorf("console: stored signing key is not ECDSA")
2668	}
2669	return ec, nil
2670	}
2671	if k, err := load(); err == nil {
2672	return k, nil
2673	} else if err != sql.ErrNoRows {
2674	return nil, err
2675	}
2676	priv, err := ecdsa.GenerateKey(elliptic.P256(), rand.Reader)
2677	if err != nil {
2678	return nil, err
2679	}
2680	der, err := x509.MarshalPKCS8PrivateKey(priv)
2681	if err != nil {
2682	return nil, err
2683	}
2684	// INSERT OR IGNORE + re-load: if a racing opener won, everyone converges
2685	// on the row that stuck.
2686	if _, err := s.db.Exec(`INSERT OR IGNORE INTO keys (name, pkcs8, created_at) VALUES ('keymail-sign', ?, ?)`,
2687	base64.StdEncoding.EncodeToString(der), time.Now().Unix()); err != nil {
2688	return nil, err
2689	}
2690	return load()
2691	}
2692	func (s *Store) RecordActivity(accountID int64, app, address string, activity Activity, now time.Time) (claimResult0 error) {
2693	return s.RecordActivityContext(context.Background(), accountID, app, address, activity, now)
2694	}
2695	func (s *Store) RecordActivityContext(claimContext context.Context, accountID int64, app, address string, activity Activity, now time.Time) (claimResult0 error) {
2696	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.RecordActivity", func(claimView *Store) error {
2697	claimResult0 = claimView.claimRawRecordActivity(accountID, app, address, activity, now)
2698	return claimResult0
2699	})
2700	return
2701	}
2702	func (s *Store) RecordEvent(accountID int64, app, address, action, detail string, now time.Time) (claimResult0 error) {
2703	return s.RecordEventContext(context.Background(), accountID, app, address, action, detail, now)
2704	}
2705	func (s *Store) RecordEventContext(claimContext context.Context, accountID int64, app, address, action, detail string, now time.Time) (claimResult0 error) {
2706	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.RecordEvent", func(claimView *Store) error {
2707	claimResult0 = claimView.claimRawRecordEvent(accountID, app, address, action, detail, now)
2708	return claimResult0
2709	})
2710	return
2711	}
2712	func (s *Store) SetAppBuildProbe(accountID int64, app, path string) (claimResult0 error) {
2713	return s.SetAppBuildProbeContext(context.Background(), accountID, app, path)
2714	}
2715	func (s *Store) SetAppBuildProbeContext(claimContext context.Context, accountID int64, app, path string) (claimResult0 error) {
2716	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppBuildProbe", func(claimView *Store) error {
2717	claimResult0 = claimView.claimRawSetAppBuildProbe(accountID, app, path)
2718	return claimResult0
2719	})
2720	return
2721	}
2722	func (s *Store) SetAppControlPlane(accountID int64, app string, on bool) (claimResult0 error) {
2723	return s.SetAppControlPlaneContext(context.Background(), accountID, app, on)
2724	}
2725	func (s *Store) SetAppControlPlaneContext(claimContext context.Context, accountID int64, app string, on bool) (claimResult0 error) {
2726	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppControlPlane", func(claimView *Store) error {
2727	claimResult0 = claimView.claimRawSetAppControlPlane(accountID, app, on)
2728	return claimResult0
2729	})
2730	return
2731	}
2732	func (s *Store) SetAppPlacement(accountID int64, app, target string) (claimResult0 error) {
2733	return s.SetAppPlacementContext(context.Background(), accountID, app, target)
2734	}
2735	func (s *Store) SetAppPlacementContext(claimContext context.Context, accountID int64, app, target string) (claimResult0 error) {
2736	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.SetAppPlacement", func(claimView *Store) error {
2737	claimResult0 = claimView.claimRawSetAppPlacement(accountID, app, target)
2738	return claimResult0
2739	})
2740	return
2741	}
2742	func (s *Store) TrashAppRow(accountID int64, app string, now time.Time) (claimResult0 error) {
2743	return s.TrashAppRowContext(context.Background(), accountID, app, now)
2744	}
2745	func (s *Store) TrashAppRowContext(claimContext context.Context, accountID int64, app string, now time.Time) (claimResult0 error) {
2746	claimResult0 = s.withAutoAppSQL(claimContext, accountID, app, "sql.TrashAppRow", func(claimView *Store) error {
2747	claimResult0 = claimView.claimRawTrashAppRow(accountID, app, now)
2748	return claimResult0
2749	})
2750	return
2751	}
2752