Marcus Rehbock
ac0b82e6a0
Some checks failed
Build & Release APK / build (push) Failing after 13s
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
74 lines
2.8 KiB
PL/PgSQL
74 lines
2.8 KiB
PL/PgSQL
-- Baseline: the schema that existed in the live `personal` DB before this repo
|
|
-- had migrations. Fully idempotent so it no-ops against the live DB and builds
|
|
-- everything from scratch on a fresh install.
|
|
|
|
create extension if not exists "uuid-ossp";
|
|
|
|
create or replace function set_updated_at() returns trigger as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create table if not exists people (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
full_name text not null,
|
|
first_name text,
|
|
last_name text,
|
|
email text,
|
|
phone text,
|
|
tags text[] not null default '{}',
|
|
notes text,
|
|
location text,
|
|
source text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create unique index if not exists people_email_lower_uniq
|
|
on people (lower(email)) where email is not null;
|
|
create index if not exists people_tags_gin on people using gin (tags);
|
|
|
|
create table if not exists interactions (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
person_id uuid not null references people(id) on delete cascade,
|
|
type text,
|
|
occurred_at timestamptz not null default now(),
|
|
summary text,
|
|
notes text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists interactions_person_idx on interactions (person_id);
|
|
create index if not exists interactions_occurred_at_idx on interactions (occurred_at desc);
|
|
|
|
create table if not exists relationships (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
from_person_id uuid not null references people(id) on delete cascade,
|
|
to_person_id uuid not null references people(id) on delete cascade,
|
|
type text not null,
|
|
notes text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint relationships_no_self check (from_person_id <> to_person_id),
|
|
constraint relationships_unique unique (from_person_id, to_person_id, type)
|
|
);
|
|
|
|
create index if not exists relationships_from_idx on relationships (from_person_id);
|
|
create index if not exists relationships_to_idx on relationships (to_person_id);
|
|
|
|
-- Triggers: drop-and-recreate is the only idempotent form pre-PG14.
|
|
drop trigger if exists people_set_updated_at on people;
|
|
create trigger people_set_updated_at before update on people
|
|
for each row execute function set_updated_at();
|
|
|
|
drop trigger if exists interactions_set_updated_at on interactions;
|
|
create trigger interactions_set_updated_at before update on interactions
|
|
for each row execute function set_updated_at();
|
|
|
|
drop trigger if exists relationships_set_updated_at on relationships;
|
|
create trigger relationships_set_updated_at before update on relationships
|
|
for each row execute function set_updated_at();
|