Research Labs

2026-09-17 20:36 UTCopen field data · live

docs/schema.sql

Snapshot 2026.09.17-a2894de (current) · 94 lines · 3,270 bytes · tree · plain text

SHA-256 a0979cb34aff4cfc2c496c916b18f839b34e0c6cb01e8f337adfe4c0f9bdb9c6
1-- Districthive Research Labs · schema v0
2CREATE TABLE instruments (
3 id INT AUTO_INCREMENT PRIMARY KEY,
4 code VARCHAR(40) UNIQUE NOT NULL,
5 name VARCHAR(120) NOT NULL,
6 product VARCHAR(160) NOT NULL, -- instrument source: named product
7 vendor_link VARCHAR(200) DEFAULT '',
8 data_path VARCHAR(200) DEFAULT '', -- how data reaches us (API/LAN/SD...)
9 zone VARCHAR(80) DEFAULT '', -- site zone / control site
10 cadence_min INT DEFAULT 60,
11 status ENUM('ok','late','silent','fault','pending') DEFAULT 'pending',
12 is_public TINYINT DEFAULT 1,
13 last_alert_at DATETIME NULL
14) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
15 
16CREATE TABLE streams (
17 id INT AUTO_INCREMENT PRIMARY KEY,
18 instrument_id INT NOT NULL,
19 code VARCHAR(48) UNIQUE NOT NULL,
20 label VARCHAR(120) NOT NULL,
21 unit VARCHAR(24) DEFAULT '',
22 page VARCHAR(40) NOT NULL, -- which page accounts for this stream
23 mode ENUM('placeholder','live') DEFAULT 'placeholder',
24 visibility ENUM('public','keyed','hidden') DEFAULT 'public',
25 series_slot TINYINT DEFAULT 1, -- fixed categorical colour slot
26 embargo_note VARCHAR(200) DEFAULT '',
27 FOREIGN KEY (instrument_id) REFERENCES instruments(id)
28) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
29 
30CREATE TABLE readings (
31 id BIGINT AUTO_INCREMENT PRIMARY KEY,
32 stream_id INT NOT NULL,
33 ts DATETIME NOT NULL,
34 value DOUBLE NOT NULL,
35 qc_flag TINYINT DEFAULT 0,
36 is_placeholder TINYINT DEFAULT 0,
37 INDEX ix_sr (stream_id, ts),
38 FOREIGN KEY (stream_id) REFERENCES streams(id)
39) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
40 
41CREATE TABLE files_raw (
42 id BIGINT AUTO_INCREMENT PRIMARY KEY,
43 stream_id INT NULL,
44 path VARCHAR(255) NOT NULL,
45 sha256 CHAR(64) NOT NULL,
46 bytes BIGINT DEFAULT 0,
47 pulled_at DATETIME NOT NULL,
48 source_url VARCHAR(255) DEFAULT '',
49 is_placeholder TINYINT DEFAULT 0
50) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
51 
52CREATE TABLE events (
53 id BIGINT AUTO_INCREMENT PRIMARY KEY,
54 instrument_id INT NULL,
55 ts DATETIME NOT NULL,
56 class ENUM('fault','gap','recovery','correction','audit','info') NOT NULL,
57 detail VARCHAR(400) NOT NULL,
58 is_public TINYINT DEFAULT 1
59) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
60 
61CREATE TABLE activity_log (
62 id BIGINT AUTO_INCREMENT PRIMARY KEY,
63 ts DATETIME NOT NULL,
64 actor ENUM('cron','admin','system','api','visitor') NOT NULL,
65 action VARCHAR(80) NOT NULL,
66 detail VARCHAR(400) DEFAULT ''
67) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
68 
69CREATE TABLE api_keys (
70 id INT AUTO_INCREMENT PRIMARY KEY,
71 key_hash CHAR(64) NOT NULL, -- sha256 of the key
72 label VARCHAR(80) NOT NULL,
73 active TINYINT DEFAULT 1,
74 created_at DATETIME NOT NULL,
75 last_used_at DATETIME NULL
76) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
77 
78CREATE TABLE annotations (
79 id INT AUTO_INCREMENT PRIMARY KEY,
80 ts DATETIME NOT NULL,
81 scope VARCHAR(40) DEFAULT 'site', -- site | construction | page code
82 text VARCHAR(300) NOT NULL
83) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
84 
85CREATE TABLE media (
86 id BIGINT AUTO_INCREMENT PRIMARY KEY,
87 camera_code VARCHAR(40) NOT NULL,
88 ts DATETIME NOT NULL,
89 path VARCHAR(255) NOT NULL,
90 kind ENUM('still','timelapse','live') DEFAULT 'still',
91 is_public TINYINT DEFAULT 1,
92 is_placeholder TINYINT DEFAULT 0
93) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
94 

Lines can be cited as docs/schema.sql L120-L140, platform code 2026.09.17-a2894de; add #L120-L140 to this page's address to highlight them. Source shown for scrutiny of the research platform. All rights reserved. Not offered as installable software.