g1t/services/context/migrations/0001_init.sql
| 1 | -- The context hub: a catalog of what each workspace builds and runs, built |
| 2 | -- by itself from its repositories, projects, deployments and integrations, |
| 3 | -- and the text search reads besides memory. Every timestamp is RFC 3339 UTC. |
| 4 | |
| 5 | -- One thing in the catalog: a project, an app, an API, a package, a |
| 6 | -- language, an owner, an environment, an integration or a doc. Its id is a |
| 7 | -- hash of (workspace, kind, key), so rebuilding it is an upsert. |
| 8 | CREATE TABLE entities ( |
| 9 | id TEXT PRIMARY KEY, |
| 10 | workspace TEXT NOT NULL, |
| 11 | kind TEXT NOT NULL, |
| 12 | key TEXT NOT NULL, |
| 13 | name TEXT NOT NULL, |
| 14 | summary TEXT, |
| 15 | -- The project it belongs to; null for what the workspace shares. |
| 16 | project_id TEXT, |
| 17 | project TEXT, |
| 18 | repo_id TEXT, |
| 19 | private INTEGER NOT NULL DEFAULT 0, |
| 20 | -- JSON, by kind. |
| 21 | data TEXT NOT NULL DEFAULT '{}', |
| 22 | -- scan, projects, deployments or integrations. |
| 23 | source TEXT NOT NULL, |
| 24 | ref TEXT, |
| 25 | updated_at TEXT NOT NULL, |
| 26 | UNIQUE (workspace, kind, key) |
| 27 | ); |
| 28 | CREATE INDEX entities_by_project ON entities (workspace, project_id); |
| 29 | CREATE INDEX entities_by_kind ON entities (workspace, kind, name); |
| 30 | |
| 31 | -- How two entities relate. `project_id` is the project whose rebuild wrote |
| 32 | -- it, so a rebuild replaces exactly its own. |
| 33 | CREATE TABLE relations ( |
| 34 | workspace TEXT NOT NULL, |
| 35 | from_id TEXT NOT NULL, |
| 36 | kind TEXT NOT NULL, |
| 37 | to_id TEXT NOT NULL, |
| 38 | project_id TEXT, |
| 39 | updated_at TEXT NOT NULL, |
| 40 | PRIMARY KEY (from_id, kind, to_id) |
| 41 | ); |
| 42 | CREATE INDEX relations_to ON relations (to_id); |
| 43 | CREATE INDEX relations_by_project ON relations (project_id); |
| 44 | CREATE INDEX relations_by_workspace ON relations (workspace); |
| 45 | |
| 46 | -- Text search reads besides the catalog: pieces of docs, issues and pull |
| 47 | -- requests. Its id is the one in the search index. |
| 48 | CREATE TABLE items ( |
| 49 | id TEXT PRIMARY KEY, |
| 50 | workspace TEXT NOT NULL, |
| 51 | -- doc, issue or pull. |
| 52 | kind TEXT NOT NULL, |
| 53 | entity_id TEXT, |
| 54 | project_id TEXT, |
| 55 | project TEXT, |
| 56 | repo_id TEXT, |
| 57 | private INTEGER NOT NULL DEFAULT 0, |
| 58 | title TEXT NOT NULL, |
| 59 | text TEXT NOT NULL, |
| 60 | url TEXT, |
| 61 | by TEXT, |
| 62 | updated_at TEXT NOT NULL |
| 63 | ); |
| 64 | CREATE INDEX items_by_workspace ON items (workspace, kind, updated_at); |
| 65 | CREATE INDEX items_by_entity ON items (entity_id); |
| 66 | |
| 67 | -- Each file of a project read when its catalog was built: its blob, so an |
| 68 | -- unchanged file is never read again, and what it said. |
| 69 | CREATE TABLE files ( |
| 70 | project_id TEXT NOT NULL, |
| 71 | path TEXT NOT NULL, |
| 72 | hash TEXT NOT NULL, |
| 73 | -- JSON: the file's facts (src/extract.ts). |
| 74 | facts TEXT NOT NULL, |
| 75 | read_at TEXT NOT NULL, |
| 76 | PRIMARY KEY (project_id, path) |
| 77 | ); |
| 78 | |
| 79 | -- When each project's catalog was last built, and from which commit. |
| 80 | CREATE TABLE scans ( |
| 81 | project_id TEXT PRIMARY KEY, |
| 82 | workspace TEXT NOT NULL, |
| 83 | repo_id TEXT NOT NULL, |
| 84 | "commit" TEXT, |
| 85 | tests INTEGER NOT NULL DEFAULT 0, |
| 86 | scanned_at TEXT NOT NULL |
| 87 | ); |
| 88 | CREATE INDEX scans_by_workspace ON scans (workspace); |
| 89 | |
| 90 | -- A workspace's backfill: the catalog for every project and memory seeded |
| 91 | -- from docs and merged pull requests. One row per workspace, the latest. |
| 92 | CREATE TABLE backfills ( |
| 93 | workspace TEXT PRIMARY KEY, |
| 94 | status TEXT NOT NULL, |
| 95 | by TEXT NOT NULL, |
| 96 | projects INTEGER NOT NULL DEFAULT 0, |
| 97 | done INTEGER NOT NULL DEFAULT 0, |
| 98 | entities INTEGER NOT NULL DEFAULT 0, |
| 99 | candidates INTEGER NOT NULL DEFAULT 0, |
| 100 | kept INTEGER NOT NULL DEFAULT 0, |
| 101 | indexed INTEGER NOT NULL DEFAULT 0, |
| 102 | error TEXT, |
| 103 | started_at TEXT NOT NULL, |
| 104 | finished_at TEXT |
| 105 | ); |
| 106 | |
| 107 | -- What putting text in the search index used, per workspace and month. |
| 108 | CREATE TABLE usage ( |
| 109 | workspace TEXT NOT NULL, |
| 110 | month TEXT NOT NULL, |
| 111 | tokens INTEGER NOT NULL DEFAULT 0, |
| 112 | cost_micros INTEGER NOT NULL DEFAULT 0, |
| 113 | PRIMARY KEY (workspace, month) |
| 114 | ); |