g1t/services/projects/migrations/0008_pins.sql
| 1 | -- What each person keeps at hand in a workspace: the projects they pinned, |
| 2 | -- in the order they put them, and the ones they opened last. Keyed by the |
| 3 | -- project's id, so a rename or a transfer keeps them; the workspace is |
| 4 | -- the project's own, read through `projects`. |
| 5 | |
| 6 | CREATE TABLE pins ( |
| 7 | user_id TEXT NOT NULL, |
| 8 | project_id TEXT NOT NULL, |
| 9 | -- 0 first, within the person's pins in the project's workspace. |
| 10 | position INTEGER NOT NULL, |
| 11 | pinned_at TEXT NOT NULL, |
| 12 | PRIMARY KEY (user_id, project_id) |
| 13 | ); |
| 14 | CREATE INDEX pins_by_project ON pins (project_id); |
| 15 | |
| 16 | -- The last time each person opened each project: kept for their latest |
| 17 | -- few, written after the page is sent. |
| 18 | CREATE TABLE visits ( |
| 19 | user_id TEXT NOT NULL, |
| 20 | project_id TEXT NOT NULL, |
| 21 | visited_at TEXT NOT NULL, |
| 22 | PRIMARY KEY (user_id, project_id) |
| 23 | ); |
| 24 | CREATE INDEX visits_by_user ON visits (user_id, visited_at DESC); |
| 25 | CREATE INDEX visits_by_project ON visits (project_id); |