postgres-init.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;


--
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


--
-- Name: nanoid(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.nanoid(size integer DEFAULT 21, alphabet text DEFAULT '_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'::text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    idBuilder     text := '';
    i             int  := 0;
    bytes         bytea;
    alphabetIndex int;
    mask          int;
    step          int;
BEGIN
    mask := (2 << cast(floor(log(length(alphabet) - 1) / log(2)) as int)) - 1;
    step := cast(ceil(1.6 * mask * size / length(alphabet)) AS int);

    while true
        loop
            bytes := gen_random_bytes(size);
            while i < size
                loop
                    alphabetIndex := (get_byte(bytes, i) & mask) + 1;
                    if alphabetIndex <= length(alphabet) then
                        idBuilder := idBuilder || substr(alphabet, alphabetIndex, 1);
                        if length(idBuilder) = size then
                            return idBuilder;
                        end if;
                    end if;
                    i = i + 1;
                end loop;

            i := 0;
        end loop;
END
$$;


ALTER FUNCTION public.nanoid(size integer, alphabet text) OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: devices; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.devices (
    id character(21) DEFAULT public.nanoid() NOT NULL,
    user_id character(21) NOT NULL,
    trusted boolean DEFAULT false NOT NULL,
    hash bytea NOT NULL
);


ALTER TABLE public.devices OWNER TO postgres;

--
-- Name: group_join_invitations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.group_join_invitations (
    group_id character(21) NOT NULL,
    user_id character(21) NOT NULL,
    inviter_id character(21) NOT NULL,
    role text NOT NULL,
    encrypted_access_keyring bytea,
    encrypted_internal_keyring bytea NOT NULL,
    encrypted_name bytea NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    encrypted_name_for_user bytea
);


ALTER TABLE public.group_join_invitations OWNER TO postgres;

--
-- Name: group_join_requests; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.group_join_requests (
    group_id character(21) NOT NULL,
    user_id character(21) NOT NULL,
    rejected boolean DEFAULT false NOT NULL,
    encrypted_name bytea NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    encrypted_name_for_user bytea NOT NULL
);


ALTER TABLE public.group_join_requests OWNER TO postgres;

--
-- Name: group_members; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.group_members (
    user_id character(21) NOT NULL,
    group_id character(21) NOT NULL,
    encrypted_access_keyring bytea,
    role text NOT NULL,
    encrypted_internal_keyring bytea NOT NULL,
    last_activity_date timestamp with time zone DEFAULT now() NOT NULL,
    encrypted_name bytea,
    encrypted_name_for_user bytea
);


ALTER TABLE public.group_members OWNER TO postgres;

--
-- Name: groups; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.groups (
    id character(21) DEFAULT public.nanoid() NOT NULL,
    main_page_id character(21) NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    user_id character(21),
    encrypted_name bytea NOT NULL,
    public_keyring bytea NOT NULL,
    encrypted_private_keyring bytea NOT NULL,
    access_keyring bytea,
    encrypted_content_keyring bytea NOT NULL,
    permanent_deletion_date timestamp with time zone,
    encrypted_rehashed_password_hash bytea,
    are_join_requests_allowed boolean DEFAULT true NOT NULL
);


ALTER TABLE public.groups OWNER TO postgres;

--
-- Name: notifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.notifications (
    type text NOT NULL,
    datetime timestamp with time zone DEFAULT now() NOT NULL,
    encrypted_content bytea NOT NULL,
    id bigint NOT NULL
);


ALTER TABLE public.notifications OWNER TO postgres;

--
-- Name: notifications_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.notifications ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.notifications_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: page_links; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.page_links (
    target_page_id character(21) NOT NULL,
    source_page_id character(21) NOT NULL,
    last_activity_date timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.page_links OWNER TO postgres;

--
-- Name: page_snapshots; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.page_snapshots (
    page_id character(21) NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    encrypted_data bytea NOT NULL,
    author_id character(21),
    type text NOT NULL,
    encrypted_symmetric_key bytea,
    id character(21) DEFAULT public.nanoid() NOT NULL
);


ALTER TABLE public.page_snapshots OWNER TO postgres;

--
-- Name: page_updates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.page_updates (
    page_id character(21) NOT NULL,
    index bigint NOT NULL,
    encrypted_data bytea NOT NULL
);


ALTER TABLE public.page_updates OWNER TO postgres;

--
-- Name: pages; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.pages (
    id character(21) DEFAULT public.nanoid() NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    last_activity_date timestamp with time zone DEFAULT now() NOT NULL,
    group_id character(21) NOT NULL,
    encrypted_relative_title bytea NOT NULL,
    encrypted_symmetric_keyring bytea NOT NULL,
    free boolean,
    next_snapshot_update_index bigint DEFAULT 100 NOT NULL,
    next_snapshot_date timestamp with time zone DEFAULT (now() + '00:15:00'::interval) NOT NULL,
    next_key_rotation_date timestamp with time zone DEFAULT (now() + '7 days'::interval) NOT NULL,
    permanent_deletion_date timestamp with time zone,
    encrypted_absolute_title bytea NOT NULL
);


ALTER TABLE public.pages OWNER TO postgres;

--
-- Name: sessions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.sessions (
    id character(21) DEFAULT public.nanoid() NOT NULL,
    user_id character(21) NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    invalidated boolean DEFAULT false NOT NULL,
    device_id character(21) NOT NULL,
    last_refresh_date timestamp with time zone DEFAULT now() NOT NULL,
    expiration_date timestamp with time zone NOT NULL,
    encryption_key bytea NOT NULL,
    refresh_code character(21) NOT NULL
);


ALTER TABLE public.sessions OWNER TO postgres;

--
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users (
    id character(21) DEFAULT public.nanoid() NOT NULL,
    creation_date timestamp with time zone DEFAULT now() NOT NULL,
    starting_page_id character(21) NOT NULL,
    recent_page_ids character(21)[] DEFAULT '{}'::character(21)[] NOT NULL,
    personal_group_id character(21) NOT NULL,
    email_verified boolean DEFAULT false NOT NULL,
    public_keyring bytea NOT NULL,
    encrypted_private_keyring bytea NOT NULL,
    encrypted_symmetric_keyring bytea NOT NULL,
    encrypted_default_arrow bytea NOT NULL,
    encrypted_default_note bytea NOT NULL,
    two_factor_auth_enabled boolean DEFAULT false NOT NULL,
    email_verification_expiration_date timestamp with time zone,
    email_verification_code text,
    recent_group_ids character(21)[] DEFAULT '{}'::character(21)[] NOT NULL,
    last_notification_read bigint,
    customer_id text,
    plan text DEFAULT 'basic'::text NOT NULL,
    subscription_id text,
    encrypted_name bytea,
    num_free_pages integer DEFAULT 0 NOT NULL,
    encrypted_authenticator_secret bytea,
    encrypted_email bytea NOT NULL,
    encrypted_new_email bytea,
    encrypted_recovery_codes bytea,
    demo boolean,
    email_hash bytea NOT NULL,
    encrypted_rehashed_login_hash bytea NOT NULL,
    new boolean DEFAULT true NOT NULL
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Name: users_notifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users_notifications (
    user_id character(21) NOT NULL,
    encrypted_symmetric_key bytea NOT NULL,
    notification_id bigint NOT NULL
);


ALTER TABLE public.users_notifications OWNER TO postgres;

--
-- Name: users_pages; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users_pages (
    user_id character(21) NOT NULL,
    page_id character(21) NOT NULL,
    last_parent_id character(21)
);


ALTER TABLE public.users_pages OWNER TO postgres;

--
-- Name: devices devices_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.devices
    ADD CONSTRAINT devices_pkey PRIMARY KEY (id);


--
-- Name: group_join_invitations group_join_invitations_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_invitations
    ADD CONSTRAINT group_join_invitations_pkey PRIMARY KEY (group_id, user_id);


--
-- Name: group_join_requests group_join_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_requests
    ADD CONSTRAINT group_join_requests_pkey PRIMARY KEY (group_id, user_id);


--
-- Name: groups groups_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);


--
-- Name: group_members groups_users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_members
    ADD CONSTRAINT groups_users_pkey PRIMARY KEY (group_id, user_id);


--
-- Name: notifications notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notifications
    ADD CONSTRAINT notifications_pkey PRIMARY KEY (id);


--
-- Name: page_links page_links_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_links
    ADD CONSTRAINT page_links_pkey PRIMARY KEY (source_page_id, target_page_id);


--
-- Name: page_snapshots page_snapshots_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_snapshots
    ADD CONSTRAINT page_snapshots_pkey PRIMARY KEY (id);


--
-- Name: pages pages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.pages
    ADD CONSTRAINT pages_pkey PRIMARY KEY (id);


--
-- Name: page_updates pages_updates_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_updates
    ADD CONSTRAINT pages_updates_pkey PRIMARY KEY (page_id, index);


--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);


--
-- Name: users users_encrypted_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_encrypted_email_key UNIQUE (encrypted_email);


--
-- Name: users_notifications users_notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users_notifications
    ADD CONSTRAINT users_notifications_pkey PRIMARY KEY (user_id, notification_id);


--
-- Name: users_pages users_pages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users_pages
    ADD CONSTRAINT users_pages_pkey PRIMARY KEY (user_id, page_id);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: group_join_invitations_user_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX group_join_invitations_user_id_idx ON public.group_join_invitations USING btree (user_id, creation_date DESC);


--
-- Name: group_join_requests_user_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX group_join_requests_user_id_idx ON public.group_join_requests USING btree (user_id, creation_date DESC);


--
-- Name: group_members_user_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX group_members_user_id_idx ON public.group_members USING btree (user_id, last_activity_date DESC);


--
-- Name: page_links_target_page_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX page_links_target_page_id_idx ON public.page_links USING btree (target_page_id, last_activity_date DESC);


--
-- Name: sessions_refresh_code_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sessions_refresh_code_idx ON public.sessions USING btree (refresh_code);


--
-- Name: users_creation_date_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX users_creation_date_idx ON public.users USING btree (creation_date DESC);


--
-- Name: users_customer_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX users_customer_id_idx ON public.users USING btree (customer_id);


--
-- Name: users_email_hash_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX users_email_hash_idx ON public.users USING btree (email_hash);


--
-- Name: users_pages_page_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX users_pages_page_id_idx ON public.users_pages USING btree (page_id);


--
-- Name: devices devices_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.devices
    ADD CONSTRAINT devices_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- Name: group_join_invitations group_join_invitations_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_invitations
    ADD CONSTRAINT group_join_invitations_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: group_join_invitations group_join_invitations_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_invitations
    ADD CONSTRAINT group_join_invitations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: group_join_requests group_join_requests_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_requests
    ADD CONSTRAINT group_join_requests_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: group_join_requests group_join_requests_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_join_requests
    ADD CONSTRAINT group_join_requests_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: groups groups_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_owner_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: group_members groups_users_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_members
    ADD CONSTRAINT groups_users_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: group_members groups_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.group_members
    ADD CONSTRAINT groups_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- Name: page_links page_links_source_page_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_links
    ADD CONSTRAINT page_links_source_page_id_fkey FOREIGN KEY (source_page_id) REFERENCES public.pages(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- Name: page_links page_links_target_page_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_links
    ADD CONSTRAINT page_links_target_page_id_fkey FOREIGN KEY (target_page_id) REFERENCES public.pages(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- Name: page_snapshots page_snapshots_page_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_snapshots
    ADD CONSTRAINT page_snapshots_page_id_fkey FOREIGN KEY (page_id) REFERENCES public.pages(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: pages pages_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.pages
    ADD CONSTRAINT pages_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- Name: page_updates pages_updates_page_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.page_updates
    ADD CONSTRAINT pages_updates_page_id_fkey FOREIGN KEY (page_id) REFERENCES public.pages(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: sessions sessions_device_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.devices(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: sessions sessions_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: users_notifications users_notifications_notification_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users_notifications
    ADD CONSTRAINT users_notifications_notification_id_fkey FOREIGN KEY (notification_id) REFERENCES public.notifications(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: users_notifications users_notifications_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users_notifications
    ADD CONSTRAINT users_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE NOT VALID;


--
-- Name: users_pages users_pages_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users_pages
    ADD CONSTRAINT users_pages_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--