schema.sql

-- ============================================================================
-- Task Organizer — complete database schema
--
-- Run ONCE in the Supabase dashboard → SQL Editor. This is the only script you
-- need to install the app from scratch.
--
-- It is idempotent: re-running it changes nothing that is already in place, so
-- it is also safe to run on a database that predates this file (it will drop
-- the older, hand-made policies by name and replace them with the ones below).
--
-- 👉 BEFORE RUNNING: edit the email in `public.is_owner()` at step 1.
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 1. Who owns this installation
--
--    Task Organizer is SINGLE-USER by design: one person, one Supabase project,
--    one deployment. The tables that hold the actual work (projects, macro
--    themes, tasks, comments, checklists) carry no `user_id` column, so there
--    is nothing to separate one user's rows from another's. Authorisation is
--    therefore a single question — "are you the owner?" — asked in one place.
--
--    ⚠️ `auth.role() = 'authenticated'` on its own protects NOTHING. Any Google
--    account can sign in to your Supabase project and would pass that test. The
--    email check is what makes the difference, and the gate in the frontend
--    (VITE_OWNER_EMAIL) does not protect the database — only these policies do.
-- ----------------------------------------------------------------------------

-- 👉 EDIT THIS LINE: the Google account that owns this installation.
--    It must match VITE_OWNER_EMAIL in your .env.
create or replace function public.is_owner() returns boolean
language sql stable as $$
  select auth.role() = 'authenticated'
     and auth.email() = 'YOUR_EMAIL@example.com'
$$;


-- ----------------------------------------------------------------------------
-- 2. Tables
--
--    Created in dependency order: projects → macrotemi → tasks → children.
--
--    On the name `macrotemi`: it is Italian for "macro themes" and it is the
--    one non-English identifier in the codebase, because it is the database
--    schema itself. Renaming it is a migration, not a translation.
-- ----------------------------------------------------------------------------

-- A project groups macro themes. Optional: a macro theme can stand alone.
create table if not exists projects (
  id          uuid primary key default gen_random_uuid(),
  title       text not null,
  created_at  timestamptz default now()
);

-- A macro theme groups tasks. `on delete restrict` on the project is
-- deliberate: deleting a project must not silently take its macro themes (and
-- through them every task) with it. Empty the project first.
create table if not exists macrotemi (
  id          uuid primary key default gen_random_uuid(),
  title       text not null default '',
  project_id  uuid references projects (id) on delete restrict,
  created_at  timestamptz default now()
);

-- `timer_started_at` lives on the task because the stopwatch has to survive a
-- reload and follow you across devices: it is database state, not React state.
--
-- `recurrence_anchor_day` is the intended day of month for monthly/yearly
-- recurrences (1–31). Without it a "the 31st" recurrence drifts: in February it
-- gets clamped to the 28th and every occurrence after that would restart from
-- the 28th. Remembering the original 31 sends March back to the 31st.
create table if not exists tasks (
  id                     uuid primary key default gen_random_uuid(),
  macrotema_id           uuid not null references macrotemi (id) on delete cascade,
  title                  text not null default '',
  description            text,
  priority               text not null default 'medium'
                           check (priority in ('high', 'medium', 'low')),
  -- 'open' = actionable now, 'waiting' = blocked on someone else
  status                 text not null default 'open'
                           check (status in ('open', 'waiting')),
  due_date               date,
  recurrence             text
                           check (recurrence is null
                                  or recurrence in ('daily', 'weekly', 'monthly', 'yearly')),
  recurrence_anchor_day  smallint
                           check (recurrence_anchor_day is null
                                  or recurrence_anchor_day between 1 and 31),
  timer_started_at       timestamptz,
  created_at             timestamptz default now()
);

create index if not exists tasks_macrotema_idx on tasks (macrotema_id);
create index if not exists tasks_due_date_idx  on tasks (due_date);
create index if not exists tasks_status_idx    on tasks (status);

create table if not exists comments (
  id          uuid primary key default gen_random_uuid(),
  task_id     uuid not null references tasks (id) on delete cascade,
  text        text not null,
  created_at  timestamptz default timezone('utc'::text, now())
);

create index if not exists comments_task_idx on comments (task_id, created_at);

-- A checklist inside a task.
create table if not exists subtasks (
  id          uuid primary key default gen_random_uuid(),
  task_id     uuid not null references tasks (id) on delete cascade,
  text        text not null,
  done        boolean not null default false,
  position    integer not null default 0,
  created_at  timestamptz not null default now()
);

create index if not exists subtasks_task_idx on subtasks (task_id, position);

-- Quick notes: either text, or a hand-drawn sketch stored as a data URI.
create table if not exists notes (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null default auth.uid()
                references auth.users (id) on delete cascade,
  type        text not null default 'text' check (type in ('text', 'drawing')),
  text        text,
  image       text,
  pinned      boolean not null default false,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz default now()
);

create index if not exists notes_user_idx on notes (user_id, created_at desc);

create table if not exists appointments (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null default auth.uid()
                references auth.users (id) on delete cascade,
  date        date not null,
  text        text not null,
  created_at  timestamptz default now(),
  updated_at  timestamptz default now()
);

create index if not exists appointments_user_idx on appointments (user_id, date);


-- ----------------------------------------------------------------------------
-- 3. Analytics events
--
--    These three tables record that something HAPPENED, and they outlive the
--    rows they refer to. That is the whole point: "Done" deletes the task, so
--    rebuilding history from the tasks that still exist would let today's
--    deletions rewrite last year's chart.
--
--    The `*_title` columns are denormalised on purpose, and the foreign keys
--    are `on delete set null` rather than `cascade`: losing a macro theme must
--    not erase the record that the work happened.
-- ----------------------------------------------------------------------------

-- A completion event.
-- `user_id` is `text` here and `uuid` in the two tables below. That asymmetry is
-- historical, it is load-bearing for the policy at the bottom (note the
-- `::text` cast), and it is kept so this file describes real installations
-- rather than an idealised one.
create table if not exists completed_tasks (
  id               uuid primary key default gen_random_uuid(),
  user_id          text not null,
  completed_at     timestamptz not null default now(),
  task_created_at  timestamptz not null,
  macrotema_id     uuid references macrotemi (id) on delete set null,
  macrotema_title  text not null default '',
  project_id       uuid references projects (id) on delete set null,
  project_title    text not null default ''
);

create index if not exists completed_tasks_user_idx      on completed_tasks (user_id, completed_at);
create index if not exists completed_tasks_macrotema_idx on completed_tasks (user_id, macrotema_id);
create index if not exists completed_tasks_project_idx   on completed_tasks (user_id, project_id);

-- A creation event. No text is stored: only WHEN a task was created.
create table if not exists created_tasks (
  id               uuid primary key default gen_random_uuid(),
  user_id          uuid not null references auth.users (id) on delete cascade,
  created_at       timestamptz not null default now(),
  macrotema_id     uuid references macrotemi (id) on delete set null,
  macrotema_title  text not null default '',
  project_id       uuid references projects (id) on delete set null,
  project_title    text not null default ''
);

create index if not exists created_tasks_user_idx      on created_tasks (user_id, created_at);
create index if not exists created_tasks_macrotema_idx on created_tasks (user_id, macrotema_id);
create index if not exists created_tasks_project_idx   on created_tasks (user_id, project_id);

-- A timed work session. `task_title` is denormalised so the row stays readable
-- ("where did the time go?") once the task is completed or deleted, which is
-- also why its foreign key is `on delete set null`.
create table if not exists time_entries (
  id               uuid primary key default gen_random_uuid(),
  user_id          uuid not null references auth.users (id) on delete cascade,
  task_id          uuid references tasks (id) on delete set null,
  task_title       text not null default '',
  seconds          integer not null default 0,
  started_at       timestamptz not null,
  ended_at         timestamptz not null default now(),
  macrotema_id     uuid references macrotemi (id) on delete set null,
  macrotema_title  text not null default '',
  project_id       uuid references projects (id) on delete set null,
  project_title    text not null default ''
);

create index if not exists time_entries_user_idx      on time_entries (user_id, ended_at);
create index if not exists time_entries_macrotema_idx on time_entries (user_id, macrotema_id);
create index if not exists time_entries_project_idx   on time_entries (user_id, project_id);


-- ----------------------------------------------------------------------------
-- 4. Row Level Security
--
--    Enabled on every table, no exceptions. Two rules, applied consistently:
--
--    a) Tables with a `user_id`: filter on the user, `auth.uid() = user_id`.
--    b) Tables without one: filter on the owner, `public.is_owner()`.
--
--    Every policy is `to authenticated`. Without that clause a policy applies
--    to PUBLIC — the `anon` role included — and the anon key is public by
--    design: it ships inside the JavaScript bundle of your deployed site.
--
--    The `drop policy if exists` lines below also remove the older, hand-made
--    policy names used before this file existed. They matter: policies combine
--    with OR, so an over-permissive leftover would keep granting access no
--    matter how strict the new policy is. On a fresh database they are no-ops.
-- ----------------------------------------------------------------------------

alter table projects        enable row level security;
alter table macrotemi       enable row level security;
alter table tasks           enable row level security;
alter table comments        enable row level security;
alter table subtasks        enable row level security;
alter table notes           enable row level security;
alter table appointments    enable row level security;
alter table completed_tasks enable row level security;
alter table created_tasks   enable row level security;
alter table time_entries    enable row level security;

-- --- Owner-gated tables ------------------------------------------------------

drop policy if exists "Allow all for projects" on projects;
drop policy if exists "owner projects" on projects;
create policy "owner projects" on projects
  for all to authenticated
  using (public.is_owner()) with check (public.is_owner());

drop policy if exists "Authenticated can select" on macrotemi;
drop policy if exists "Authenticated can insert" on macrotemi;
drop policy if exists "Authenticated can update" on macrotemi;
drop policy if exists "Authenticated can delete" on macrotemi;
drop policy if exists "owner macrotemi" on macrotemi;
create policy "owner macrotemi" on macrotemi
  for all to authenticated
  using (public.is_owner()) with check (public.is_owner());

drop policy if exists "Authenticated can select" on tasks;
drop policy if exists "Authenticated can insert" on tasks;
drop policy if exists "Authenticated can update" on tasks;
drop policy if exists "Authenticated can delete" on tasks;
drop policy if exists "owner tasks" on tasks;
create policy "owner tasks" on tasks
  for all to authenticated
  using (public.is_owner()) with check (public.is_owner());

drop policy if exists "Authenticated can select" on comments;
drop policy if exists "Authenticated can insert" on comments;
drop policy if exists "Authenticated can update" on comments;
drop policy if exists "Authenticated can delete" on comments;
drop policy if exists "owner comments" on comments;
create policy "owner comments" on comments
  for all to authenticated
  using (public.is_owner()) with check (public.is_owner());

drop policy if exists "authenticated subtasks" on subtasks;
drop policy if exists "authorized subtasks" on subtasks;
drop policy if exists "owner subtasks" on subtasks;
create policy "owner subtasks" on subtasks
  for all to authenticated
  using (public.is_owner()) with check (public.is_owner());

-- --- User-scoped tables ------------------------------------------------------

drop policy if exists "Authenticated can select" on notes;
drop policy if exists "Authenticated can insert" on notes;
drop policy if exists "Authenticated can update" on notes;
drop policy if exists "Authenticated can delete" on notes;
drop policy if exists "own notes" on notes;
create policy "own notes" on notes
  for all to authenticated
  using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "Users can view their own appointments" on appointments;
drop policy if exists "Users can insert their own appointments" on appointments;
drop policy if exists "Users can update their own appointments" on appointments;
drop policy if exists "Enable delete for users based on user_id" on appointments;
drop policy if exists "own appointments" on appointments;
create policy "own appointments" on appointments
  for all to authenticated
  using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "Users manage own completed_tasks" on completed_tasks;
drop policy if exists "own completed_tasks" on completed_tasks;
create policy "own completed_tasks" on completed_tasks
  for all to authenticated
  using (user_id = auth.uid()::text) with check (user_id = auth.uid()::text);

drop policy if exists "own created_tasks" on created_tasks;
create policy "own created_tasks" on created_tasks
  for all to authenticated
  using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "own time_entries" on time_entries;
create policy "own time_entries" on time_entries
  for all to authenticated
  using (auth.uid() = user_id) with check (auth.uid() = user_id);


-- ----------------------------------------------------------------------------
-- 5. Repairs for installations created before this file existed
--
--    Every statement here is a no-op on a fresh database. They only matter if
--    your tables were built by hand in the dashboard, where a few defaults and
--    constraints ended up wrong.
-- ----------------------------------------------------------------------------

-- `macrotemi.title` was created with the literal string 'NOT NULL' as its
-- default — the words typed into the dashboard's "Default Value" box, where
-- they were read as text rather than as a constraint.
alter table macrotemi alter column title set default '';

-- `notes.user_id` defaulted to `gen_random_uuid()`, so a note inserted without
-- an explicit user_id was assigned a random owner: invisible afterwards, with
-- no error and no way to find it again.
alter table notes alter column user_id set default auth.uid();
alter table notes alter column pinned  set default false;

-- Two foreign keys on `tasks.macrotema_id` pointed at the same column with
-- opposite delete rules — one CASCADE, one NO ACTION — leaving the outcome of
-- deleting a macro theme dependent on the order Postgres evaluated them in.
-- Keep CASCADE, drop the other.
do $$
declare c record;
begin
  for c in
    select conname from pg_constraint
    where conrelid = 'tasks'::regclass
      and contype = 'f'
      and confdeltype = 'a'                              -- NO ACTION
      and conkey = array[(select attnum from pg_attribute
                          where attrelid = 'tasks'::regclass
                            and attname = 'macrotema_id')]
  loop
    execute format('alter table tasks drop constraint %I', c.conname);
    raise notice 'dropped duplicate foreign key %', c.conname;
  end loop;
end $$;