980 lines
41 KiB
PL/PgSQL
980 lines
41 KiB
PL/PgSQL
-- =============================================================================
|
|
-- Life OS - Release 1 COMPLETE Schema
|
|
-- Self-hosted PostgreSQL 16 on defiant-01 (Hetzner)
|
|
-- Database: lifeos_dev
|
|
-- Generated from Architecture Design Document v2.0
|
|
-- =============================================================================
|
|
|
|
-- Extensions
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- =============================================================================
|
|
-- LOOKUP TABLE: Context Types
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE context_types (
|
|
id SERIAL PRIMARY KEY,
|
|
value TEXT NOT NULL UNIQUE,
|
|
label TEXT NOT NULL,
|
|
description TEXT,
|
|
is_system BOOLEAN NOT NULL DEFAULT true,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- CORE HIERARCHY
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE domains (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
color TEXT,
|
|
description TEXT,
|
|
icon TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE areas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
icon TEXT,
|
|
color TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
|
area_id UUID REFERENCES areas(id) ON DELETE SET NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
priority INTEGER NOT NULL DEFAULT 3,
|
|
start_date DATE,
|
|
target_date DATE,
|
|
completed_at TIMESTAMPTZ,
|
|
color TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Forward-declare releases for tasks.release_id FK
|
|
CREATE TABLE releases (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
version_label TEXT,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'planned',
|
|
target_date DATE,
|
|
released_at DATE,
|
|
release_notes TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Forward-declare contacts for tasks.waiting_for_contact_id FK
|
|
CREATE TABLE contacts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
first_name TEXT NOT NULL,
|
|
last_name TEXT,
|
|
company TEXT,
|
|
role TEXT,
|
|
email TEXT,
|
|
phone TEXT,
|
|
notes TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE tasks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID REFERENCES domains(id) ON DELETE CASCADE,
|
|
area_id UUID REFERENCES areas(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
release_id UUID REFERENCES releases(id) ON DELETE SET NULL,
|
|
parent_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
priority INTEGER NOT NULL DEFAULT 3,
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
due_date DATE,
|
|
deadline TIMESTAMPTZ,
|
|
recurrence TEXT,
|
|
estimated_minutes INTEGER,
|
|
energy_required TEXT,
|
|
context TEXT,
|
|
is_custom_context BOOLEAN NOT NULL DEFAULT false,
|
|
waiting_for_contact_id UUID REFERENCES contacts(id) ON DELETE SET NULL,
|
|
waiting_since DATE,
|
|
import_batch_id UUID,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- KNOWLEDGE MANAGEMENT
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE note_folders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_id UUID REFERENCES note_folders(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
auto_generated BOOLEAN NOT NULL DEFAULT false,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Forward-declare meetings for notes.meeting_id FK
|
|
CREATE TABLE meetings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_id UUID REFERENCES meetings(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
meeting_date DATE NOT NULL,
|
|
start_at TIMESTAMPTZ,
|
|
end_at TIMESTAMPTZ,
|
|
location TEXT,
|
|
status TEXT NOT NULL DEFAULT 'scheduled',
|
|
priority INTEGER,
|
|
recurrence TEXT,
|
|
agenda TEXT,
|
|
transcript TEXT,
|
|
notes_body TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE notes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID REFERENCES domains(id) ON DELETE CASCADE,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
folder_id UUID REFERENCES note_folders(id) ON DELETE SET NULL,
|
|
meeting_id UUID REFERENCES meetings(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
body TEXT,
|
|
content_format TEXT NOT NULL DEFAULT 'rich',
|
|
is_meeting_note BOOLEAN NOT NULL DEFAULT false,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE decisions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
rationale TEXT,
|
|
status TEXT NOT NULL DEFAULT 'proposed',
|
|
impact TEXT NOT NULL DEFAULT 'medium',
|
|
decided_at DATE,
|
|
meeting_id UUID REFERENCES meetings(id) ON DELETE SET NULL,
|
|
superseded_by_id UUID REFERENCES decisions(id) ON DELETE SET NULL,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE lists (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID REFERENCES domains(id) ON DELETE CASCADE,
|
|
area_id UUID REFERENCES areas(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
name TEXT NOT NULL,
|
|
list_type TEXT NOT NULL DEFAULT 'checklist',
|
|
description TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE list_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
list_id UUID NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
|
|
parent_item_id UUID REFERENCES list_items(id) ON DELETE SET NULL,
|
|
content TEXT NOT NULL,
|
|
completed BOOLEAN NOT NULL DEFAULT false,
|
|
completed_at TIMESTAMPTZ,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE links (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID REFERENCES domains(id) ON DELETE CASCADE,
|
|
area_id UUID REFERENCES areas(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
label TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
description TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE files (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
filename TEXT NOT NULL,
|
|
original_filename TEXT NOT NULL,
|
|
storage_path TEXT NOT NULL,
|
|
mime_type TEXT,
|
|
size_bytes INTEGER,
|
|
description TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Appointments
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE appointments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
location TEXT,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ,
|
|
all_day BOOLEAN NOT NULL DEFAULT false,
|
|
recurrence TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Milestones
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE milestones (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
release_id UUID REFERENCES releases(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
name TEXT NOT NULL,
|
|
target_date DATE NOT NULL,
|
|
completed_at DATE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Processes
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE processes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
process_type TEXT NOT NULL DEFAULT 'checklist',
|
|
category TEXT,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE process_steps (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
process_id UUID NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
instructions TEXT,
|
|
expected_output TEXT,
|
|
estimated_days INTEGER,
|
|
context TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE process_runs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
process_id UUID NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'not_started',
|
|
process_type TEXT NOT NULL,
|
|
task_generation TEXT NOT NULL DEFAULT 'all_at_once',
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
contact_id UUID REFERENCES contacts(id) ON DELETE SET NULL,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE process_run_steps (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
run_id UUID NOT NULL REFERENCES process_runs(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
instructions TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
completed_by_id UUID REFERENCES contacts(id) ON DELETE SET NULL,
|
|
completed_at TIMESTAMPTZ,
|
|
notes TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Daily Focus
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE daily_focus (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
focus_date DATE NOT NULL,
|
|
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
slot INTEGER,
|
|
completed BOOLEAN NOT NULL DEFAULT false,
|
|
note TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Capture Queue
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE capture (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
raw_text TEXT NOT NULL,
|
|
processed BOOLEAN NOT NULL DEFAULT false,
|
|
converted_to_type TEXT,
|
|
converted_to_id UUID,
|
|
area_id UUID REFERENCES areas(id) ON DELETE SET NULL,
|
|
project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
|
|
list_id UUID REFERENCES lists(id) ON DELETE SET NULL,
|
|
import_batch_id UUID,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Task Templates
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE task_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
priority INTEGER,
|
|
estimated_minutes INTEGER,
|
|
energy_required TEXT,
|
|
context TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE task_template_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id UUID NOT NULL REFERENCES task_templates(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- TIME MANAGEMENT
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE time_entries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ,
|
|
duration_minutes INTEGER,
|
|
notes TEXT,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE time_blocks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
context TEXT,
|
|
energy TEXT,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ NOT NULL,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE time_budgets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
|
weekly_hours DECIMAL NOT NULL,
|
|
effective_from DATE NOT NULL,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Weblink Directory
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE weblink_folders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_id UUID REFERENCES weblink_folders(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
auto_generated BOOLEAN NOT NULL DEFAULT false,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE weblinks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
label TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
description TEXT,
|
|
tags TEXT[],
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
search_vector TSVECTOR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- SYSTEM LEVEL: Reminders (polymorphic)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE reminders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
entity_type TEXT NOT NULL,
|
|
entity_id UUID NOT NULL,
|
|
remind_at TIMESTAMPTZ NOT NULL,
|
|
note TEXT,
|
|
delivered BOOLEAN NOT NULL DEFAULT false,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- UNIVERSAL: Dependencies (polymorphic DAG)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE dependencies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
blocker_type TEXT NOT NULL,
|
|
blocker_id UUID NOT NULL,
|
|
dependent_type TEXT NOT NULL,
|
|
dependent_id UUID NOT NULL,
|
|
dependency_type TEXT NOT NULL DEFAULT 'finish_to_start',
|
|
lag_days INTEGER NOT NULL DEFAULT 0,
|
|
note TEXT,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (blocker_type, blocker_id, dependent_type, dependent_id, dependency_type),
|
|
CHECK (NOT (blocker_type = dependent_type AND blocker_id = dependent_id))
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- JUNCTION TABLES
|
|
-- =============================================================================
|
|
|
|
-- Notes <-> Projects (M2M)
|
|
CREATE TABLE note_projects (
|
|
note_id UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (note_id, project_id)
|
|
);
|
|
|
|
-- Notes <-> Notes (wiki graph)
|
|
CREATE TABLE note_links (
|
|
source_note_id UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
|
|
target_note_id UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (source_note_id, target_note_id)
|
|
);
|
|
|
|
-- Files <-> any entity (polymorphic M2M)
|
|
CREATE TABLE file_mappings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
|
|
context_type TEXT NOT NULL,
|
|
context_id UUID NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (file_id, context_type, context_id)
|
|
);
|
|
|
|
-- Releases <-> Projects (M2M)
|
|
CREATE TABLE release_projects (
|
|
release_id UUID NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (release_id, project_id)
|
|
);
|
|
|
|
-- Releases <-> Domains (M2M)
|
|
CREATE TABLE release_domains (
|
|
release_id UUID NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
|
|
domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (release_id, domain_id)
|
|
);
|
|
|
|
-- Contacts <-> Tasks
|
|
CREATE TABLE contact_tasks (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, task_id)
|
|
);
|
|
|
|
-- Contacts <-> Projects
|
|
CREATE TABLE contact_projects (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, project_id)
|
|
);
|
|
|
|
-- Contacts <-> Lists
|
|
CREATE TABLE contact_lists (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
list_id UUID NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, list_id)
|
|
);
|
|
|
|
-- Contacts <-> List Items
|
|
CREATE TABLE contact_list_items (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
list_item_id UUID NOT NULL REFERENCES list_items(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, list_item_id)
|
|
);
|
|
|
|
-- Contacts <-> Appointments
|
|
CREATE TABLE contact_appointments (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
appointment_id UUID NOT NULL REFERENCES appointments(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, appointment_id)
|
|
);
|
|
|
|
-- Contacts <-> Meetings
|
|
CREATE TABLE contact_meetings (
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
meeting_id UUID NOT NULL REFERENCES meetings(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (contact_id, meeting_id)
|
|
);
|
|
|
|
-- Decisions <-> Projects
|
|
CREATE TABLE decision_projects (
|
|
decision_id UUID NOT NULL REFERENCES decisions(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (decision_id, project_id)
|
|
);
|
|
|
|
-- Decisions <-> Contacts
|
|
CREATE TABLE decision_contacts (
|
|
decision_id UUID NOT NULL REFERENCES decisions(id) ON DELETE CASCADE,
|
|
contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
role TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (decision_id, contact_id)
|
|
);
|
|
|
|
-- Meetings <-> Tasks
|
|
CREATE TABLE meeting_tasks (
|
|
meeting_id UUID NOT NULL REFERENCES meetings(id) ON DELETE CASCADE,
|
|
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (meeting_id, task_id)
|
|
);
|
|
|
|
-- Process Run Steps <-> Tasks
|
|
CREATE TABLE process_run_tasks (
|
|
run_step_id UUID NOT NULL REFERENCES process_run_steps(id) ON DELETE CASCADE,
|
|
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (run_step_id, task_id)
|
|
);
|
|
|
|
-- Weblinks <-> Folders (M2M)
|
|
CREATE TABLE folder_weblinks (
|
|
folder_id UUID NOT NULL REFERENCES weblink_folders(id) ON DELETE CASCADE,
|
|
weblink_id UUID NOT NULL REFERENCES weblinks(id) ON DELETE CASCADE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (folder_id, weblink_id)
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- INDEXES
|
|
-- =============================================================================
|
|
|
|
-- Sort order indexes
|
|
CREATE INDEX idx_domains_sort ON domains(sort_order);
|
|
CREATE INDEX idx_areas_sort ON areas(domain_id, sort_order);
|
|
CREATE INDEX idx_projects_sort ON projects(domain_id, sort_order);
|
|
CREATE INDEX idx_projects_area_sort ON projects(area_id, sort_order);
|
|
CREATE INDEX idx_tasks_project_sort ON tasks(project_id, sort_order);
|
|
CREATE INDEX idx_tasks_parent_sort ON tasks(parent_id, sort_order);
|
|
CREATE INDEX idx_tasks_domain_sort ON tasks(domain_id, sort_order);
|
|
CREATE INDEX idx_list_items_sort ON list_items(list_id, sort_order);
|
|
CREATE INDEX idx_list_items_parent_sort ON list_items(parent_item_id, sort_order);
|
|
CREATE INDEX idx_weblink_folders_sort ON weblink_folders(parent_id, sort_order);
|
|
|
|
-- Lookup indexes
|
|
CREATE INDEX idx_tasks_status ON tasks(status);
|
|
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
|
|
CREATE INDEX idx_tasks_priority ON tasks(priority);
|
|
CREATE INDEX idx_projects_status ON projects(status);
|
|
CREATE INDEX idx_daily_focus_date ON daily_focus(focus_date);
|
|
CREATE INDEX idx_appointments_start ON appointments(start_at);
|
|
CREATE INDEX idx_capture_processed ON capture(processed);
|
|
CREATE INDEX idx_file_mappings_context ON file_mappings(context_type, context_id);
|
|
CREATE INDEX idx_dependencies_blocker ON dependencies(blocker_type, blocker_id);
|
|
CREATE INDEX idx_dependencies_dependent ON dependencies(dependent_type, dependent_id);
|
|
CREATE INDEX idx_reminders_entity ON reminders(entity_type, entity_id);
|
|
CREATE INDEX idx_time_entries_task ON time_entries(task_id);
|
|
CREATE INDEX idx_meetings_date ON meetings(meeting_date);
|
|
|
|
-- Full-text search GIN indexes
|
|
CREATE INDEX idx_domains_search ON domains USING GIN(search_vector);
|
|
CREATE INDEX idx_areas_search ON areas USING GIN(search_vector);
|
|
CREATE INDEX idx_projects_search ON projects USING GIN(search_vector);
|
|
CREATE INDEX idx_tasks_search ON tasks USING GIN(search_vector);
|
|
CREATE INDEX idx_notes_search ON notes USING GIN(search_vector);
|
|
CREATE INDEX idx_contacts_search ON contacts USING GIN(search_vector);
|
|
CREATE INDEX idx_meetings_search ON meetings USING GIN(search_vector);
|
|
CREATE INDEX idx_decisions_search ON decisions USING GIN(search_vector);
|
|
CREATE INDEX idx_lists_search ON lists USING GIN(search_vector);
|
|
CREATE INDEX idx_links_search ON links USING GIN(search_vector);
|
|
CREATE INDEX idx_files_search ON files USING GIN(search_vector);
|
|
CREATE INDEX idx_weblinks_search ON weblinks USING GIN(search_vector);
|
|
CREATE INDEX idx_processes_search ON processes USING GIN(search_vector);
|
|
CREATE INDEX idx_appointments_search ON appointments USING GIN(search_vector);
|
|
|
|
-- =============================================================================
|
|
-- SEARCH VECTOR TRIGGERS
|
|
-- =============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' ||
|
|
coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(NEW.name, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), '')
|
|
);
|
|
RETURN NEW;
|
|
EXCEPTION WHEN undefined_column THEN
|
|
-- Fallback for tables with different column names
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Per-table triggers with correct columns
|
|
CREATE OR REPLACE FUNCTION update_domains_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english', coalesce(NEW.name, ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_domains_search BEFORE INSERT OR UPDATE ON domains
|
|
FOR EACH ROW EXECUTE FUNCTION update_domains_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_areas_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_areas_search BEFORE INSERT OR UPDATE ON areas
|
|
FOR EACH ROW EXECUTE FUNCTION update_areas_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_projects_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_projects_search BEFORE INSERT OR UPDATE ON projects
|
|
FOR EACH ROW EXECUTE FUNCTION update_projects_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_tasks_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_tasks_search BEFORE INSERT OR UPDATE ON tasks
|
|
FOR EACH ROW EXECUTE FUNCTION update_tasks_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_notes_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_notes_search BEFORE INSERT OR UPDATE ON notes
|
|
FOR EACH ROW EXECUTE FUNCTION update_notes_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_contacts_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.first_name, '') || ' ' || coalesce(NEW.last_name, '') || ' ' ||
|
|
coalesce(NEW.company, '') || ' ' || coalesce(NEW.email, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_contacts_search BEFORE INSERT OR UPDATE ON contacts
|
|
FOR EACH ROW EXECUTE FUNCTION update_contacts_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_meetings_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' || coalesce(NEW.agenda, '') || ' ' ||
|
|
coalesce(NEW.notes_body, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_meetings_search BEFORE INSERT OR UPDATE ON meetings
|
|
FOR EACH ROW EXECUTE FUNCTION update_meetings_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_decisions_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' || coalesce(NEW.rationale, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_decisions_search BEFORE INSERT OR UPDATE ON decisions
|
|
FOR EACH ROW EXECUTE FUNCTION update_decisions_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_lists_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_lists_search BEFORE INSERT OR UPDATE ON lists
|
|
FOR EACH ROW EXECUTE FUNCTION update_lists_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_links_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.label, '') || ' ' || coalesce(NEW.url, '') || ' ' ||
|
|
coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_links_search BEFORE INSERT OR UPDATE ON links
|
|
FOR EACH ROW EXECUTE FUNCTION update_links_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_files_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.original_filename, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_files_search BEFORE INSERT OR UPDATE ON files
|
|
FOR EACH ROW EXECUTE FUNCTION update_files_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_weblinks_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.label, '') || ' ' || coalesce(NEW.url, '') || ' ' ||
|
|
coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_weblinks_search BEFORE INSERT OR UPDATE ON weblinks
|
|
FOR EACH ROW EXECUTE FUNCTION update_weblinks_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_processes_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_processes_search BEFORE INSERT OR UPDATE ON processes
|
|
FOR EACH ROW EXECUTE FUNCTION update_processes_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_appointments_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.title, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(NEW.location, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_appointments_search BEFORE INSERT OR UPDATE ON appointments
|
|
FOR EACH ROW EXECUTE FUNCTION update_appointments_search();
|
|
|
|
CREATE OR REPLACE FUNCTION update_releases_search() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector := to_tsvector('pg_catalog.english',
|
|
coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, '') || ' ' ||
|
|
coalesce(NEW.version_label, '') || ' ' ||
|
|
coalesce(array_to_string(NEW.tags, ' '), ''));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_releases_search BEFORE INSERT OR UPDATE ON releases
|
|
FOR EACH ROW EXECUTE FUNCTION update_releases_search();
|
|
|
|
-- =============================================================================
|
|
-- SEED DATA: Context Types
|
|
-- =============================================================================
|
|
|
|
INSERT INTO context_types (value, label, is_system, sort_order) VALUES
|
|
('deep_work', 'Deep Work', true, 10),
|
|
('quick', 'Quick', true, 20),
|
|
('waiting', 'Waiting', true, 30),
|
|
('someday', 'Someday', true, 40),
|
|
('meeting', 'Meeting', true, 50),
|
|
('errand', 'Errand', true, 60);
|