BacklogAI/db_root_backup.sql at main · winitramesh2/BacklogAI · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "users" (
    "id" CHAR(36) NOT NULL  PRIMARY KEY,
    "email" VARCHAR(255) NOT NULL UNIQUE,
    "full_name" VARCHAR(255),
    "picture" VARCHAR(1024),
    "auth_provider" VARCHAR(50) NOT NULL  DEFAULT 'google',
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "projects" (
    "id" CHAR(36) NOT NULL  PRIMARY KEY,
    "name" VARCHAR(255) NOT NULL,
    "description" TEXT,
    "weight_user_value" REAL NOT NULL  DEFAULT 0.2,
    "weight_commercial_impact" REAL NOT NULL  DEFAULT 0.2,
    "weight_strategic_horizon" REAL NOT NULL  DEFAULT 0.2,
    "weight_competitive_positioning" REAL NOT NULL  DEFAULT 0.2,
    "weight_technical_reality" REAL NOT NULL  DEFAULT 0.2,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "owner_id" CHAR(36) NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "backlog_items" (
    "id" CHAR(36) NOT NULL  PRIMARY KEY,
    "type" VARCHAR(5) NOT NULL  DEFAULT 'story' /* EPIC: epic\nSTORY: story\nTASK: task */,
    "status" VARCHAR(11) NOT NULL  DEFAULT 'draft' /* DRAFT: draft\nAPPROVED: approved\nSYNCED: synced\nIN_PROGRESS: in_progress\nDONE: done */,
    "title" VARCHAR(255) NOT NULL,
    "description" TEXT NOT NULL,
    "acceptance_criteria" JSON NOT NULL,
    "priority_score" REAL NOT NULL  DEFAULT 0,
    "score_user_value" REAL NOT NULL  DEFAULT 0,
    "score_commercial_impact" REAL NOT NULL  DEFAULT 0,
    "score_strategic_horizon" REAL NOT NULL  DEFAULT 0,
    "score_competitive_positioning" REAL NOT NULL  DEFAULT 0,
    "score_technical_reality" REAL NOT NULL  DEFAULT 0,
    "jira_key" VARCHAR(50),
    "jira_url" VARCHAR(512),
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "parent_id" CHAR(36) REFERENCES "backlog_items" ("id") ON DELETE CASCADE,
    "project_id" CHAR(36) NOT NULL REFERENCES "projects" ("id") ON DELETE CASCADE
);
COMMIT;