Database specificaties

De VEMAP database is gebouwd op PostgreSQL en maakt gebruik van moderne features zoals UUID’s, JSONB data types, triggers, views en stored procedures. De database is geoptimaliseerd voor performance met strategische indexering en data normalisatie.

Database-engine

Type: PostgreSQL (Relationeel)

Versie: PostgreSQL 13+

Waarom PostgreSQL:

Conform MAPtm standaard
Uitstekende ondersteuning voor JSONB voor flexibele data structuren
UUID generatie voor veilige identifier generatie (UUID v4, via uuid-ossp extensie)
Geavanceerde indexering opties (B-tree, GIN, partial indexes)
Triggers en stored procedures voor business logic (voor complexe queries)
ACID compliant voor data integriteit

Database Schema

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

public - Productie/klantdata schema
development - Development/testdata schema

Character set: UTF-8 Timezone: UTC (alle timestamps worden opgeslagen met timezone, conform PostgreSQL best-practices)

De applicatie selecteert automatisch het juiste schema op basis van de ENVIRONMENT variabele:

ENVIRONMENT=local → gebruikt development schema
Anders → gebruikt public schema

Beide schema’s bevatten dezelfde tabelstructuur, maar de data is gescheiden voor veiligheid en isolatie.

PostgreSQL extensies

De database maakt gebruik van de volgende PostgreSQL extensions:

uuid-ossp

Gebruik: UUID generatie functies

Functies:

uuid_generate_v4() - Random UUID (UUID v4)

Installatie:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Datatypes

Primaire datatypes

Type	Gebruik	Voorbeelden
`uuid`	Primary keys, foreign keys, unieke identifiers	id, user_id, project_id
`character varying`	Tekst velden met variabele lengte	email, name, description
`text`	Lange tekst velden zonder lengte limiet	beschrijvingen, comments
`boolean`	True/false velden	is_active, in_s3, archived
`timestamp with time zone`	Tijdstip met timezone	created_at, updated_at
`jsonb`	Flexibele JSON data	template_data, styles, props
`integer`	Hele getallen	sort_order, level, counts
`USER-DEFINED` (enums)	Status enumeraties	sub_project_status, authorisation

Waarom JSONB?

Flexibiliteit: Template data kan zonder schema wijzigingen worden uitgebreid
Performance: JSONB is binair en ondersteunt GIN indexing
Query support: Native PostgreSQL operators voor JSON queries
Validatie: Kan gecombineerd worden met CHECK constraints

Databasetriggers

Trigger	Gebruik
`update_updated_at_column()`	Automatisch `updated_at` timestamp bijwerken bij rijmodificaties
`update_removed_at()`	Automatisch `removed_at` timestamp bijwerken bij softdelete’s

Databasefuncties

De database bevat 18 custom functies en maakt gebruik van PostgreSQL built-in functies:

Custom databasefuncties

Categorie	Functie	Beschrijving
Klantomgevingsmanagement	`create_client_environment()`	Creëert nieuwe client environment
	`delete_client_environment()`	Cascade delete van client environment
	`get_client_environments()`	Haalt alle client environments op
Project- & afsluitingsqueries	`get_all_project_closures()`	Project afsluitingen als JSON
	`get_closure_by_id()`	Specifieke closure details
	`get_closure_references_by_project_id()`	Closure referenties
	`get_closure_events()`	Closure event history
	`get_projects_by_user_permissions()`	Role-based project filtering
Templatemanagement	`get_all_client_templates()`	Client templates met data
	`get_complete_template_structure()`	Template hiërarchie
	`get_template_blocks()`	Template blocks met properties
Triggerfuncties	`update_updated_at_column()`	Auto-update timestamps
	`update_removed_at()`	Soft delete timestamps
	`cascade_delete_project_managers()`	Cascade delete trigger

Built-in functies

Categorie	Functie	Beschrijving
JSON/Array Aggregatie	`json_agg()`, `jsonb_agg()`	JSON aggregatie
	`array_agg()`, `unnest()`	Array operaties
UUID Generatie	`uuid_generate_v4()`	Random UUID (meest gebruikt)
	`uuid_generate_v1()`	Time-based UUID
Timestamps	`NOW()`, `CURRENT_TIMESTAMP`	Timestamp functies

Databaseprocedures

De database bevat 6 custom procedures voor complexe queries:

Functie	Gebruik
`get_all_client_templates()`	Haal alle templates op voor een klantomgeving
`get_all_project_closures()`	Haal alle closures van een project op als JSON
`get_closure_by_id()`	Haal specifieke closure details op als JSON
`get_closure_references_by_project_id()`	Haal closure referenties van een project op
`get_complete_template_structure()`	Haal complete template structuur op met alle block details
`get_template_blocks()`	Haal template blocks op met hun eigenschappen

Indexeringsstrategie

Primaire keys

Type: B-tree unique indexes op UUID kolommen
Naming: <table_name>_pkey
Performance: O(log n) lookups

Foreign keys

Type: B-tree indexes voor join optimalisatie
Naming: idx_<table>_<column>
Performance: Snellere joins en referential integrity checks

Partial indices

Gebruik: Conditionele indexering voor subset van data
Voorbeelden:
- idx_files_current_version - Alleen voor is_current_version = true
- idx_vkm_plan_current_version - Unieke huidige VKM versie per closure
- idx_active_templates - Alleen actieve templates

GIN indices

Gebruik: JSONB kolom indexering voor snelle JSON queries
Toepassing: Template data, email properties, custom configurations
Voorbeelden:
- idx_template_block_properties_properties_gin - JSONB properties search
- idx_template_block_styles_style_gin - JSONB styles search
- idx_column_configurations_column_configs_gin - Column layout search

Samengestelde indices

Gebruik: Multi-column queries voor vaak uitgevoerde filters
Voorbeelden:
- idx_files_closure_type - (closure_id, type) voor VKM plan lookups
- idx_files_project_type - (project_id, type) voor document filtering
- idx_email_messages_closure_status - (closure_id, status) voor email tracking
- idx_mailing_lists_closure_active - (closure_id, is_active) voor active lists

Dataintegriteit

Constraints

Primaire key constraints:

Alle tabellen hebben UUID primary keys
Sommige junction tabellen gebruiken composite primary keys

Foreign key constraints:

Cascade deletes waar van toepassing
Referential integrity tussen gerelateerde tabellen

Unieke constraints:

projects - Project name uniek per client environment
closures - Closure name uniek per project
user_env - Primary key id (uuid), met tenant_id foreign key
recipients - Email uniek per project
email_templates - Template name uniek per client environment
template_block_types - Block type naam uniek
mailing_lists - Per closure (unique constraint)
files - VKM plan versie uniekheid per closure (partial index)

Check constraints:

files - File type validatie (type IN ('vkm-plan', 'support', 'logo'))
files - File type category validatie
files - Status field validatie (status IN ('concept', 'active', 'archived'))
closures - Status field validatie
projects - Status field validatie
Boolean consistency checks voor archived en soft delete velden

Performance optimalisatie

Connection pooling

De applicatie gebruikt connection pooling via de DatabaseConfig singleton voor efficiënte database connecties:

Pool type: psycopg2 SimpleConnectionPool
Minimum connections: 2
Maximum connections: 50
Connection timeout: 30 seconden
Pool timeout: 10 seconden
Keepalive settings: idle=60s, interval=10s, count=5
Statement timeout: 30 seconden
Pool refresh: Elke 5 minuten (300 seconden)

Query optimalisatie

Gebruik van prepared statements via psycopg2
Strategisch gebruik van indices voor frequent queries
Stored procedures voor multi-step operaties

Beveiliging

Toegangscontrole

Role-based access: Database roles per applicatie component
Least privilege: Minimale permissies per service
No public schema access: Alleen via applicatie layer

Data encryptie

At rest: AWS RDS encryption enabled
In transit: SSL/TLS connecties verplicht
Credentials: Opgeslagen in AWS Secrets Manager

Audit logging

created_at, updated_at timestamps op alle tabellen
created_by, updated_by user tracking
Soft deletes met is_archived, archived_at voor audit trail

Compatibiliteit

PostgreSQL Versie	Ondersteuning	Notities
PostgreSQL 13	Volledig	Minimum versie
PostgreSQL 14	Volledig	Aanbevolen
PostgreSQL 15	Volledig	Getest
PostgreSQL 16	Volledig	Latest features

Vereiste extensies:

uuid-ossp (UUID generatie)