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

• [x] The table and column names can revised to make them more descriptive.
  • Any column in particular you have in mind?
    • Ahmed: I was referring to the following columns org_number_ak, latest_change_number, exported and reservation. It is not immediately apparent what their purpose is at first glance.
    • Ahmed: I also find the schema name ‘krr’ to be quite brief.
• [x] To ensure data integrity, adding a unique constraint on the mailbox_supplier table will guarantee that each supplier has a distinct identifier.
  • Good idea.
    • Ahmed: I updated the table on Tuesday, October 8, 2024.
• [x] 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.
  • We can revisit this once we start looking at the batch job and what we need for the batch job.
• [x] 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.
  • I want us to follow the definition of KRR if that is possible. We can revisit this when we start looking at the KRR documentation.
• [x] 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.
  • I believe the data comes from KRR. Not something we produce.
• [x] 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.
  • I think that is unnecessary. Mailbox supplier might change, but it would require all users to be migrated to another supplier. I don't know how that is supposed to happen, but I hope there are zero persons left once the supplier is deleted. If not there is a bug.

[Ahmed-Ghanam]

The database script:

-- 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;

-- Grant access to the schema
GRANT ALL ON SCHEMA contact_and_reservation TO platform_profile_admin;
GRANT USAGE ON SCHEMA contact_and_reservation TO platform_profile;

-- 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,
    CONSTRAINT unique_org_number_ak UNIQUE (org_number_ak)
);

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

-- Create table Person
CREATE TABLE IF NOT EXISTS contact_and_reservation.person (
    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}$')
);

-- Indexes for performance
CREATE INDEX idx_fnumber_ak ON contact_and_reservation.person (fnumber_ak);

[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".

[SandGrainOne]

We can rename contact_and_reservation.register to contact_and_reservation.person. That is a better entity name for the table and makes it easier to create a logical name for the id: person_id

[SandGrainOne]

I've commented on most of the ideas you have @Ahmed-Ghanam

[Ahmed-Ghanam]

Til info @SandGrainOne:

• I updated the script to include a UNIQUE constraint on the mailbox_supplier.org_number_ak.
• I changed the table name registerto person.
• I created two indexes for performance on the person table: idx_mailbox_supplier_id_fkand idx_fnumber_ak.

[SandGrainOne]

We will use your schema name suggestion: contact_and_reservation. It's going to be a headache finding a schema name for "Offisiell varslingsinformasjon for virksomhet", but eh.

reservation is a bit hard to describe in a few words. People in Norway can use reservation to force the Norwegian goverment and agencies to use analog forms of communication with them. Like if you want to have your tax card on paper instead of digitally.

org_number_ak, organization number. There are a few suppliers of digital mail boxes and this column describes the organization number of those suppliers.

The latest_change_number and exported are used by the batch/synchronization job that reads from KRR. It's needed so that we keep track of were we are in the KRR change log. Maybe the solution is to rename the table? Instead of metadata. we could call it sync_data or something.

[Ahmed-Ghanam]

We will use your schema name suggestion: contact_and_reservation. It's going to be a headache finding a schema name for "Offisiell varslingsinformasjon for virksomhet", but eh.

reservation is a bit hard to describe in a few words. People in Norway can use reservation to force the Norwegian goverment and agencies to use analog forms of communication with them. Like if you want to have your tax card on paper instead of digitally.

org_number_ak, organization number. There are a few suppliers of digital mail boxes and this column describes the organization number of those suppliers.

The latest_change_number and exported are used by the batch/synchronization job that reads from KRR. It's needed so that we keep track of were we are in the KRR change log. Maybe the solution is to rename the table? Instead of metadata. we could call it sync_data or something.

I think we can go forward with these names as they are for now.