init.sql

-- Create user table
CREATE TABLE organization_t (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    saml_enabled boolean,
    saml_metadata_idp text,
    saml_metadata_sp text,
    saml_private_key text,
    saml_attr_mapping text
);
CREATE TABLE user_t (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    username varchar(256) NOT NULL,
    member_number varchar(256),
    saml_id varchar(256) CONSTRAINT minlength_saml_id CHECK (char_length(saml_id) >= 1),
    pronoun varchar(32),
    title varchar(256),
    first_name varchar(256),
    last_name varchar(256),
    is_active boolean DEFAULT True,
    is_physical_person boolean DEFAULT True,
    password varchar(256),
    default_password varchar(256),
    can_change_own_password boolean DEFAULT True,
    email varchar(256),
    default_vote_weight decimal(16,6) CONSTRAINT minimum_default_vote_weight CHECK (default_vote_weight >= 0.000001) DEFAULT '1.000000',
    last_email_sent timestamptz,
    is_demo_user boolean,
    last_login timestamptz,
    external boolean,
    gender_id integer,
    organization_management_level varchar(256) CONSTRAINT enum_user_organization_management_level CHECK (organization_management_level IN ('superadmin', 'can_manage_organization', 'can_manage_users')),
    home_committee_id integer,
    organization_id integer GENERATED ALWAYS AS (1) STORED NOT NULL
);