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
107CREATE TABLE groups (
108 id INTEGER PRIMARY KEY,
109 owner_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
110 name TEXT NOT NULL,
111 created_at INTEGER NOT NULL,
112 UNIQUE (owner_user_id, name)
113) STRICT;
114
115CREATE TABLE group_members (
116 group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
117 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
118 added_at INTEGER NOT NULL,
119 PRIMARY KEY (group_id, user_id)
120) STRICT, WITHOUT ROWID;
121
122-- You share *yourself*, not a phone: there is no per-device dimension here.
123CREATE TABLE shares (
124 id INTEGER PRIMARY KEY,
125 owner_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
126 -- Exactly one of these two.
127 viewer_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
128 viewer_group_id INTEGER REFERENCES groups(id) ON DELETE CASCADE,
129 trail_visible INTEGER NOT NULL DEFAULT 1 CHECK (trail_visible IN (0, 1)),
130 -- Metres to round the position to before showing it. 0 = exact.
131 precision_m INTEGER NOT NULL DEFAULT 0,
132 -- NULL means no expiry. Enforced at query time, never by a background job,
133 -- so a stalled job can never leak a position.
134 expires_at INTEGER,
135 revoked_at INTEGER,
136 created_at INTEGER NOT NULL,
137
138 CHECK ((viewer_user_id IS NULL) <> (viewer_group_id IS NULL)),
139 CHECK (owner_user_id <> viewer_user_id)
140) STRICT;
141
142CREATE INDEX shares_owner ON shares(owner_user_id);
143CREATE INDEX shares_viewer_user ON shares(viewer_user_id);
144CREATE INDEX shares_viewer_group ON shares(viewer_group_id);
145
146-- OSM tile proxy cache metadata. The bytes live on the filesystem at
147-- {cache_dir}/tiles/{z}/{x}/{y}.png; this table is the index and the accounting
148-- that makes max_cache_bytes enforceable.
149CREATE TABLE tiles (
150 z INTEGER NOT NULL,
151 x INTEGER NOT NULL,
152 y INTEGER NOT NULL,
153 etag TEXT,
154 last_modified TEXT,
155 fetched_at INTEGER NOT NULL,
156 expires_at INTEGER NOT NULL,
157 bytes INTEGER NOT NULL,
158 last_access INTEGER NOT NULL,
159 PRIMARY KEY (z, x, y)
160) STRICT, WITHOUT ROWID;
161
162-- Drives least-recently-used eviction.
163CREATE INDEX tiles_last_access ON tiles(last_access);
164
165CREATE TABLE audit_log (
166 id INTEGER PRIMARY KEY,
167 at INTEGER NOT NULL,
168 -- Nullable: failed logins have no authenticated user yet.
169 user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
170 action TEXT NOT NULL,
171 detail TEXT NOT NULL DEFAULT '',
172 src_ip TEXT
173) STRICT;
174
175CREATE INDEX audit_log_at ON audit_log(at);
176
177CREATE TABLE settings (
178 key TEXT PRIMARY KEY,
179 value TEXT NOT NULL
180) STRICT, WITHOUT ROWID;
181