schema.sql

--
-- PostgreSQL database dump
--

\restrict pIT2EI3DAwl6M92sx6PJ2Ykeoy4UzqxYdSYR8LMncOzSbBzlOF8ho5j57o0UITE

-- Dumped from database version 15.14 (Debian 15.14-1.pgdg13+1)
-- Dumped by pg_dump version 15.14 (Debian 15.14-1.pgdg13+1)

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: app_status; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.app_status AS ENUM (
    'active',
    'inactive',
    'blocked',
    'deleted',
    'unverified'
);


ALTER TYPE public.app_status OWNER TO sso_admin;

--
-- Name: available_fields; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.available_fields AS ENUM (
    'email',
    'username',
    'language',
    'theme',
    'profile_pic',
    'date_of_birth',
    'gender',
    'phone_numbers',
    'addresses',
    'full_name',
    'preferred_first_name'
);


ALTER TYPE public.available_fields OWNER TO sso_admin;

--
-- Name: gender; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.gender AS ENUM (
    'male',
    'female',
    'other',
    'prefer not to say'
);


ALTER TYPE public.gender OWNER TO sso_admin;

--
-- Name: language; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.language AS ENUM (
    'en'
);


ALTER TYPE public.language OWNER TO sso_admin;

--
-- Name: theme; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.theme AS ENUM (
    'light',
    'dark',
    'automatic',
    'app-default'
);


ALTER TYPE public.theme OWNER TO sso_admin;

--
-- Name: user_status; Type: TYPE; Schema: public; Owner: sso_admin
--

CREATE TYPE public.user_status AS ENUM (
    'active',
    'inactive',
    'deleted',
    'unverified'
);


ALTER TYPE public.user_status OWNER TO sso_admin;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: addresses; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.addresses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid,
    line1 character varying NOT NULL,
    line2 character varying,
    city character varying,
    state character varying,
    postal_code character varying,
    country character varying,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    default_address boolean
);


ALTER TABLE public.addresses OWNER TO sso_admin;

--
-- Name: apps; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.apps (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    client_name text NOT NULL,
    scopes public.available_fields[] DEFAULT ARRAY['email'::public.available_fields],
    created_at timestamp without time zone DEFAULT now(),
    status public.app_status DEFAULT 'unverified'::public.app_status,
    secret text,
    logo text,
    CONSTRAINT apps_scopes_check CHECK (('email'::public.available_fields = ANY (scopes)))
);


ALTER TABLE public.apps OWNER TO sso_admin;

--
-- Name: otp; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.otp (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid,
    code character(6) NOT NULL,
    created_at timestamp without time zone DEFAULT now(),
    expires_at timestamp without time zone DEFAULT (now() + '00:10:00'::interval)
);


ALTER TABLE public.otp OWNER TO sso_admin;

--
-- Name: phones; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.phones (
    id uuid NOT NULL,
    user_id uuid,
    country_code text NOT NULL,
    number text NOT NULL,
    verified boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    default_phone boolean
);


ALTER TABLE public.phones OWNER TO sso_admin;

--
-- Name: refresh_tokens; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.refresh_tokens (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    token text NOT NULL,
    user_id uuid NOT NULL,
    app_id uuid,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    expires_at timestamp without time zone DEFAULT (CURRENT_TIMESTAMP + '30 days'::interval) NOT NULL
);


ALTER TABLE public.refresh_tokens OWNER TO sso_admin;

--
-- Name: usernames; Type: TABLE; Schema: public; Owner: sso_admin
--

CREATE TABLE public.usernames (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    current boolean NOT NULL,
    username text NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.usernames OWNER TO sso_admin;

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

CREATE TABLE public.users (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    email text NOT NULL,
    password text NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    status public.user_status DEFAULT 'unverified'::public.user_status,
    profile_pic text,
    language public.language DEFAULT 'en'::public.language,
    theme public.theme DEFAULT 'app-default'::public.theme,
    date_of_birth date,
    gender public.gender,
    full_name text,
    preferred_first_name text
);


ALTER TABLE public.users OWNER TO sso_admin;

--
-- Data for Name: addresses; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.addresses (id, user_id, line1, line2, city, state, postal_code, country, created_at, default_address) FROM stdin;
\.


--
-- Data for Name: apps; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.apps (id, client_name, scopes, created_at, status, secret, logo) FROM stdin;
a0f9ce02-f29a-40ce-abe5-b23fdc9fed87	DevUs AMS	{email,username,language,theme,profile_pic,date_of_birth,gender,phone_numbers,addresses,full_name,preferred_first_name}	2025-11-14 20:35:16.308948	active	48a3d9e0b12d1d0b6a85baff38fa72e03ed7f6b0d0f50658369719057fd0f2f6	\N
\.


--
-- Data for Name: otp; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.otp (id, user_id, code, created_at, expires_at) FROM stdin;
\.


--
-- Data for Name: phones; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.phones (id, user_id, country_code, number, verified, created_at, default_phone) FROM stdin;
\.


--
-- Data for Name: refresh_tokens; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.refresh_tokens (id, token, user_id, app_id, created_at, expires_at) FROM stdin;
\.


--
-- Data for Name: usernames; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.usernames (id, user_id, current, username, created_at) FROM stdin;
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: sso_admin
--

COPY public.users (id, email, password, created_at, status, profile_pic, language, theme, date_of_birth, gender, full_name, preferred_first_name) FROM stdin;
\.


--
-- Name: addresses addresses_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.addresses
    ADD CONSTRAINT addresses_pkey PRIMARY KEY (id);


--
-- Name: apps apps_client_name_key; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.apps
    ADD CONSTRAINT apps_client_name_key UNIQUE (client_name);


--
-- Name: apps apps_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.apps
    ADD CONSTRAINT apps_pkey PRIMARY KEY (id);


--
-- Name: otp otp_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.otp
    ADD CONSTRAINT otp_pkey PRIMARY KEY (id);


--
-- Name: phones phones_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.phones
    ADD CONSTRAINT phones_pkey PRIMARY KEY (id);


--
-- Name: refresh_tokens refresh_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.refresh_tokens
    ADD CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id);


--
-- Name: refresh_tokens refresh_tokens_token_key; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.refresh_tokens
    ADD CONSTRAINT refresh_tokens_token_key UNIQUE (token);


--
-- Name: usernames usernames_pkey; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.usernames
    ADD CONSTRAINT usernames_pkey PRIMARY KEY (id);


--
-- Name: usernames usernames_username_key; Type: CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.usernames
    ADD CONSTRAINT usernames_username_key UNIQUE (username);


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

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


--
-- Name: unique_active_email; Type: INDEX; Schema: public; Owner: sso_admin
--

CREATE UNIQUE INDEX unique_active_email ON public.users USING btree (email) WHERE (status <> 'deleted'::public.user_status);


--
-- Name: unique_current_username_per_user; Type: INDEX; Schema: public; Owner: sso_admin
--

CREATE UNIQUE INDEX unique_current_username_per_user ON public.usernames USING btree (user_id) WHERE (current = true);


--
-- Name: unique_default_address_per_user; Type: INDEX; Schema: public; Owner: sso_admin
--

CREATE UNIQUE INDEX unique_default_address_per_user ON public.addresses USING btree (user_id) WHERE (default_address = true);


--
-- Name: unique_default_phone_per_user; Type: INDEX; Schema: public; Owner: sso_admin
--

CREATE UNIQUE INDEX unique_default_phone_per_user ON public.phones USING btree (user_id) WHERE (default_phone = true);


--
-- Name: addresses addresses_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.addresses
    ADD CONSTRAINT addresses_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: otp otp_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.otp
    ADD CONSTRAINT otp_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: phones phones_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.phones
    ADD CONSTRAINT phones_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: refresh_tokens refresh_tokens_app_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.refresh_tokens
    ADD CONSTRAINT refresh_tokens_app_id_fkey FOREIGN KEY (app_id) REFERENCES public.apps(id) ON DELETE CASCADE;


--
-- Name: refresh_tokens refresh_tokens_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.refresh_tokens
    ADD CONSTRAINT refresh_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: usernames usernames_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: sso_admin
--

ALTER TABLE ONLY public.usernames
    ADD CONSTRAINT usernames_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- PostgreSQL database dump complete
--

\unrestrict pIT2EI3DAwl6M92sx6PJ2Ykeoy4UzqxYdSYR8LMncOzSbBzlOF8ho5j57o0UITE