schema.psql

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1
-- Dumped by pg_dump version 14.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: claim_type; Type: TYPE; Schema: public; Owner: kranzky
--

CREATE TYPE public.claim_type AS ENUM (
    'email',
    'phone'
);


ALTER TYPE public.claim_type OWNER TO kranzky;

--
-- Name: punk_state; Type: TYPE; Schema: public; Owner: kranzky
--

CREATE TYPE public.punk_state AS ENUM (
    'created',
    'active',
    'deleted'
);


ALTER TYPE public.punk_state OWNER TO kranzky;

--
-- Name: session_state; Type: TYPE; Schema: public; Owner: kranzky
--

CREATE TYPE public.session_state AS ENUM (
    'pending',
    'created',
    'active',
    'deleted',
    'expired'
);


ALTER TYPE public.session_state OWNER TO kranzky;

SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.groups (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    state public.punk_state DEFAULT 'created'::public.punk_state NOT NULL,
    name text NOT NULL,
    icon text,
    data jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    tenant_id uuid NOT NULL
);


ALTER TABLE public.groups OWNER TO kranzky;

--
-- Name: groups_users; Type: TABLE; Schema: public; Owner: kranzky
--

CREATE TABLE public.groups_users (
    group_id uuid NOT NULL,
    user_id uuid NOT NULL
);


ALTER TABLE public.groups_users OWNER TO kranzky;

--
-- Name: identities; Type: TABLE; Schema: public; Owner: kranzky
--

CREATE TABLE public.identities (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    state public.punk_state DEFAULT 'created'::public.punk_state NOT NULL,
    claim_type public.claim_type NOT NULL,
    claim text NOT NULL,
    data jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    user_id uuid
);


ALTER TABLE public.identities OWNER TO kranzky;

--
-- Name: schema_info; Type: TABLE; Schema: public; Owner: kranzky
--

CREATE TABLE public.schema_info (
    version integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.schema_info OWNER TO kranzky;

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

CREATE TABLE public.sessions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    slug uuid DEFAULT gen_random_uuid(),
    state public.session_state DEFAULT 'created'::public.session_state NOT NULL,
    salt bytea,
    hash bytea,
    attempt_count integer DEFAULT 0 NOT NULL,
    remote_addr cidr DEFAULT '127.0.0.1/32'::cidr NOT NULL,
    user_agent text DEFAULT 'Mozilla/5.0 (compatible; Punk!; +https://punk.kranzky.com)'::text NOT NULL,
    data jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    identity_id uuid NOT NULL
);


ALTER TABLE public.sessions OWNER TO kranzky;

--
-- Name: tenants; Type: TABLE; Schema: public; Owner: kranzky
--

CREATE TABLE public.tenants (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    state public.punk_state DEFAULT 'created'::public.punk_state NOT NULL,
    name text NOT NULL,
    icon text,
    data jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone
);


ALTER TABLE public.tenants OWNER TO kranzky;

--
-- Name: tenants_users; Type: TABLE; Schema: public; Owner: kranzky
--

CREATE TABLE public.tenants_users (
    tenant_id uuid NOT NULL,
    user_id uuid NOT NULL
);


ALTER TABLE public.tenants_users OWNER TO kranzky;

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

CREATE TABLE public.users (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    state public.punk_state DEFAULT 'created'::public.punk_state NOT NULL,
    name text NOT NULL,
    icon text,
    email text,
    phone text,
    data jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone
);


ALTER TABLE public.users OWNER TO kranzky;

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

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


--
-- Name: groups_users groups_users_pkey; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

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


--
-- Name: identities identities_claim_key; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.identities
    ADD CONSTRAINT identities_claim_key UNIQUE (claim);


--
-- Name: identities identities_pkey; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.identities
    ADD CONSTRAINT identities_pkey PRIMARY KEY (id);


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

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


--
-- Name: tenants tenants_pkey; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.tenants
    ADD CONSTRAINT tenants_pkey PRIMARY KEY (id);


--
-- Name: tenants_users tenants_users_pkey; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.tenants_users
    ADD CONSTRAINT tenants_users_pkey PRIMARY KEY (tenant_id, user_id);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- Name: users users_phone_key; Type: CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_phone_key UNIQUE (phone);


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

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


--
-- Name: groups_users_group_id_user_id_index; Type: INDEX; Schema: public; Owner: kranzky
--

CREATE INDEX groups_users_group_id_user_id_index ON public.groups_users USING btree (group_id, user_id);


--
-- Name: tenants_users_tenant_id_user_id_index; Type: INDEX; Schema: public; Owner: kranzky
--

CREATE INDEX tenants_users_tenant_id_user_id_index ON public.tenants_users USING btree (tenant_id, user_id);


--
-- Name: groups groups_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id);


--
-- Name: groups_users groups_users_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.groups_users
    ADD CONSTRAINT groups_users_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id);


--
-- Name: groups_users groups_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

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


--
-- Name: identities identities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

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


--
-- Name: sessions sessions_identity_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_identity_id_fkey FOREIGN KEY (identity_id) REFERENCES public.identities(id);


--
-- Name: tenants_users tenants_users_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

ALTER TABLE ONLY public.tenants_users
    ADD CONSTRAINT tenants_users_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id);


--
-- Name: tenants_users tenants_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: kranzky
--

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


--
-- PostgreSQL database dump complete
--