| 1 | CREATE TABLE teams ( |
| 2 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 3 | sqid TEXT NOT NULL UNIQUE CHECK(length(sqid) BETWEEN 1 AND 64 AND sqid NOT GLOB '*[^a-zA-Z0-9]*'), |
| 4 | label TEXT NOT NULL UNIQUE CHECK(length(label) BETWEEN 1 AND 63 AND label NOT GLOB '*[^a-z0-9-]*' AND substr(label,1,1) != '-' AND substr(label,-1,1) != '-'), |
| 5 | sealed_name BLOB NOT NULL CHECK(typeof(sealed_name) = 'blob' AND length(sealed_name) BETWEEN 29 AND 284), |
| 6 | created_at INTEGER NOT NULL CHECK(created_at > 0), |
| 7 | roster_epoch INTEGER NOT NULL DEFAULT 1 CHECK(typeof(roster_epoch) = 'integer' AND roster_epoch > 0) |
| 8 | ); |
| 9 | CREATE TABLE team_instances ( |
| 10 | team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE RESTRICT, |
| 11 | app TEXT NOT NULL, |
| 12 | origin TEXT NOT NULL UNIQUE, |
| 13 | consumer_id TEXT NOT NULL UNIQUE, |
| 14 | state TEXT NOT NULL CHECK(state IN ('pending', 'live', 'disabled')), |
| 15 | PRIMARY KEY(team_id, app) |
| 16 | ); |
| 17 | CREATE TABLE memberships ( |
| 18 | team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE RESTRICT, |
| 19 | person_ref TEXT NOT NULL CHECK(length(person_ref) = 29 AND substr(person_ref,1,3) = 'pr_' AND substr(person_ref,4) NOT GLOB '*[^a-z2-7]*'), |
| 20 | role TEXT NOT NULL CHECK(role IN ('owner', 'member')), |
| 21 | PRIMARY KEY(team_id, person_ref) |
| 22 | ); |
| 23 | CREATE INDEX memberships_person ON memberships(person_ref, team_id); |
| 24 | |
amadan