-- -- PostgreSQL database dump -- -- Dumped from database version 17.2 -- Dumped by pg_dump version 17.2 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET transaction_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: assignment_state; Type: TYPE; Schema: public; Owner: postgres -- CREATE TYPE public.assignment_state AS ENUM ( 'AWAITING_GARDENER_NOTIFICATION', 'AWAITING_FINISH', 'AWAITING_WATERMARKING', 'AWAITING_OWNER_NOTIFICATION', 'DONE' ); ALTER TYPE public.assignment_state OWNER TO postgres; -- -- Name: TYPE assignment_state; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON TYPE public.assignment_state IS 'The states for an assignment.'; -- -- Name: role; Type: TYPE; Schema: public; Owner: postgres -- CREATE TYPE public.role AS ENUM ( 'gardener', 'owner' ); ALTER TYPE public.role OWNER TO postgres; -- -- Name: TYPE role; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON TYPE public.role IS 'The role of a user within the system.'; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: assignments; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.assignments ( id integer NOT NULL, gardener_id integer NOT NULL, cemetary_plot_id integer NOT NULL, date date, state public.assignment_state DEFAULT 'AWAITING_GARDENER_NOTIFICATION'::public.assignment_state NOT NULL, note text ); ALTER TABLE public.assignments OWNER TO postgres; -- -- Name: COLUMN assignments.note; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.assignments.note IS 'When an assignment is finished, the gardener can supply a note.'; -- -- Name: assignments_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- ALTER TABLE public.assignments ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( SEQUENCE NAME public.assignments_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); -- -- Name: cemetary_plots; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.cemetary_plots ( id integer NOT NULL, address text NOT NULL, owner_id integer NOT NULL, assignment_interval interval day DEFAULT '14 days'::interval NOT NULL ); ALTER TABLE public.cemetary_plots OWNER TO postgres; -- -- Name: COLUMN cemetary_plots.assignment_interval; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.cemetary_plots.assignment_interval IS 'The interval between when cleaning assignments. If there was a job at time N, the next one would be at N + assignment_interval.'; -- -- Name: cementary_plots_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- ALTER TABLE public.cemetary_plots ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( SEQUENCE NAME public.cementary_plots_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); -- -- Name: images; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.images ( id integer NOT NULL, s3_path text NOT NULL, original_filename text NOT NULL, assignment_id integer ); ALTER TABLE public.images OWNER TO postgres; -- -- Name: TABLE images; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON TABLE public.images IS 'This table stores the images uploaded when finishing an assignment.'; -- -- Name: COLUMN images.assignment_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.images.assignment_id IS 'Reference to the owning assignment'; -- -- Name: images_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- ALTER TABLE public.images ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( SEQUENCE NAME public.images_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); -- -- Name: sessions; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.sessions ( token text NOT NULL, user_id integer NOT NULL, expires_at timestamp with time zone NOT NULL ); ALTER TABLE public.sessions OWNER TO postgres; -- -- Name: users; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.users ( id integer NOT NULL, role public.role NOT NULL, first_name text NOT NULL, last_name text NOT NULL, password_hash text NOT NULL, email text ); ALTER TABLE public.users OWNER TO postgres; -- -- Name: COLUMN users.password_hash; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.users.password_hash IS 'Hash of password + salt.'; -- -- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( SEQUENCE NAME public.users_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); -- -- Data for Name: assignments; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.assignments (id, gardener_id, cemetary_plot_id, date, state, note) FROM stdin; 2 1 2 2025-03-05 AWAITING_GARDENER_NOTIFICATION \N 1 1 1 2025-02-28 AWAITING_WATERMARKING \N \. -- -- Data for Name: cemetary_plots; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.cemetary_plots (id, address, owner_id, assignment_interval) FROM stdin; 1 24B, Kirkevej 64, Egå 8328 4 21 days 2 26C, Kirkevej 64, Egå 8328 5 14 days \. -- -- Data for Name: images; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.images (id, s3_path, original_filename, assignment_id) FROM stdin; 3 fe5e7c27-99c4-4d4b-ba77-fcad5c6607e3 eva.jpeg 1 \. -- -- Data for Name: sessions; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.sessions (token, user_id, expires_at) FROM stdin; je2bic5i5jou7woob4ehreoqojuir4co 1 2025-03-19 18:04:35.904+01 27ghlb4vzh543kxpnkniip2oai6q4dvx 1 2025-03-19 18:04:47.722+01 bjt7wgbfrlzrg7fhphsxezpc7fornezv 1 2025-03-19 20:42:57.838+01 bgrgdtxmiq44ig6dh32pop4wkterqj6r 1 2025-03-20 18:03:54.199+01 q6ifvlkjdoncw5usnb37da3sf4y3fxyq 1 2025-03-20 20:01:53.318+01 \. -- -- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.users (id, role, first_name, last_name, password_hash, email) FROM stdin; 1 gardener Emil Blomst $2a$06$vw1iVTLDQzeUwmhyW17ZzeJL.gyC4xWWzAwvJO8dsZi7Jdg2w5zbu emil@eksempel.dk 2 gardener Sarah Hækkesen $2a$06$RyzzMad/jmKZwg9hRDymCO/KvxiSUFcdYQZdxB9trHHOU3tKhtFIK sarah.hækkesen@kirkefætrene.dk 3 gardener Sten Graversen $2a$06$/InJ2HFv1/z0YVXMwoHWZuV7aTlWl3GH9csKEvN5gb4nEPSECEdFq wad@gaidefar.com 4 owner Lotte Ejersen $2a$06$C1JTdp2y9lIO57S7kGsBruE2oLj87AYpNNrZqx/5Zgap5lqcDj.my l.ejersen@eksempel.dk 5 owner Mads Hylgaard Jensen $2a$06$FuFHsoFo1cPJ9ms9TceVG..2BAHAyJpE2NyBUTH9JnG6v8GWuOh4u mads@wraaath.xyz 6 owner Lukas Ibasen $2a$06$p2nOFXtLC0YE/7TTxqzUOeEfpPKVjVZr2sge1KBFT/7lrx2rpsvO2 contact@ibsenware.xyz \. -- -- Name: assignments_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.assignments_id_seq', 2, true); -- -- Name: cementary_plots_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.cementary_plots_id_seq', 2, true); -- -- Name: images_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.images_id_seq', 3, true); -- -- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.users_id_seq', 6, true); -- -- Name: assignments assignments_id_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.assignments ADD CONSTRAINT assignments_id_pkey PRIMARY KEY (id); -- -- Name: cemetary_plots cemetary_plots_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.cemetary_plots ADD CONSTRAINT cemetary_plots_pkey PRIMARY KEY (id); -- -- Name: users email_uniqueness; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT email_uniqueness UNIQUE (email); -- -- Name: CONSTRAINT email_uniqueness ON users; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON CONSTRAINT email_uniqueness ON public.users IS 'Multiple users cannot have the same email.'; -- -- Name: images images_s3_path_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.images ADD CONSTRAINT images_s3_path_key UNIQUE (s3_path); -- -- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.sessions ADD CONSTRAINT sessions_pkey PRIMARY KEY (token); -- -- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT users_pkey PRIMARY KEY (id); -- -- Name: assignments assignments_cemetary_plot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.assignments ADD CONSTRAINT assignments_cemetary_plot_id_fkey FOREIGN KEY (cemetary_plot_id) REFERENCES public.cemetary_plots(id); -- -- Name: assignments assignments_gardener_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.assignments ADD CONSTRAINT assignments_gardener_id_fkey FOREIGN KEY (gardener_id) REFERENCES public.users(id); -- -- Name: cemetary_plots cemetary_plots_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.cemetary_plots ADD CONSTRAINT cemetary_plots_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id); -- -- Name: images images_assignment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.images ADD CONSTRAINT images_assignment_id_fkey FOREIGN KEY (assignment_id) REFERENCES public.assignments(id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: sessions sessions_userid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.sessions ADD CONSTRAINT sessions_userid_fkey FOREIGN KEY (user_id) REFERENCES public.users(id); -- -- PostgreSQL database dump complete --