Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
183 lines (151 loc) · 7.04 KB
/
Copy pathschema.sql
File metadata and controls
183 lines (151 loc) · 7.04 KB
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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
-- ChatGPT Toolbox Enhanced - Database Schema
-- PostgreSQL 15+
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- =============================================================================
-- USERS TABLE
-- =============================================================================
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
CREATE INDEX idx_users_email ON users(email);
-- =============================================================================
-- FOLDERS TABLE (Hierarchical Structure)
-- =============================================================================
CREATE TABLE folders (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
parent_id UUID REFERENCES folders(id) ON DELETE CASCADE,
is_pinned BOOLEAN DEFAULT FALSE,
position INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT name_not_empty CHECK (LENGTH(TRIM(name)) > 0)
);
CREATE INDEX idx_folders_user_id ON folders(user_id);
CREATE INDEX idx_folders_parent_id ON folders(parent_id);
CREATE INDEX idx_folders_is_pinned ON folders(is_pinned);
CREATE INDEX idx_folders_position ON folders(position);
-- =============================================================================
-- PROMPTS TABLE
-- =============================================================================
CREATE TABLE prompts (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
category VARCHAR(100),
is_favorite BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT title_not_empty CHECK (LENGTH(TRIM(title)) > 0),
CONSTRAINT content_not_empty CHECK (LENGTH(TRIM(content)) > 0)
);
CREATE INDEX idx_prompts_user_id ON prompts(user_id);
CREATE INDEX idx_prompts_category ON prompts(category);
CREATE INDEX idx_prompts_is_favorite ON prompts(is_favorite);
-- Full-text search index for prompts
CREATE INDEX idx_prompts_search ON prompts
USING gin(to_tsvector('english', title || ' ' || content));
-- =============================================================================
-- CHAINS TABLE
-- =============================================================================
CREATE TABLE chains (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT name_not_empty CHECK (LENGTH(TRIM(name)) > 0)
);
CREATE INDEX idx_chains_user_id ON chains(user_id);
-- =============================================================================
-- CHAIN STEPS TABLE
-- =============================================================================
CREATE TABLE chain_steps (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
chain_id UUID NOT NULL REFERENCES chains(id) ON DELETE CASCADE,
prompt_id UUID REFERENCES prompts(id) ON DELETE SET NULL,
prompt_text TEXT NOT NULL,
position INTEGER NOT NULL,
output_variable VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT prompt_text_not_empty CHECK (LENGTH(TRIM(prompt_text)) > 0),
CONSTRAINT position_non_negative CHECK (position >= 0)
);
CREATE INDEX idx_chain_steps_chain_id ON chain_steps(chain_id);
CREATE INDEX idx_chain_steps_position ON chain_steps(position);
CREATE UNIQUE INDEX idx_chain_steps_chain_position ON chain_steps(chain_id, position);
-- =============================================================================
-- CONVERSATIONS TABLE (ChatGPT Conversation Tracking)
-- =============================================================================
CREATE TABLE conversations (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
folder_id UUID REFERENCES folders(id) ON DELETE SET NULL,
chatgpt_id VARCHAR(255),
title VARCHAR(500),
is_pinned BOOLEAN DEFAULT FALSE,
last_synced_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_conversations_user_id ON conversations(user_id);
CREATE INDEX idx_conversations_folder_id ON conversations(folder_id);
CREATE INDEX idx_conversations_chatgpt_id ON conversations(chatgpt_id);
CREATE INDEX idx_conversations_is_pinned ON conversations(is_pinned);
-- =============================================================================
-- BOOKMARKS TABLE (Saved Messages)
-- =============================================================================
CREATE TABLE bookmarks (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
message_text TEXT NOT NULL,
message_index INTEGER,
created_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT message_text_not_empty CHECK (LENGTH(TRIM(message_text)) > 0)
);
CREATE INDEX idx_bookmarks_user_id ON bookmarks(user_id);
CREATE INDEX idx_bookmarks_conversation_id ON bookmarks(conversation_id);
-- =============================================================================
-- TRIGGERS FOR UPDATED_AT
-- =============================================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_folders_updated_at
BEFORE UPDATE ON folders
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_prompts_updated_at
BEFORE UPDATE ON prompts
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_chains_updated_at
BEFORE UPDATE ON chains
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_conversations_updated_at
BEFORE UPDATE ON conversations
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- =============================================================================
-- SAMPLE SEED DATA (Optional - for development)
-- =============================================================================
-- Uncomment to add sample user (password: Test1234)
-- INSERT INTO users (email, password_hash) VALUES
-- ('test@example.com', '$2b$10$X9h9GqYvYxJ7cP1M1X9h9eZ.9h9GqYvYxJ7cP1M1X9h9eZ.9h9GqY');
-- Add sample prompt categories
-- This helps with frontend autocomplete
COMMENT ON COLUMN prompts.category IS 'Suggested categories: Programming, Writing, Education, Business, Creative, Research, Translation, Productivity, Marketing, Data Analysis';