Database design

VEMAP maakt gebruik van een relationele database gebouwd op PostgreSQL. Deze keuze is gemaakt in lijn met de gebruikelijke werkwijze van MAPtm en de eisen van de applicatie: onderhoud baarheid, overdraagbaarheid en schaalbaarheid.

Design principes

Data normalisatie

De database is ontworpen volgens Third Normal Form (3NF) principes:

1NF: Alle kolommen bevatten atomaire waarden
2NF: Geen partiële afhankelijkheden van samengestelde keys
3NF: Geen transitieve afhankelijkheden tussen kolommen

Voordelen:

Minimale data redundantie
Consistente data updates****
Bescherming tegen data anomalieën
Eenvoudiger onderhoud

Multi-tenancy

De database ondersteunt multi-tenancy via tenants (voorheen client_environment):

Isolatie: Elke client heeft eigen environment identifier
Data segregatie: Queries filteren automatisch op tenant_id
Schaalbaarheid: Nieuwe clients zonder schema wijzigingen
Security: Row-level security door applicatie layer

Schema Scheiding

De database gebruikt twee schema’s voor scheiding tussen productie en ontwikkelomgeving:

public - Productie/klantdata
development - Development/testdata

Deze scheiding zorgt voor:

Data integriteit: Klantdata wordt beschermd tijdens ontwikkeling
Veilige demo’s: Testdata kan gebruikt worden zonder risico voor productie
Betere ontwikkelervaring: Ontwikkelaars kunnen vrij experimenteren zonder impact op productie

Entity relations diagram

Het ERD diagram toont de relaties tussen alle database tabellen en hun kardinaliteit.

ERD Diagram

Database architectuur

Hiërarchische structuur

Client Environment (root)
  └── User Environment (many-to-many)
  └── Projects (one-to-many)
      └── Closures (one-to-many)
          ├── Files (one-to-many)
          ├── Closure Stakeholders (many-to-many)
          └── Closure Comments (one-to-many)
      └── Project Managers (many-to-many)
      └── Project Recipients (many-to-many)
  └── Email Templates (one-to-many)
      └── Template Blocks (hierarchical)
  └── Files (client logos)

Kern entiteiten

Klant en gebruikersmanagement

tenants (voorheen client_environment)

Root entiteit voor multi-tenancy
Bevat bedrijfsinformatie en contactgegevens
One-to-many relatie met projects en users
Primary key: id (uuid)

user_env (voorheen user_environment)

Junction tabel voor users binnen client environments
Bevat autorisatie niveau per environment
Primary key: id (uuid)
Foreign key naar tenants via tenant_id

Project hiërarchie

projects

Centrale entiteit voor werkprojecten
Bevat project metadata (wegnummers, fasenummer, etc.)
Archivering ondersteuning
One-to-many relatie met closures

closures (voorheen sub_projects)

Afsluitingen binnen een project
Bevat periode, type, en status informatie
Labels voor Melvin, Action, SPIN tracking
One-to-many relatie met files en comments

Documentmanagement

files

Centrale bestandsopslag voor alle document types
Versie beheer met version en is_current_version
S3 integratie voor externe opslag
Ondersteunt: VKM plannen, support documenten, client logos
Soft delete met is_archived

Email systeem

email_templates

Herbruikbare email templates per client
Root block verwijzing naar template structuur
Soft delete met is_active

template_blocks (hierarchical)

Boom structuur voor email layout
Parent-child relaties voor nesting
JSONB voor styles en properties
Block types voor verschillende elementen

email_messages

Verzonden email tracking
Status tracking (sent, failed, bounced)
SES message ID voor AWS integratie

email_message_recipients & email_lists

Recipient management per project/closure
Email list grouping voor bulk emails via email_list_recipients junction tabel

email_recipient_delivery

Delivery tracking voor email recipients
Status tracking en error handling
Timestamps voor queue, sent, delivered, failed

Samenwerking

closure_stakeholders

Many-to-many relatie tussen closures en users
Soft delete tracking met is_deleted en deleted_at

closure_comments

Feedback en discussie per VKM plan versie
Type field voor verschillende comment soorten
Gekoppeld aan specifieke VKM versie

project_managers

Many-to-many relatie tussen projects en manager users
Access control voor project beheer

project_recipients

Email recipients specifiek voor project communicatie
Gescheiden van closure recipients voor granulaire controle

Relatie types

One-to-Many

Voorbeelden:

tenants → projects
projects → closures
closures → files
email_templates → template_blocks

Implementatie: Foreign key in child tabel

Many-to-Many

Voorbeelden:

users ↔ tenants via user_env
projects ↔ users via project_managers
closures ↔ users via closure_stakeholders
email_lists ↔ recipients via email_list_recipients

Implementatie: Junction/bridge tabellen met composite keys

Naamconventies

Tabellen

Lowercase met underscores: tenants, user_env
Plural waar logisch: projects, closures, files
Descriptive names: Duidelijk doel uit naam

Kolommen

Lowercase met underscores: created_at, tenant_id
Suffix conventies:
- _id voor identifiers
- _at voor timestamps
- _by voor user references
- is_ voor booleans

Constraints

Primary keys: <table>_pkey
Foreign keys: <table>_<column>_fkey
Unique constraints: <table>_<column>_key
Check constraints: <table>_<column>_check

Indices

General: idx_<table>_<column>
Composite: idx_<table>_<col1>_<col2>
Functional: idx_<purpose>_<table>

Data types strategie

UUID gebruik

Waarom UUID’s voor primary keys:

Security: Niet-sequentieel, moeilijk te raden
Distributed systems: Geen centrale ID generator nodig
Merging: Eenvoudig data van meerdere bronnen samenvoegen
Privacy: Geen inferentie over record counts

Performance overweging:

UUID v4 (random) gebruikt - 16 bytes vs 8 bytes integer
B-tree indexes blijven efficiënt tot miljarden records
Trade-off: grotere storage voor betere security

JSONB voor flexibiliteit

Usecases:

Template data: Email template structuur zonder rigid schema
Properties & Styles: CSS-achtige configuraties
Custom metadata: Uitbreidbaar zonder migrations

Benefits:

Schema-less binnen gestructureerde tabellen
Native PostgreSQL query operators (->, ->>, @>, ?)
GIN indexing voor snelle JSON queries
Validatie via applicatie layer (Pydantic)

Timestamps met timezone

Strategie: Alle timestamps inclusief timezone

Voordelen:

Correcte tijd voor international users
Daylight saving handling
Audit trail nauwkeurigheid

Soft deletes pattern

Implementatie

In plaats van fysieke deletes gebruikt VEMAP soft deletes:

Closure stakeholders:

is_deleted BOOLEAN DEFAULT false
deleted_at TIMESTAMP WITH TIME ZONE

Files:

is_archived BOOLEAN DEFAULT false
archived_at TIMESTAMP WITH TIME ZONE (impliciet via triggers)

Templates:

is_active BOOLEAN DEFAULT true

Voordelen

Audit trail: Historische data behouden
Recovery: Ongedaan maken van deletes
Analytics: Trend analyse over tijd
Compliance: Voldoen aan data retention policies

Indexing filosofie

Strategisch indexeren

Criteria voor nieuwe index:

Query wordt >100x per dag uitgevoerd
Query duurt >100ms zonder index
Index grootte < 20% van tabel grootte
Query gebruikt WHERE, JOIN of ORDER BY op kolom

Vermeden:

Over-indexing (trade-off: write performance)
Indices op low-cardinality kolommen
Redundante composite indices

Partial indices

Use case: Indexeer alleen subset van data

Voorbeeld:

-- Alleen actieve VKM plannen indexeren
CREATE UNIQUE INDEX idx_vkm_plan_current_version
ON files(closure_id)
WHERE type = 'vkm-plan' AND is_current_version = true;

Voordeel: Kleinere index size, snellere queries

Compliance & auditing

Data retention

Active data:

All current projects en closures
All user data voor actieve environments

Archived data:

Gearchiveerde projecten: 7 jaar
Email logs: 2 jaar
Bounce events: 1 jaar

Audit trail

Tracking per record:

created_at - Wanneer aangemaakt
created_by - Door wie aangemaakt
updated_at - Laatste wijziging
updated_by - Door wie gewijzigd
archived_at - Wanneer gearchiveerd (waar van toepassing)
deleted_at - Wanneer soft deleted (waar van toepassing)

Change logging:

Application layer logging van alle mutations
CloudWatch logs voor database queries
Slow query logging voor performance monitoring