| 1 | CREATE TABLE directory_operator_consumer_records ( |
| 2 | consumer_id TEXT PRIMARY KEY REFERENCES roster_consumers(consumer_id), |
| 3 | operation_id TEXT NOT NULL UNIQUE REFERENCES roster_operator_receipts(operation_id), |
| 4 | sequence INTEGER NOT NULL CHECK(typeof(sequence)='integer' AND sequence>0), |
| 5 | generation TEXT NOT NULL CHECK(length(generation)=43), |
| 6 | record_digest BLOB NOT NULL CHECK(typeof(record_digest)='blob' AND length(record_digest)=32) |
| 7 | ); |
| 8 | CREATE TABLE verification_attempts ( |
| 9 | operation_id TEXT PRIMARY KEY CHECK(length(operation_id)=43), |
| 10 | consumer_id TEXT NOT NULL REFERENCES roster_consumers(consumer_id), |
| 11 | team_id INTEGER NOT NULL REFERENCES teams(id), |
| 12 | app TEXT NOT NULL, |
| 13 | origin TEXT NOT NULL, |
| 14 | generation TEXT NOT NULL CHECK(length(generation)=43), |
| 15 | record_sequence INTEGER NOT NULL CHECK(typeof(record_sequence)='integer' AND record_sequence>0), |
| 16 | record_digest BLOB NOT NULL CHECK(typeof(record_digest)='blob' AND length(record_digest)=32), |
| 17 | expected_epoch INTEGER NOT NULL CHECK(typeof(expected_epoch)='integer' AND expected_epoch>0), |
| 18 | owner_ref TEXT NOT NULL CHECK(length(owner_ref)=29), |
| 19 | issued_at INTEGER NOT NULL CHECK(typeof(issued_at)='integer' AND issued_at>0), |
| 20 | expires_at INTEGER NOT NULL CHECK(typeof(expires_at)='integer' AND expires_at>issued_at AND expires_at-issued_at<=600), |
| 21 | authorization_digest BLOB NOT NULL CHECK(typeof(authorization_digest)='blob' AND length(authorization_digest)=32) |
| 22 | ); |
| 23 | CREATE TABLE verification_active ( |
| 24 | consumer_id TEXT PRIMARY KEY REFERENCES roster_consumers(consumer_id), |
| 25 | operation_id TEXT NOT NULL UNIQUE REFERENCES verification_attempts(operation_id) |
| 26 | ); |
| 27 | CREATE TABLE verification_outcomes ( |
| 28 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 29 | attempt_id TEXT NOT NULL REFERENCES verification_attempts(operation_id), |
| 30 | operator_operation_id TEXT NOT NULL CHECK(length(operator_operation_id)=43), |
| 31 | kind TEXT NOT NULL CHECK(kind IN ('opened','expired','activated')), |
| 32 | completed_at INTEGER NOT NULL CHECK(typeof(completed_at)='integer' AND completed_at>0), |
| 33 | UNIQUE(attempt_id,operator_operation_id,kind) |
| 34 | ); |
| 35 | |
amadan