1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

package main

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"time"

	_ "modernc.org/sqlite"
)

// Schema + migrations
// ============================================================================
//
// One SQLite file holds every submission, audio bytes included. Nothing is
// written to the filesystem under a name derived from a submission, which is
// deliberate: the access token is the only way to reach a recording, and a
// token that also named a directory would leak through backups, directory
// listings and stray `ls` output. Here it is a column, and the audio is a blob.
//
// The other consequence is that the database file *is* the inbox: one file to
// copy, inspect or delete, and deleting a row is what frees quota.
//
// baselineSchema holds the tables in their original shape; dbMigrations carries
// every change since. Each migration runs exactly once, in order, tracked by
// schema_version. Never edit an existing migration — append a new one.
const baselineSchema = `
CREATE TABLE IF NOT EXISTS schema_version (
    version    INTEGER PRIMARY KEY,
    applied_at INTEGER NOT NULL
) STRICT;

-- One row per submitted recording.
--
-- token is the capability: 256 bits from crypto/rand, and the only thing that
-- grants access to the row. It is UNIQUE (hence indexed) because every lookup
-- is by token and nothing else — there is no route that lists submissions, so
-- knowing one token reveals nothing about any other.
--
-- deleted_at marks a submission as gone without removing the row, so that a
-- token which has been used once cannot be distinguished from one that never
-- existed: both answer 404. The audio itself is cleared on delete, which is
-- what actually returns the space.
CREATE TABLE IF NOT EXISTS submission (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    token       TEXT    NOT NULL UNIQUE,
    received_at INTEGER NOT NULL,
    audio       BLOB,
    byte_size   INTEGER NOT NULL,
    mime        TEXT    NOT NULL,
    filename    TEXT    NOT NULL DEFAULT '',
    duration_ms INTEGER,
    consented   INTEGER NOT NULL DEFAULT 0,
    transcript  TEXT,
    note        TEXT    NOT NULL DEFAULT '',
    source_ip   TEXT    NOT NULL DEFAULT '',
    deleted_at  INTEGER
) STRICT;

-- Quota is the sum of byte_size over live rows, so it is asked on every
-- upload.
CREATE INDEX IF NOT EXISTS submission_live ON submission (deleted_at);
`

// dbMigrations is appended to as the schema changes.
var dbMigrations = []struct {
	version int
	sql     string
}{
	// A missing transcript has several causes and they are not
	// interchangeable: the sender withheld consent, transcription is not
	// configured, or it was attempted and failed. Only the first is visible
	// from the other columns, so a failure used to be indistinguishable from
	// a submission nobody had permission to transcribe — the reason existed
	// only in the log and in a mail that had already been sent.
	//
	// NULL means "nothing went wrong", not "no reason recorded": every row
	// that predates this column has one of the earlier states, and the review
	// page says only what it can actually tell.
	{1, `ALTER TABLE submission ADD COLUMN transcript_error TEXT`},
}

// openDB opens the database and brings the schema up to date.
//
// The pragmas travel in the DSN because the modernc driver applies those to
// every connection it opens. A post-open `db.Exec("PRAGMA …")` would only
// configure whichever pooled connection happened to run it, leaving the others
// at SQLite's defaults:
//
//   - busy_timeout: a writer waits for the lock instead of failing outright
//     with SQLITE_BUSY.
//   - journal_mode(WAL): readers do not block the writer.
func openDB(path string) (*sql.DB, error) {
	if dir := filepath.Dir(path); dir != "" {
		if err := os.MkdirAll(dir, 0o755); err != nil {
			return nil, fmt.Errorf("create db dir %q: %w", dir, err)
		}
	}
	db, err := sql.Open("sqlite",
		"file:"+path+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)")
	if err != nil {
		return nil, fmt.Errorf("open %q: %w", path, err)
	}
	if _, err := db.Exec(baselineSchema); err != nil {
		db.Close()
		return nil, fmt.Errorf("create schema in %q: %w", path, err)
	}
	if err := migrate(db); err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

// migrate applies every not-yet-applied migration, in order.
func migrate(db *sql.DB) error {
	for _, m := range dbMigrations {
		var applied int
		if err := db.QueryRow(
			`SELECT COUNT(*) FROM schema_version WHERE version = ?`, m.version,
		).Scan(&applied); err != nil {
			return fmt.Errorf("check migration %d: %w", m.version, err)
		}
		if applied > 0 {
			continue
		}
		tx, err := db.Begin()
		if err != nil {
			return fmt.Errorf("begin migration %d: %w", m.version, err)
		}
		if _, err := tx.Exec(m.sql); err != nil {
			tx.Rollback()
			return fmt.Errorf("migration %d: %w", m.version, err)
		}
		if _, err := tx.Exec(
			`INSERT INTO schema_version (version, applied_at) VALUES (?, ?)`,
			m.version, time.Now().Unix(),
		); err != nil {
			tx.Rollback()
			return fmt.Errorf("record migration %d: %w", m.version, err)
		}
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commit migration %d: %w", m.version, err)
		}
	}
	return nil
}