Teknisk dokumentation

Datamodel

Penly CRM er bygget i PHP Laravel med én central MySQL-database (8.4.11): 35 tabeller i 10 moduler og 69 fremmednøgler. Siden er genereret direkte fra databasens skema, så den altid passer med systemet.

Brugere og adgang 3 Kunder (CRM) 5 Salg 4 Pension 4 Rådgivning 2 Dokumenter 3 Opgaver og automatisering 3 Compliance og GDPR 4 Provision 4 Integrationer og AI 3

PK = primærnøgle · FK = fremmednøgle (klik for at gå til tabellen) · fed = påkrævet felt. Tabellerne er InnoDB med utf8mb4 og dansk sortering. Filer gemmes krypteret (AES-256-GCM) uden for databasen; document_versions peger på dem. Alle vigtige handlinger skrives i audit_log.

Brugere og adgang

erDiagram
  users ||--o{ api_tokens : "user_id"
  users ||--o{ crm_sessions : "user_id"

users

Medarbejdere med login. En bruger kan have flere roller.

PKid	bigint
username	varchar(60) · unik
name	varchar(120)
email	varchar(160)
phone	varchar(40)
title	varchar(120)
team	varchar(80)
roles	varchar(160)	kommasepareret: admin,management,advisor,sales,compliance,finance
pass_hash	varchar(255)
active	tinyint(1)	standard 1
ics_token	varchar(40) · unik
last_login_at	datetime
created_at	datetime

crm_sessions

Aktive login-sessioner. Kun en hash af cookien gemmes.

PKtoken	varchar(64)	sha256 af cookie-værdien
FKuser_id	bigint → users
created_at	datetime
expires_at	datetime

api_tokens

Nøgler til REST API for Penlys egne platforme. Kun hash gemmes.

PKid	bigint
FKuser_id	bigint → users
name	varchar(120)
token_hash	varchar(64) · unik
last_used_at	datetime
created_at	datetime

Kunder (CRM)

erDiagram
  companies ||--o{ activities : "company_id"
  customers ||--o{ activities : "customer_id"
  users ||--o{ activities : "user_id"
  users ||--o{ companies : "advisor_id"
  companies ||--o{ contacts : "company_id"
  customers ||--o{ customer_relations : "customer_id"
  customers ||--o{ customer_relations : "related_customer_id"
  users ||--o{ customers : "advisor_id"
  companies ||--o{ customers : "employer_company_id"

companies

Erhvervskunder, fx virksomheder med firmapensionsordning.

PKid	bigint
name	varchar(160)
cvr	varchar(12)
industry	varchar(80)
employees	int
address	varchar(160)
city	varchar(80)
phone	varchar(40)
email	varchar(160)
segment	varchar(40)
FKadvisor_id	bigint → users
status	varchar(20)	standard aktiv
notes	text
created_at	datetime

Bruges af: activities, contacts, customers, documents, meetings, opportunities, pension_schemes, tasks

customers

Privatkunder. Kan være ansat i en erhvervskunde.

PKid	bigint
first_name	varchar(80)
last_name	varchar(80)
birth_date	date
cpr_last4	varchar(4)	kun de sidste 4 cifre gemmes i demoen
email	varchar(160)
phone	varchar(40)
address	varchar(160)
city	varchar(80)
zip	varchar(10)
civil_status	varchar(30)
occupation	varchar(80)
annual_income	int
FKemployer_company_id	bigint → companies
FKadvisor_id	bigint → users
segment	varchar(40)	Privat, Privat+, Firmapension, Selvstændig
status	varchar(20)	aktiv, prospekt, inaktiv, slettet
source	varchar(40)
review_interval_months	tinyint	standard 12
next_review_date	date
created_at	datetime

Bruges af: activities, advisory_cases, ai_outputs, commissions, consents, customer_relations, deletion_requests, documents, leads, meetings, opportunities, pension_schemes, tasks

contacts

Kontaktpersoner hos en erhvervskunde (HR, ejer, bogholder).

PKid	bigint
FKcompany_id	bigint → companies
name	varchar(120)
title	varchar(80)
email	varchar(160)
phone	varchar(40)
is_primary	tinyint(1)	standard 0
created_at	datetime

customer_relations

Relationer mellem kunder, fx ægtefælle.

PKid	bigint
FKcustomer_id	bigint → customers
FKrelated_customer_id	bigint → customers
relation	varchar(30)	ægtefælle, samlever, barn, forælder

activities

Al kommunikation og historik, som udgør kundens tidslinje.

PKid	bigint
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
FKuser_id	bigint → users
type	varchar(20)	note, opkald, møde, email, sms, system
subject	varchar(255)
body	text
at	datetime

Salg

erDiagram
  customers ||--o{ leads : "customer_id"
  users ||--o{ leads : "owner_id"
  companies ||--o{ meetings : "company_id"
  customers ||--o{ meetings : "customer_id"
  opportunities ||--o{ meetings : "opportunity_id"
  users ||--o{ meetings : "user_id"
  companies ||--o{ opportunities : "company_id"
  customers ||--o{ opportunities : "customer_id"
  leads ||--o{ opportunities : "lead_id"
  users ||--o{ opportunities : "owner_id"
  pipeline_stages ||--o{ opportunities : "stage_id"

leads

Henvendelser fra hjemmeside, beregner, henvisning m.m.

PKid	bigint
name	varchar(120)
email	varchar(160)
phone	varchar(40)
company_name	varchar(160)
source	varchar(40)	hjemmeside, omkostningsberegner, henvisning, kampagne, event, telefon
interest	varchar(80)
status	varchar(20)	ny, kontaktet, kvalificeret, diskvalificeret, konverteret
score	tinyint	standard 0
FKowner_id	bigint → users
FKcustomer_id	bigint → customers
notes	text
created_at	datetime
converted_at	datetime

Bruges af: opportunities

pipeline_stages

Konfigurerbare salgsfaser med sandsynlighed.

PKid	bigint
name	varchar(60)
position	smallint
probability	tinyint
is_won	tinyint(1)	standard 0
is_lost	tinyint(1)	standard 0

Bruges af: opportunities

opportunities

Salgsmuligheder med værdi, fase og forventet lukkedato.

PKid	bigint
title	varchar(200)
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
FKlead_id	bigint → leads
FKstage_id	bigint → pipeline_stages
FKowner_id	bigint → users
product	varchar(80)
value	int	forventet årlig indbetaling / provisionsgrundlag i kr
expected_close	date
proposal_status	varchar(20)	ingen, udkast, sendt, accepteret, afvist
lost_reason	varchar(160)
created_at	datetime
closed_at	datetime

Bruges af: commissions, meetings

meetings

Møder i kalenderen, koblet til kunde og salgsmulighed.

PKid	bigint
title	varchar(200)
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
FKopportunity_id	bigint → opportunities
FKuser_id	bigint → users
start_at	datetime
end_at	datetime
location	varchar(30)	kontor, video, telefon, hos kunden
status	varchar(20)	planlagt, afholdt, aflyst
notes	text
created_at	datetime

Pension

erDiagram
  providers ||--o{ pension_products : "provider_id"
  companies ||--o{ pension_schemes : "company_id"
  customers ||--o{ pension_schemes : "customer_id"
  pension_products ||--o{ pension_schemes : "product_id"
  providers ||--o{ pension_schemes : "provider_id"
  pension_schemes ||--o{ scheme_history : "scheme_id"
  users ||--o{ scheme_history : "user_id"

providers

Pensionsselskaber, banker og forsikringsselskaber.

PKid	bigint
name	varchar(80)
type	varchar(30)	pensionsselskab, bank, forsikring
cvr	varchar(12)
data_integration	varchar(30)	pensionsinfo, api, manuel

Bruges af: commission_payments, commission_rules, commissions, pension_products, pension_schemes

pension_products

Produkter hos udbyderne.

PKid	bigint
FKprovider_id	bigint → providers
name	varchar(120)
kind	varchar(30)	ratepension, livrente, aldersopsparing, kapitalpension, forsikring, frie midler

Bruges af: pension_schemes

pension_schemes

Kundens pensionsordninger med depot, indbetaling og dækninger.

PKid	bigint
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
FKprovider_id	bigint → providers
FKproduct_id	bigint → pension_products
policy_no	varchar(40)
kind	varchar(30)
scheme_type	varchar(10)	privat, firma
status	varchar(20)	aktiv, hvilende, under flytning, ophørt
start_date	date
renewal_date	date
balance	bigint	standard 0
monthly_contribution	int	standard 0
employer_pct	double
employee_pct	double
annual_cost_pct	double
investment_profile	varchar(40)
cover_death	int
cover_disability	int
cover_critical_illness	int
source	varchar(20)	manuel, pensionsinfo, udbyder-api, udtræk
updated_at	datetime
created_at	datetime

Bruges af: commissions, scheme_history

scheme_history

Historik over ændringer i en pensionsordning (felt for felt).

PKid	bigint
FKscheme_id	bigint → pension_schemes
FKuser_id	bigint → users
field	varchar(40)
old_value	varchar(255)
new_value	varchar(255)
at	datetime

Rådgivning

erDiagram
  users ||--o{ advisory_cases : "advisor_id"
  users ||--o{ advisory_cases : "approved_by"
  customers ||--o{ advisory_cases : "customer_id"
  advisory_cases ||--o{ case_events : "case_id"
  users ||--o{ case_events : "user_id"

advisory_cases

Rådgivningssager med behovsanalyse, risikoprofil, anbefaling og godkendelse.

PKid	bigint
FKcustomer_id	bigint → customers
FKadvisor_id	bigint → users
title	varchar(200)
type	varchar(30)	pensionstjek, samlet økonomi, firmapension, udbetaling, forsikring
status	varchar(20)	behovsanalyse, anbefaling, til godkendelse, godkendt, afvist, afsluttet
objectives	text
retirement_age	tinyint
desired_income_pct	tinyint
risk_profile	varchar(10)	lav, middel, høj
risk_score	tinyint
horizon_years	tinyint
income	int
assets	bigint
debt	bigint
housing	varchar(30)
dependants	tinyint
needs_json	json	behovsanalyse: svar på spørgeskema
recommendation	text
rationale	text
FKapproved_by	bigint → users
approved_at	datetime
approval_comment	text
follow_up_date	date
created_at	datetime
updated_at	datetime
closed_at	datetime

Bruges af: case_events, documents

case_events

Statusforløb og kommentarer på en rådgivningssag.

PKid	bigint
FKcase_id	bigint → advisory_cases
FKuser_id	bigint → users
event	varchar(30)
comment	text
at	datetime

Dokumenter

erDiagram
  documents ||--o{ document_versions : "document_id"
  users ||--o{ document_versions : "uploaded_by"
  advisory_cases ||--o{ documents : "case_id"
  companies ||--o{ documents : "company_id"
  users ||--o{ documents : "created_by"
  customers ||--o{ documents : "customer_id"
  document_templates ||--o{ documents : "template_id"

document_templates

Skabeloner til aftaler, samtykker og rapporter.

PKid	bigint
name	varchar(120)
category	varchar(30)
body	text	tekst med {{felter}}
version	smallint	standard 1
updated_at	datetime

Bruges af: documents

documents

Kundedokumenter med godkendelses- og underskriftsstatus.

PKid	bigint
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
FKcase_id	bigint → advisory_cases
FKtemplate_id	bigint → document_templates
title	varchar(200)
category	varchar(30)	aftale, samtykke, rådgivningsrapport, pensionsoversigt, id, andet
confidential	tinyint(1)	kun ansvarlig rådgiver, compliance og admin
current_version	smallint	standard 1
approval_status	varchar(20)	ikke krævet, afventer, godkendt, afvist
signature_status	varchar(20)	ikke krævet, klar, sendt, underskrevet, afvist
signed_at	datetime
retention_until	date
extracted_json	json
FKcreated_by	bigint → users
created_at	datetime

Bruges af: consents, document_versions

document_versions

Versioner af hvert dokument. Filen gemmes krypteret uden for webroden.

PKid	bigint
FKdocument_id	bigint → documents
version	smallint
filename	varchar(160)
mime	varchar(100)
size	int
storage_key	varchar(24)	krypteret fil (AES-256-GCM) uden for webroden
sha256	varchar(64)
FKuploaded_by	bigint → users
at	datetime

Opgaver og automatisering

erDiagram
  users ||--o{ notifications : "user_id"
  users ||--o{ tasks : "assignee_id"
  companies ||--o{ tasks : "company_id"
  users ||--o{ tasks : "created_by"
  customers ||--o{ tasks : "customer_id"
  automation_rules ||--o{ tasks : "rule_id"

automation_rules

Regler der automatisk opretter opgaver ved hændelser.

PKid	bigint
name	varchar(120)
event	varchar(40)	lead.oprettet, møde.afholdt, salg.vundet, sag.til_godkendelse, sag.godkendt, pension.fornyelse, dokument.mangler, kunde.gennemgang, samtykke.udløber
offset_days	smallint	standard 0
task_title	varchar(160)
assign_to	varchar(40)	ejer, rådgiver, rolle:compliance ...
priority	varchar(10)	standard normal
active	tinyint(1)	standard 1

Bruges af: tasks

tasks

Opgaver til rådgivere og teams, manuelle eller automatiske.

PKid	bigint
title	varchar(255)
description	text
FKcustomer_id	bigint → customers
FKcompany_id	bigint → companies
related_type	varchar(20)	lead, opportunity, case, scheme, document, consent, meeting, customer
related_id	bigint
FKassignee_id	bigint → users
team	varchar(80)
due_date	date
priority	varchar(10)	lav, normal, høj
status	varchar(10)	åben, færdig
recurring_months	tinyint
FKrule_id	bigint → automation_rules
FKcreated_by	bigint → users
created_at	datetime
completed_at	datetime

notifications

Påmindelser og varsler til brugerne.

PKid	bigint
FKuser_id	bigint → users
kind	varchar(20)	påmindelse, forfalden, tildelt, godkendelse
text	varchar(255)
link	varchar(160)
read_at	datetime
at	datetime

Provision

erDiagram
  providers ||--o{ commission_payments : "provider_id"
  users ||--o{ commission_payments : "registered_by"
  users ||--o{ commission_rules : "advisor_id"
  providers ||--o{ commission_rules : "provider_id"
  users ||--o{ commissions : "advisor_id"
  customers ||--o{ commissions : "customer_id"
  opportunities ||--o{ commissions : "opportunity_id"
  providers ||--o{ commissions : "provider_id"
  commission_rules ||--o{ commissions : "rule_id"
  pension_schemes ||--o{ commissions : "scheme_id"
  commissions ||--o{ payment_allocations : "commission_id"
  commission_payments ||--o{ payment_allocations : "payment_id"

commission_rules

Provisionssatser pr. udbyder, produkt og rådgiver.

PKid	bigint
FKprovider_id	bigint → providers
product_kind	varchar(30)	null = alle produkter
FKadvisor_id	bigint → users
basis	varchar(20)	etablering_pct, løbende_pct, fast
rate	double
advisor_share_pct	double	standard 40
clawback_months	tinyint	standard 12
active	tinyint(1)	standard 1

Bruges af: commissions

commissions

Forventet og optjent provision pr. ordning og periode.

PKid	bigint
FKcustomer_id	bigint → customers
FKscheme_id	bigint → pension_schemes
FKopportunity_id	bigint → opportunities
FKprovider_id	bigint → providers
FKadvisor_id	bigint → users
FKrule_id	bigint → commission_rules
period	char(7)	YYYY-MM
kind	varchar(20)	etablering, løbende, justering, clawback
expected_amount	int
received_amount	int	standard 0
status	varchar(20)	forventet, optjent, delvist betalt, betalt, annulleret, clawback
note	varchar(255)
created_at	datetime

Bruges af: payment_allocations

commission_payments

Modtagne provisionsbetalinger fra udbydere.

PKid	bigint
FKprovider_id	bigint → providers
reference	varchar(60)
received_at	date
amount	int
status	varchar(20)	ikke afstemt, delvist afstemt, afstemt
FKregistered_by	bigint → users