opentracker

opentracker/crates/otserver/migrations/0001_init.sql
0001_init.sql
⎇
Raw
1-- opentracker initial schema.
2--
3-- Every table is STRICT: SQLite's default type affinity would happily store the
4-- string 'north' in an INTEGER latitude column, and this database is written to
5-- by a hot path that must never be the place a type error is discovered.
6--
7-- The central decision here: **the position stream belongs to the account.** A
8-- token is a credential that authorises writing into its owner's stream. It is
9-- never an identity — it does not appear in a point's key, in a share, or on the
10-- map. One person is one dot, regardless of which pocket the phone is in.
11--
12-- Coordinates are integers (degrees × 1e7) end to end — protocol, database, JSON,
13-- UI — so there is no float-formatting drift anywhere in the system.
14
15CREATE TABLE users (
16 id INTEGER PRIMARY KEY,
17 -- NOCASE so 'Marc' and 'marc' are the same account, which is what users
18 -- assume, and which closes a whole class of impersonation confusion.
19 username TEXT NOT NULL COLLATE NOCASE UNIQUE,
20 -- argon2id PHC string; carries its own parameters so the policy can change
21 -- without invalidating existing hashes.
22 pw_hash TEXT NOT NULL,
23 display_name TEXT NOT NULL,
24 is_admin INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0, 1)),
25 -- Set rather than deleting the row: points and shares reference it.
26 disabled_at INTEGER,
27 created_at INTEGER NOT NULL,
28 -- Sessions and tokens issued before this are treated as revoked, which is
29 -- how "changing my password logs everything else out" is enforced without
30 -- having to enumerate them.
31 pw_changed_at INTEGER NOT NULL
32) STRICT;
33
34-- One row per login. Purely a credential plus per-phone telemetry.
35CREATE TABLE tokens (
36 -- The u64 that travels in every datagram header. Random, not sequential:
37 -- guessing one must not be easier than guessing a key.
38 token_id INTEGER PRIMARY KEY,
39 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
40 -- The 32-byte token secret, wrapped with the server key from OT_SECRET_KEY
41 -- (AAD = token_id), so a stolen .db alone yields no working keys.
42 key_wrapped BLOB NOT NULL,
43 name TEXT NOT NULL,
44 platform TEXT NOT NULL DEFAULT '',
45 app_version INTEGER,
46 os_api_level INTEGER,
47 config_version INTEGER NOT NULL DEFAULT 1,
48 config_json TEXT NOT NULL DEFAULT '{}',
49 last_seen_at INTEGER,
50 last_src_ip TEXT,
51 last_src_port INTEGER,
52 last_transport TEXT,
53 created_at INTEGER NOT NULL,
54 created_ip TEXT,
55 revoked_at INTEGER
56) STRICT;
57
58CREATE INDEX tokens_user ON tokens(user_id);
59-- Drives the 30-day staleness sweep.
60CREATE INDEX tokens_last_seen ON tokens(last_seen_at);
61
62CREATE TABLE points (
63 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
64 -- Unix seconds from the client's wall clock, stored as sent.
65 ts INTEGER NOT NULL,
66 lat INTEGER NOT NULL,
67 lon INTEGER NOT NULL,
68 acc_dm INTEGER,
69 alt_m INTEGER,
70 spd_cms INTEGER,
71 brg_cdeg INTEGER,
72 bat_pct INTEGER,
73 flags INTEGER NOT NULL DEFAULT 0,
74 recv_at INTEGER NOT NULL,
75 -- Provenance only: nullable, never in a key, never read by the UI. It exists
76 -- so "which phone sent this?" is answerable while debugging. Deliberately
77 -- NOT a foreign key with CASCADE — a token being deleted by the staleness
78 -- sweep must not take history with it.
79 src_token_id INTEGER,
80
81 -- The dedup that makes retries and replays both harmless. A replayed
82 -- datagram carries a ts that already exists and collapses into the row
83 -- already there, which is why this schema needs no replay window.
84 PRIMARY KEY (user_id, ts)
85) STRICT, WITHOUT ROWID;
86
87-- For the retention sweep, which scans by age across all users.
88CREATE INDEX points_ts ON points(ts);
89
90-- Current position per *account*, in its own table so the retention GC can never
91-- delete the live marker.
92CREATE TABLE user_latest (
93 user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
94 ts INTEGER NOT NULL,
95 lat INTEGER NOT NULL,
96 lon INTEGER NOT NULL,
97 acc_dm INTEGER,
98 alt_m INTEGER,
99 spd_cms INTEGER,
100 brg_cdeg INTEGER,
101 bat_pct INTEGER,
102 flags INTEGER NOT NULL DEFAULT 0,
103 recv_at INTEGER NOT NULL,
104 src_token_id INTEGER
105) STRICT;
106
107-- You share *yourself*, not a phone: there is no per-device dimension here, and
108-- a share targets exactly one other account.
109CREATE TABLE shares (
110 id INTEGER PRIMARY KEY,
111 owner_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
112 viewer_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
113 trail_visible INTEGER NOT NULL DEFAULT 1 CHECK (trail_visible IN (0, 1)),
114 -- Metres to round the position to before showing it. 0 = exact.
115 precision_m INTEGER NOT NULL DEFAULT 0,
116 -- NULL means no expiry. Enforced at query time, never by a background job,
117 -- so a stalled job can never leak a position.
118 expires_at INTEGER,
119 revoked_at INTEGER,
120 created_at INTEGER NOT NULL,
121
122 CHECK (owner_user_id <> viewer_user_id)
123) STRICT;
124
125CREATE INDEX shares_owner ON shares(owner_user_id);
126CREATE INDEX shares_viewer_user ON shares(viewer_user_id);
127
128-- OSM tile proxy cache metadata. The bytes live on the filesystem at
129-- {cache_dir}/tiles/{z}/{x}/{y}.png; this table is the index and the accounting
130-- that makes max_cache_bytes enforceable.
131CREATE TABLE tiles (
132 z INTEGER NOT NULL,
133 x INTEGER NOT NULL,
134 y INTEGER NOT NULL,
135 etag TEXT,
136 last_modified TEXT,
137 fetched_at INTEGER NOT NULL,
138 expires_at INTEGER NOT NULL,
139 bytes INTEGER NOT NULL,
140 last_access INTEGER NOT NULL,
141 PRIMARY KEY (z, x, y)
142) STRICT, WITHOUT ROWID;
143
144-- Drives least-recently-used eviction.
145CREATE INDEX tiles_last_access ON tiles(last_access);
146
147CREATE TABLE audit_log (
148 id INTEGER PRIMARY KEY,
149 at INTEGER NOT NULL,
150 -- Nullable: failed logins have no authenticated user yet.
151 user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
152 action TEXT NOT NULL,
153 detail TEXT NOT NULL DEFAULT '',
154 src_ip TEXT
155) STRICT;
156
157CREATE INDEX audit_log_at ON audit_log(at);
158
159CREATE TABLE settings (
160 key TEXT PRIMARY KEY,
161 value TEXT NOT NULL
162) STRICT, WITHOUT ROWID;
163