enzo/init_db.sql at main · HectorGitt/enzo · 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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UserProfile Table
CREATE TABLE IF NOT EXISTS "UserProfile" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    location TEXT,
    bio TEXT,
    title TEXT,
    "portfolioRepo" TEXT,
    "connectedProviders" TEXT, -- JSON string
    "lastSyncLog" TEXT, -- JSON string
    "resumeConfig" TEXT, -- JSON string
    "bioVariations" TEXT, -- JSON string
    "createdAt" TIMESTAMP NOT NULL DEFAULT NOW(),
    "updatedAt" TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Experience Table
CREATE TABLE IF NOT EXISTS "Experience" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role TEXT NOT NULL,
    company TEXT NOT NULL,
    "startDate" TEXT NOT NULL,
    "endDate" TEXT,
    current BOOLEAN DEFAULT FALSE,
    description TEXT NOT NULL,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE,
    wins TEXT -- JSON string
);

-- Education Table
CREATE TABLE IF NOT EXISTS "Education" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    degree TEXT NOT NULL,
    school TEXT NOT NULL,
    "graduationDate" TEXT NOT NULL,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- Skill Table
CREATE TABLE IF NOT EXISTS "Skill" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    level INTEGER NOT NULL,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- Publication Table
CREATE TABLE IF NOT EXISTS "Publication" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT NOT NULL,
    publisher TEXT NOT NULL,
    date TEXT NOT NULL,
    link TEXT NOT NULL,
    type TEXT NOT NULL,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- SpeakingEngagement Table
CREATE TABLE IF NOT EXISTS "SpeakingEngagement" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT NOT NULL,
    event TEXT NOT NULL,
    date TEXT NOT NULL,
    link TEXT,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- Win Table
CREATE TABLE IF NOT EXISTS "Win" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT NOT NULL,
    source TEXT NOT NULL,
    "rawContent" TEXT NOT NULL,
    summary TEXT NOT NULL,
    date TEXT NOT NULL,
    tags TEXT, -- JSON string
    status TEXT NOT NULL,
    "showOnResume" BOOLEAN DEFAULT FALSE,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- RawActivity Table
CREATE TABLE IF NOT EXISTS "RawActivity" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source TEXT NOT NULL,
    "externalId" TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    "metadataJson" TEXT,
    date TEXT NOT NULL,
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);

-- ResumeTemplate Table
CREATE TABLE IF NOT EXISTS "ResumeTemplate" (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    filename TEXT NOT NULL,
    "uploadDate" TIMESTAMP NOT NULL DEFAULT NOW(),
    "userId" UUID NOT NULL REFERENCES "UserProfile"(id) ON DELETE CASCADE
);