gistfile1.sql

create table account (
    id          serial  not null,

    constraint pk_account
        primary key(id),
);

create table form (
    id          serial      not null,
    account_id  integer     not null,

    constraint pk_form
        primary key(id),

    constraint fk_account
        foreign key(account_id) references account(id)
);

create table result (
    id          serial      not null,
    form_id     integer     not null,
    account_id  integer     not null,

    constraint pk_result
        primary key(id),

    constraint fk_form
        foreign key(form_id) references form(id),

    constraint fk_account
        foreign key(account_id) references account(id)
);

create table pool (
    id          serial      not null,
    account_id  integer     not null,

    constraint pk_pool
        primary key(id),

    constraint fk_account
        foreign key(account_id) references account(id)
);

create table pool_invite (
    pool_id     integer not null,
    account_id  integer not null,

    constraint pk_pool_invite
        primary key(pool_id, account_id),

    constraint fk_pool
        foreign key(pool_id) references pool(id),

    constraint fk_account
        foreign key(account_id) references account(id)
);

create table pool_invite_result (
    pool_invite_pool_id     integer not null,
    pool_invite_account_id  integer not null,
    result_id               integer not null,
    added                   timestamptz not null default current_timestamp,

    constraint pk_pool_invite_result
        primary key(pool_invite_pool_id, pool_invite_account_id, result_id),

    constraint fk_result
        foreign key(result_id) references result(id),

    constraint fk_pool_invite_pool_id
        foreign key(pool_invite_pool_id, pool_invite_account_id)
        references pool_invite(pool_id, account_id)
);