usaa.ma
A deployable markdown editor that connects with your self hosted files and lets you edit in a beautiful interface
1-- Users table
2CREATE TABLE IF NOT EXISTS users (
3 id INTEGER PRIMARY KEY AUTOINCREMENT,
4 github_id INTEGER UNIQUE,
5 username TEXT NOT NULL,
6 email TEXT,
7 avatar_url TEXT,
8 last_repo TEXT,
9 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
10 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
11);
12
13-- Sessions/Tokens (encrypted access tokens)
14CREATE TABLE IF NOT EXISTS auth_tokens (
15 id INTEGER PRIMARY KEY AUTOINCREMENT,
16 user_id INTEGER NOT NULL,
17 provider TEXT NOT NULL,
18 access_token TEXT NOT NULL,
19 refresh_token TEXT,
20 expires_at DATETIME,
21 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
22 UNIQUE(user_id, provider),
23 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
24);
25
26-- Branch state tracking
27CREATE TABLE IF NOT EXISTS branch_states (
28 id INTEGER PRIMARY KEY AUTOINCREMENT,
29 user_id INTEGER NOT NULL,
30 repo_full_name TEXT NOT NULL,
31 branch_name TEXT NOT NULL,
32 base_branch TEXT DEFAULT 'main',
33 last_push_at DATETIME NOT NULL,
34 has_uncommitted_changes BOOLEAN DEFAULT FALSE,
35 file_paths TEXT,
36 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
37 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
38 UNIQUE(user_id, repo_full_name),
39 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
40);
41
42-- Work-in-progress content (auto-save buffer)
43CREATE TABLE IF NOT EXISTS draft_content (
44 id INTEGER PRIMARY KEY AUTOINCREMENT,
45 user_id INTEGER NOT NULL,
46 repo_full_name TEXT NOT NULL,
47 file_path TEXT NOT NULL,
48 content TEXT NOT NULL,
49 last_saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
50 UNIQUE(user_id, repo_full_name, file_path),
51 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
52);
53
54-- Indexes for performance
55CREATE INDEX IF NOT EXISTS idx_auth_tokens_user_provider ON auth_tokens(user_id, provider);
56CREATE INDEX IF NOT EXISTS idx_branch_states_user_repo ON branch_states(user_id, repo_full_name);
57CREATE INDEX IF NOT EXISTS idx_draft_content_user_repo_file ON draft_content(user_id, repo_full_name, file_path);