Establish KRR DB schema

[olebhansen]

Description

Current plan is to copy the schema/structure from Altinn2. Use Entity Framework to import the schemas defined in Altinn 2. Posibly only 3 tables.

There should also be a column for user preferred language. I believe it's a new value Altinn 2 never added support for.

Acceptance criteria:

• KRR Schema deployed to the DB, ready to recieve data and queries.

Recommendations for improvement

• The table and column names can revised to make them more descriptive.
• To ensure data integrity, adding a unique constraint on the mailbox_supplier table will guarantee that each supplier has a distinct identifier.
• For the exported column in the contact_and_reservation.metadata table, we can set a default value of the current timestamp to streamline data entry.
• As email addresses have a maximum allowable length of 255 characters based on internet standards, we can reduce the size of the contact_and_reservation.register.email_address field to 255 characters to optimize storage.
• To ensure that the mobile_phone_number_last_updated and email_address_last_updated fields are consistently updated, implementing a trigger to automatically set these values upon record changes would reduce the chance of manual errors.
• Adding an ON DELETE CASCADE action for the mailbox_supplier_id_fk foreign key in the contact_and_reservation.register table, will help maintain referential integrity.

[Ahmed-Ghanam]

We have initiated the database server order, which is currently being processed. In the meantime, I am providing the transcript below:

-- Drop the database if it exists
DROP DATABASE IF EXISTS profiledb;

-- Create the database
CREATE DATABASE profiledb
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Norwegian_Norway.1252'
    LC_CTYPE = 'Norwegian_Norway.1252'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS contact_and_reservation;

-- Create table MailboxSupplier
CREATE TABLE IF NOT EXISTS contact_and_reservation.mailbox_supplier (
    mailbox_supplier_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    org_number_ak CHAR(9) NOT NULL
);

-- Create table Metadata
CREATE TABLE IF NOT EXISTS contact_and_reservation.metadata (
    latest_change_number BIGINT PRIMARY KEY,
    exported TIMESTAMPTZ
);

-- Create table Register
CREATE TABLE IF NOT EXISTS contact_and_reservation.register (
    contact_and_reservation_user_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    fnumber_ak CHAR(11) NOT NULL UNIQUE,
    reservation BOOLEAN,
    description VARCHAR(20),
    mobile_phone_number VARCHAR(20),
    mobile_phone_number_last_updated TIMESTAMPTZ,
    mobile_phone_number_last_verified TIMESTAMPTZ,
    email_address VARCHAR(400),
    email_address_last_updated TIMESTAMPTZ,
    email_address_last_verified TIMESTAMPTZ,
    mailbox_address VARCHAR(50),
    mailbox_supplier_id_fk INT,
    x509_certificate TEXT,
    language_code CHAR(2) NULL,
    CONSTRAINT fk_mailbox_supplier FOREIGN KEY (mailbox_supplier_id_fk) REFERENCES contact_and_reservation.mailbox_supplier (mailbox_supplier_id),
    CONSTRAINT chk_language_code CHECK (language_code ~* '^[a-z]{2}$')
);

[Ahmed-Ghanam]

We follow the database-first approach. However, if we decide to go with a code-first approach, I can create the data models, configure the DbContext, write migrations, and use an in-memory database for testing.

[SandGrainOne]

I want a shorter schema name. I was thinking about simply "krr".