96 lines
3.5 KiB
YAML
96 lines
3.5 KiB
YAML
{{- if .Values.postgres }}
|
|
apiVersion: v1
|
|
kind: ConfigMap
|
|
metadata:
|
|
name: {{ include "tasko.fullname" . }}-db-schema
|
|
labels:
|
|
{{- include "tasko.labels" . | nindent 4 }}
|
|
app.kubernetes.io/component: database
|
|
data:
|
|
schema.sql: |
|
|
-- Create users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
first_name TEXT,
|
|
last_name TEXT,
|
|
display_name TEXT UNIQUE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users (username);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_display_name ON users (display_name);
|
|
|
|
-- Create tasks table
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending', -- pending / in_progress / completed / cancelled
|
|
priority TEXT DEFAULT 'medium', -- low / medium / high / urgent
|
|
due_date TIMESTAMP,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
assigned_to INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TIMESTAMP
|
|
);
|
|
|
|
-- Indexes for tasks
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks (status);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks (priority);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_assigned_to ON tasks (assigned_to);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks (due_date);
|
|
|
|
-- Create tags table
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT UNIQUE NOT NULL,
|
|
color TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create task_tags junction table
|
|
CREATE TABLE IF NOT EXISTS task_tags (
|
|
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
|
|
tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (task_id, tag_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_task_tags_task_id ON task_tags (task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_task_tags_tag_id ON task_tags (tag_id);
|
|
|
|
-- Add display_name column if it doesn't exist (migration support)
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'users' AND column_name = 'display_name'
|
|
) THEN
|
|
ALTER TABLE users ADD COLUMN display_name TEXT;
|
|
-- Set display_name to username for existing users
|
|
UPDATE users SET display_name = username WHERE display_name IS NULL;
|
|
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
|
|
ALTER TABLE users ADD CONSTRAINT users_display_name_key UNIQUE (display_name);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify schema
|
|
SELECT 'Users table:' as info;
|
|
SELECT column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'users'
|
|
ORDER BY ordinal_position;
|
|
|
|
SELECT 'Tasks table:' as info;
|
|
SELECT column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'tasks'
|
|
ORDER BY ordinal_position;
|
|
{{- end }}
|