3 files changed, 201 insertions, 0 deletions
diff --git a/db/create_db.sh b/db/create_db.sh
new file mode 100755
index 0000000..adbc245
--- /dev/null
+++ b/db/create_db.sh
@@ -0,0 +1,22 @@
+set -e -u -x
+
+if [ -e "$1" ]; then
+	echo "Database \"$1\" already exists. Doing nothing."
+	exit 0
+fi
+
+mkdir --parent -- "$1"
+
+# Create database cluster
+initdb --auth-local=trust --username=postgres -- "$1"
+
+# Start DB for next operation...
+pg_ctl -D "$1" start
+trap 'pg_ctl -D "$1" stop' EXIT
+
+# Create a database within said cluster called 'testdb'.
+# TODO: We should restore a "backup" which contains the initial state for testing (i.e. with assignments and existing users).
+createdb --username=postgres --no-password --echo testdb "Default database for testing".
+
+# Load DB dump
+psql --username=postgres --dbname=testdb <./db/dump.sql
diff --git a/db/dump.sql b/db/dump.sql
new file mode 100644
index 0000000..5cb3a51
--- /dev/null
+++ b/db/dump.sql
@@ -0,0 +1,177 @@
+--
+-- 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: 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: 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: 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
+\.
+
+
+--
+-- 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	[email protected]
+2	gardener	Sarah	Hækkesen	$2a$06$RyzzMad/jmKZwg9hRDymCO/KvxiSUFcdYQZdxB9trHHOU3tKhtFIK	sarah.hækkesen@kirkefætrene.dk
+3	gardener	Sten	Graversen	$2a$06$/InJ2HFv1/z0YVXMwoHWZuV7aTlWl3GH9csKEvN5gb4nEPSECEdFq	[email protected]
+\.
+
+
+--
+-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
+--
+
+SELECT pg_catalog.setval('public.users_id_seq', 3, true);
+
+
+--
+-- 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: 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: 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
+--
+
diff --git a/db/run_db.sh b/db/run_db.sh
new file mode 100755
index 0000000..3fdf58f
--- /dev/null
+++ b/db/run_db.sh
@@ -0,0 +1,2 @@
+set -ue
+postgres -D "$1" -c listen_addresses=localhost