Open Source Team Metrics based on PRs
vorcigernix.tngl.sh
1-- Enable Foreign Key constraints
2PRAGMA foreign_keys = ON;
3
4-- Schema version tracking table
5CREATE TABLE IF NOT EXISTS schema_migrations (
6 version INTEGER PRIMARY KEY,
7 applied_at TEXT NOT NULL DEFAULT (datetime('now'))
8);
9
10-- Users table
11CREATE TABLE IF NOT EXISTS users (
12 id TEXT PRIMARY KEY,
13 name TEXT,
14 email TEXT UNIQUE,
15 image TEXT,
16 created_at TEXT NOT NULL DEFAULT (datetime('now')),
17 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
18);
19
20-- Organizations table (GitHub organizations)
21CREATE TABLE IF NOT EXISTS organizations (
22 id INTEGER PRIMARY KEY,
23 github_id INTEGER UNIQUE,
24 name TEXT NOT NULL,
25 avatar_url TEXT,
26 installation_id INTEGER NULL,
27 production_access BOOLEAN NOT NULL DEFAULT 0,
28 created_at TEXT NOT NULL DEFAULT (datetime('now')),
29 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
30);
31
32-- User-Organization relationship (many-to-many)
33CREATE TABLE IF NOT EXISTS user_organizations (
34 user_id TEXT NOT NULL,
35 organization_id INTEGER NOT NULL,
36 role TEXT NOT NULL DEFAULT 'member', -- 'member', 'admin', 'owner'
37 created_at TEXT NOT NULL DEFAULT (datetime('now')),
38 PRIMARY KEY (user_id, organization_id),
39 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
40 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
41);
42
43-- Repositories table
44CREATE TABLE IF NOT EXISTS repositories (
45 id INTEGER PRIMARY KEY,
46 github_id INTEGER UNIQUE,
47 organization_id INTEGER,
48 name TEXT NOT NULL,
49 full_name TEXT NOT NULL,
50 description TEXT,
51 private BOOLEAN NOT NULL DEFAULT 0,
52 is_tracked BOOLEAN NOT NULL DEFAULT 0,
53 created_at TEXT NOT NULL DEFAULT (datetime('now')),
54 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
55 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
56);
57
58-- Categories for PR classification
59CREATE TABLE IF NOT EXISTS categories (
60 id INTEGER PRIMARY KEY,
61 organization_id INTEGER,
62 name TEXT NOT NULL,
63 description TEXT,
64 color TEXT,
65 is_default BOOLEAN DEFAULT 0,
66 created_at TEXT NOT NULL DEFAULT (datetime('now')),
67 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
68 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
69);
70
71-- Pull Requests table
72CREATE TABLE IF NOT EXISTS pull_requests (
73 id INTEGER PRIMARY KEY,
74 github_id INTEGER NOT NULL,
75 repository_id INTEGER NOT NULL,
76 number INTEGER NOT NULL,
77 title TEXT NOT NULL,
78 description TEXT,
79 author_id TEXT,
80 state TEXT NOT NULL, -- 'open', 'closed', 'merged'
81 created_at TEXT NOT NULL,
82 updated_at TEXT NOT NULL,
83 closed_at TEXT,
84 merged_at TEXT,
85 draft BOOLEAN NOT NULL DEFAULT 0,
86 additions INTEGER,
87 deletions INTEGER,
88 changed_files INTEGER,
89 category_id INTEGER,
90 category_confidence REAL,
91 embedding_id INTEGER,
92 FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
93 FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL,
94 FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
95 UNIQUE (repository_id, number)
96);
97
98-- PR Reviews table
99CREATE TABLE IF NOT EXISTS pr_reviews (
100 id INTEGER PRIMARY KEY,
101 github_id INTEGER NOT NULL,
102 pull_request_id INTEGER NOT NULL,
103 reviewer_id TEXT,
104 state TEXT NOT NULL, -- 'approved', 'changes_requested', 'commented', 'dismissed'
105 submitted_at TEXT NOT NULL,
106 FOREIGN KEY (pull_request_id) REFERENCES pull_requests(id) ON DELETE CASCADE,
107 FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL
108);
109
110-- Settings table for user/org preferences
111CREATE TABLE IF NOT EXISTS settings (
112 id INTEGER PRIMARY KEY,
113 user_id TEXT,
114 organization_id INTEGER,
115 key TEXT NOT NULL,
116 value TEXT,
117 created_at TEXT NOT NULL DEFAULT (datetime('now')),
118 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
119 UNIQUE (user_id, organization_id, key),
120 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
121 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
122 CHECK ((user_id IS NULL) != (organization_id IS NULL)) -- Exactly one of user_id or organization_id must be NULL
123);
124
125-- Recommendations table
126CREATE TABLE IF NOT EXISTS recommendations (
127 id INTEGER PRIMARY KEY,
128 organization_id INTEGER NOT NULL,
129 title TEXT NOT NULL,
130 description TEXT NOT NULL,
131 recommendation_type TEXT NOT NULL, -- 'process', 'technical', 'workflow', etc.
132 status TEXT NOT NULL DEFAULT 'open', -- 'open', 'accepted', 'rejected', 'implemented'
133 priority INTEGER NOT NULL DEFAULT 1, -- 1-5
134 created_at TEXT NOT NULL DEFAULT (datetime('now')),
135 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
136 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
137);
138
139-- Table for vector embeddings (when enabled)
140CREATE TABLE IF NOT EXISTS embeddings (
141 id INTEGER PRIMARY KEY,
142 source_type TEXT NOT NULL, -- 'pull_request', 'category', etc.
143 source_id INTEGER NOT NULL,
144 vector BLOB, -- Will store the vector embedding
145 created_at TEXT NOT NULL DEFAULT (datetime('now')),
146 UNIQUE (source_type, source_id)
147);
148
149-- Create initial schema version
150INSERT INTO schema_migrations (version) VALUES (1);