mirror of
https://github.com/Dvorinka/Trackeep.git
synced 2026-06-03 20:12:58 +00:00
Tomas Dvorak
954a1a1080
- Replace Redis with DragonflyDB for better performance and memory efficiency - Remove redundant environment variables (POSTGRES_*, ENCRYPTION_KEY, OAUTH_SERVICE_URL) - Consolidate database configuration to use single DB_* variables - Use JWT_SECRET for both JWT tokens and encryption - Remove PORT variable redundancy, use BACKEND_PORT consistently - Clean up docker-compose configurations for dev/prod consistency - Add DragonflyDB configuration with optimized memory usage - Remove redis.conf as it's no longer needed - Update health checks to use Redis-compatible CLI for DragonflyDB - Add missing VITE_API_URL to production frontend - Fix GitHub Actions to use correct go.sum path - Clean up development directories and unused files
150 lines
5.0 KiB
SQL
150 lines
5.0 KiB
SQL
-- +goose Up
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
avatar_url TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
is_verified BOOLEAN DEFAULT false,
|
|
last_login TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Tags table
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(100) NOT NULL,
|
|
color VARCHAR(7) DEFAULT '#39b9ff',
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(name, user_id)
|
|
);
|
|
|
|
-- Bookmarks table
|
|
CREATE TABLE IF NOT EXISTS bookmarks (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
description TEXT,
|
|
favicon_url TEXT,
|
|
screenshot_url TEXT,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
is_archived BOOLEAN DEFAULT false,
|
|
is_favorite BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Bookmark tags junction table
|
|
CREATE TABLE IF NOT EXISTS bookmark_tags (
|
|
bookmark_id UUID REFERENCES bookmarks(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (bookmark_id, tag_id)
|
|
);
|
|
|
|
-- Tasks table
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed')),
|
|
priority VARCHAR(10) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high')),
|
|
due_date TIMESTAMP,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Task tags junction table
|
|
CREATE TABLE IF NOT EXISTS task_tags (
|
|
task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (task_id, tag_id)
|
|
);
|
|
|
|
-- Notes table
|
|
CREATE TABLE IF NOT EXISTS notes (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Note tags junction table
|
|
CREATE TABLE IF NOT EXISTS note_tags (
|
|
note_id UUID REFERENCES notes(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (note_id, tag_id)
|
|
);
|
|
|
|
-- Files table
|
|
CREATE TABLE IF NOT EXISTS files (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
filename VARCHAR(255) NOT NULL,
|
|
original_name VARCHAR(255) NOT NULL,
|
|
file_size BIGINT NOT NULL,
|
|
mime_type VARCHAR(100),
|
|
file_path TEXT NOT NULL,
|
|
thumbnail_path TEXT,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- File tags junction table
|
|
CREATE TABLE IF NOT EXISTS file_tags (
|
|
file_id UUID REFERENCES files(id) ON DELETE CASCADE,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (file_id, tag_id)
|
|
);
|
|
|
|
-- Audit logs table
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(50) NOT NULL,
|
|
resource_id UUID,
|
|
old_values JSONB,
|
|
new_values JSONB,
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_tags_user_id ON tags(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bookmarks_user_id ON bookmarks(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bookmarks_url ON bookmarks(url);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
|
|
CREATE INDEX IF NOT EXISTS idx_notes_user_id ON notes(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_files_user_id ON files(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at);
|
|
|
|
-- +goose Down
|
|
-- Drop tables in reverse order due to foreign key constraints
|
|
DROP TABLE IF EXISTS file_tags;
|
|
DROP TABLE IF EXISTS note_tags;
|
|
DROP TABLE IF EXISTS task_tags;
|
|
DROP TABLE IF EXISTS bookmark_tags;
|
|
DROP TABLE IF EXISTS audit_logs;
|
|
DROP TABLE IF EXISTS files;
|
|
DROP TABLE IF EXISTS notes;
|
|
DROP TABLE IF EXISTS tasks;
|
|
DROP TABLE IF EXISTS bookmarks;
|
|
DROP TABLE IF EXISTS tags;
|
|
DROP TABLE IF EXISTS users;
|