schema.sql

create table
  public.auth_ledger (
    id bigint generated by default as identity,
    action public.AUTH_ACTION not null,
    username text null,
    email text null,
    action_timestamp timestamp with time zone null default now(),
    user_id bigint null,
    successful boolean null,
    constraint auth_ledger_pkey primary key (id),
    constraint auth_ledger_user_id_fkey foreign key (user_id) references users (id)
  ) tablespace pg_default;

  create table
  public.group_members (
    group_id bigint generated by default as identity,
    user_id bigint not null,
    constraint group_members_pkey primary key (group_id, user_id),
    constraint group_members_group_id_fkey foreign key (group_id) references groups (id),
    constraint group_members_user_id_fkey foreign key (user_id) references users (id)
  ) tablespace pg_default;

  create table
  public.groups (
    id bigint generated by default as identity,
    name text null,
    created_at timestamp with time zone not null default now(),
    owner bigint not null,
    constraint groups_pkey primary key (id),
    constraint groups_owner_fkey foreign key (owner) references users (id)
  ) tablespace pg_default;

  create table
  public.line_item_members (
    user_id bigint generated by default as identity,
    line_item_id bigint not null,
    constraint line_item_members_pkey primary key (user_id, line_item_id),
    constraint line_item_members_line_item_id_fkey foreign key (line_item_id) references line_items (id),
    constraint line_item_members_user_id_fkey foreign key (user_id) references users (id)
  ) tablespace pg_default;

  create table
  public.line_items (
    trip_id bigint generated by default as identity,
    item_name text not null,
    price integer not null,
    quantity integer null,
    id bigint generated by default as identity,
    constraint trip_line_items_pkey primary key (id),
    constraint line_items_trip_id_fkey foreign key (trip_id) references shopping_trips (id),
    constraint line_items_price_check check (
      (
        (price)::double precision >= (0)::double precision
      )
    ),
    constraint line_items_quantity_check check ((quantity >= 0))
  ) tablespace pg_default;

  create table
  public.shopping_trips (
    id bigint generated by default as identity,
    description text not null,
    payer_id bigint not null,
    created_at timestamp with time zone not null default now(),
    group_id bigint not null,
    constraint expenses_pkey primary key (id),
    constraint shopping_trips_group_id_fkey foreign key (group_id) references groups (id),
    constraint shopping_trips_payer_id_fkey foreign key (payer_id) references users (id)
  ) tablespace pg_default;

  create table
  public.transaction_ledger (
    transaction_id bigint not null,
    timestamp timestamp with time zone not null default now(),
    to_id bigint not null,
    from_id bigint not null,
    change integer not null,
    id bigint generated by default as identity,
    constraint transaction_ledger_pkey primary key (id),
    constraint transaction_ledger_from_id_fkey foreign key (from_id) references users (id),
    constraint transaction_ledger_to_id_fkey foreign key (to_id) references users (id),
    constraint transaction_ledger_transaction_id_fkey foreign key (transaction_id) references transactions (id)
  ) tablespace pg_default;

  create table
  public.transactions (
    id bigint generated by default as identity,
    description text not null,
    trip_id bigint null,
    timestamp timestamp with time zone not null default now(),
    group_id bigint null,
    constraint transactions_pkey primary key (id),
    constraint transactions_group_id_fkey foreign key (group_id) references groups (id),
    constraint transactions_trip_id_fkey foreign key (trip_id) references shopping_trips (id)
  ) tablespace pg_default;

  create table
  public.users (
    id bigint generated by default as identity,
    username text not null,
    password text not null,
    created_at timestamp with time zone null default now(),
    email text not null,
    constraint users_pkey primary key (id),
    constraint users_email_key unique (email)
  ) tablespace pg_default;