tables.sql

create table public.components (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  name text not null,
  description text null,
  lifecycle_state text not null default 'active'::text,
  organization_id uuid not null,
  criticality text not null default 'low'::text,
  constraint components_pkey primary key (id),
  constraint components_organization_id_fkey foreign KEY (organization_id) references organizations (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.feedback (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  details text not null,
  user_id uuid null,
  email text null,
  constraint feedback_pkey primary key (id),
  constraint feedback_user_id_fkey foreign KEY (user_id) references users (id) on update CASCADE on delete set null
) TABLESPACE pg_default;

create table public.organization_users (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  name text not null,
  organization_id uuid not null,
  constraint organization_users_pkey primary key (id),
  constraint organization_users_organization_id_fkey foreign KEY (organization_id) references organizations (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.organizations (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  name text not null,
  user_id uuid not null,
  plan text null default 'free'::text,
  constraint organization_pkey primary key (id),
  constraint organization_user_id_fkey foreign KEY (user_id) references users (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.team_components (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  team_id uuid not null,
  component_id uuid not null,
  constraint team_components_pkey primary key (id),
  constraint unique_team_component unique (component_id, team_id),
  constraint team_components_component_id_fkey foreign KEY (component_id) references components (id) on update CASCADE on delete CASCADE,
  constraint team_components_team_id_fkey foreign KEY (team_id) references teams (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.team_users (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  team_id uuid not null,
  organization_user_id uuid not null,
  constraint team_users_pkey primary key (id),
  constraint unique_organization_user_team unique (organization_user_id, team_id),
  constraint team_users_organization_user_id_fkey foreign KEY (organization_user_id) references organization_users (id) on update CASCADE on delete CASCADE,
  constraint team_users_team_id_fkey foreign KEY (team_id) references teams (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.teams (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  name text not null,
  organization_id uuid not null,
  description text null,
  constraint teams_pkey primary key (id),
  constraint teams_organization_id_fkey foreign KEY (organization_id) references organizations (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.user_components (
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  proficiency text not null default 'noKnowledge'::text,
  interest text not null default 'noInterest'::text,
  organization_user_id uuid not null,
  component_id uuid not null,
  constraint user_components_pkey primary key (organization_user_id, component_id),
  constraint unique_organization_user_component unique (organization_user_id, component_id),
  constraint user_components_component_id_fkey foreign KEY (component_id) references components (id) on update CASCADE on delete CASCADE,
  constraint user_components_organization_user_id_fkey foreign KEY (organization_user_id) references organization_users (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

create table public.users (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  name text null,
  organization_id uuid null,
  constraint users_pkey primary key (id),
  constraint users_id_fkey foreign KEY (id) references auth.users (id) on update CASCADE on delete CASCADE,
  constraint users_organization_id_fkey foreign KEY (organization_id) references organizations (id) on update CASCADE on delete set null
) TABLESPACE pg_default;