Domain Model

Domain Model (as implemented)

This describes the actual core entities in api/ that back the dashboard/ UI, plus bank import, matching, AI context, and the documents hub.

Core entities

User

• Fields (typical): id, name, email, phone?, password (hashed), role (admin | agent)
• Authentication: Sanctum personal access tokens via POST /auth/login. Only admin and agent roles may authenticate.
• Roles:
  • admin — manages users (/users), uploads/indexes Belgian legislation PDFs (knowledge base), tunes global matching scores and AI system message. Has access to all agent features.
  • agent — manages assigned portfolio (properties, tenants, leases, payments, transactions), documents hub templates, building PDFs. Cannot manage users or knowledge-base writes (API-enforced).
• Relationships (conceptual):
  • hasMany properties (assigned agent via properties.user_id)
  • hasMany tenants (managed agent via tenants.user_id)
  • hasMany leases (assigned agent via leases.user_id)

Property (Building)

• Fields: id, title, owner? (string), description, city, address, status (available | sold | rented), user_id?
• Relationships:
  • belongsTo user (assigned agent)
  • hasMany units
  • hasMany images
  • hasMany property documents (PDFs for RAG — insurance, claims, maintenance)

Unit

• Fields: id, property_id, unit_number, floor?, spaces? (JSON unit composition used for condition report templates), rent_price?, energy? (JSON: epb optional class A–F only when a certificate applies; primary_energy_consumption, certificate_unique_code, co2_emission, yearly_theoretical_total_energy), epb_certif_path? (storage path for optional certificate PDF/DOC), status (available | rented | maintenance)
• Relationships:
  • belongsTo property
  • hasMany leases (occupancy history)

Tenant

• Fields: id, name, email, phone?, ibans: string[], user_id
• Relationships:
  • belongsTo user (managing agent)
  • hasMany leases

Notes:

• A tenant can have multiple IBANs (ibans) used by matching logic. Manual match learns new IBANs from bank lines.

Lease

• Fields: id, tenant_id, unit_id, user_id?, start_date, end_date, rent_amount, base_rent_amount, base_index? (decimal reference index at contract baseline, e.g. CPI / health-housing index), deposit_amount?, status (active | future | ended | terminated), lease_type? (residential | commercial | common_law), principale_residence? (brussels | flemish | walloon | german), attachment_path?
• Relationships:
  • belongsTo tenant
  • belongsTo unit
  • belongsTo user (assigned agent)
  • hasMany payments (one per billed calendar month)
  • hasMany rent revisions (manual history; newest-first when loaded)
  • hasOne active repayment plan (optional)

LeaseRentRevision

Records a manual rent change for a lease (indexation or other revision).

• Fields: id, lease_id, effective_date (DATE), new_rent_amount (decimal), note?, indexation_year? (small integer, e.g. calendar year of indexation), created_by?, timestamps
• Behaviour:
  • Only the latest revision can be edited or deleted.
  • Applying a revision updates lease.rent_amount and stamps base_amount on all Pending payments from effective_date onward.
• Relationships:
  • belongsTo lease
  • belongsTo user (creator, nullable)

Payment

One payment row represents one calendar billing month for a lease.

• Fields (key): id, lease_id, billing_period_start (YYYY-MM-01), sequence?, amount, base_amount, arrears_extra_amount, paid_at?, bank_reference?, status, cash_received_amount?
  • base_amount — contract rent for this billing month (equals lease.rent_amount after revisions)
  • arrears_extra_amount — extra added by an active repayment plan (0 when none); invariant: amount = base_amount + arrears_extra_amount
• Statuses:
  • stored: pending, partial, matched, confirmed
  • derived in API responses: overdue for pending rows whose billing month is before the current calendar month
• Relationships:
  • belongsTo lease
  • hasMany bank transactions (a payment can become partial via multiple credits)

LeaseRepaymentPlan

Records an agent-agreed arrears amortization schedule for a lease.

• Fields: id, lease_id, status (active | cancelled | completed), start_billing_period (YYYY-MM-01), extra_amount_per_month, total_debt_amount, note?, timestamps
• Behaviour:
  • Only one active plan per lease at a time; upserting a new plan cancels the previous one.
  • Upsert stamps arrears_extra_amount on all Pending payments from start_billing_period onwards.
  • Cancel zeroes arrears_extra_amount on those same Pending payments.
  • Rent revisions preserve arrears_extra_amount on top of the updated base_amount.
• API: GET|PUT|DELETE /leases/{lease}/repayment-plan
• Relationships:
  • belongsTo lease

Bank ingestion & reconciliation entities

BankTransaction

Represents an incoming credit (amount ≥ 0) imported from Ponto or other sources and used for reconciliation.

• Fields (key): id, amount, currency?, execution_date?, value_date?, counterparty_name?, iban?, message?, purpose_code?, creditor_id?, internal_reference?
• Dedup / identity fields: external_id?, native_event_id?, canonical_event_key, fingerprint_id, fingerprint_version, source, bank_account_id?
• Import sources: Ponto refresh (POST /bank-transactions/refresh), bulk JSON (POST /bank-transactions/import), single upsert (POST /bank-transactions)
• Relationships:
  • belongsTo payment (optional link via payment_id)

BankTransactionImportJob

Tracks a refresh/import run so the dashboard can poll job progress (job_id, status, result, error_message).

AiMatchBatchJob

Tracks provider-backed AI matching batch runs (queued/async).

AI & knowledge entities

KnowledgeDocument / KnowledgeChunk

Global Belgian legislation PDF store for RAG (admin-managed).

• knowledge_documents — filename, original_name, status (pending | processing | indexed | failed), chunk_count, indexed_at, uploaded_by
• knowledge_chunks — text segments with optional embedding JSON for vector retrieval
• Storage: storage/app/knowledge-base/belgium-legislation (private)
• Retrieved during POST /ai/chat (top 5 chunks by query similarity)

PropertyDocument / PropertyDocumentChunk

Per-building PDF store for RAG (agent-managed on building detail).

• Categories: assurance | sinistre | entretien | autre
• Same chunking/embedding pipeline as knowledge documents
• Retrieved when property_id is sent with an AI chat request (top 5 chunks scoped to that property)

Configuration singletons

MatchingSettings

Persisted tuning for rule-based matching and the AI assistant system prompt.

• Matching: thresholds (min_score_to_auto_match, min_score_partial_link, min_score_modal_list), scoring weights (score_iban, score_amount, score_late_window, score_billing_period, score_name_max), knobs (rent_amount_tolerance, late_window_max_months, infer_month_from_message, tenant_name_min_similarity), exclusions (excluded_ibans, transaction_blacklisted_ibans), auto_match_enabled, last-run telemetry
• AI assistant: ai_chat_system_message — optional admin-defined system prompt prepended to every chat completion

PontoSettings

Ponto connectivity: client_id, client_secret, selected_account_ids[], cached accounts, paging/date filters.

Matching flow (BankTransaction → Payment)

Rule-based matching (PaymentMatchingService) scores candidates (max ~100) and auto-links or suggests manual review.

Signal	Default pts	Rule
IBAN match	30	Required; matches any tenant ibans[]; excluded IBANs → skip
Amount	25	Within rent_amount_tolerance vs rent or payment amount (incl. repayment extra)
Billing period	25	Transaction month = billing month
Late window	25	Payment after billing month, within late_window_max_months
Name similarity	up to 20	Fuzzy counterparty name; optional month from message

Auto-match: full link if score ≥ 75 and amount OK; partial if ≥ 50. AI batch handles remainder via POST /bank-transactions/ai-match.

Relationship diagram (high level)

• User → many Properties, Tenants, Leases
• Property → many Units, PropertyDocuments
• Unit → many Leases
• Tenant → many Leases
• Lease → many Payments, RentRevisions, optional RepaymentPlan
• Payment → many BankTransactions
• KnowledgeDocument → many KnowledgeChunks (global RAG)
• PropertyDocument → many PropertyDocumentChunks (building-scoped RAG)

---

Detailed schema notes

Database

The Laravel API uses MySQL for development and app runtime. Schema changes, JSON columns, constraints, and SQL behavior should be designed and tested against MySQL rather than SQLite assumptions.

lease_rent_revisions document columns

Optional files stored on the public disk under each lease’s tenant folder (…/rent-revisions/):

• signed_letter_path — tenant-signed revision / indexation letter (PDF or Word).
• post_receipt_path — proof the recommended letter was sent or received (image or PDF).

API responses expose temporary signed download URLs (signed_letter_url, post_receipt_url). Amount, note, and indexation year can only be changed on the latest revision; uploads can be attached to any revision for the lease.

Activity timestamps (last action, notification-ready)

Denormalized columns on the same row (updated via the existing rent-revision PUT/POST endpoint):

• last_shared_email_at / last_shared_email_by — set when the client sends mark_email_shared: true (user opened the mailto draft from the app).
• last_shared_whatsapp_at / last_shared_whatsapp_by — set when the client sends mark_whatsapp_shared: true.
• signed_letter_uploaded_at / signed_letter_uploaded_by — set when a new signed letter file is stored.
• post_receipt_uploaded_at / post_receipt_uploaded_by — set when a new post receipt file is stored.

These support queries such as “receipt missing after indexation” without scanning files. For a full append-only audit trail (every click, every version), a future table lease_rent_revision_events can be added; the columns above remain the fast “last known” mirror for UI and notifications.

lease_rent_revisions index snapshot columns

Optional decimals stored per revision (inputs used when applying or auto-calculating indexation for that step):

• base_index — baseline reference index for this revision.
• new_index — new/reference index for the indexation period.
• base_year_index — Belgian health-housing legal reference frame as a year enum (1996, 2004, 2013, 2025). Default-from-signature mapping is implemented in App\Enums\BaseYearIndex::fromSignatureDate (API) and dashboard/src/lib/baseYearIndex.ts (must stay in sync).

The leases.index JSON column was removed; index numbers are no longer stored on the lease row.

revision_reminder_settings

Singleton table (one row). Stores how many calendar months before each lease’s anniversary month (derived from leases.start_date) the payments calendar shows a reminder indicator.

• months_before_anniversary — integer 0–12; 0 means reminders are off.

communication_template_settings

Singleton table (one row). Stores editable plain-text templates for tenant communication that is opened from the dashboard, separate from printable document templates (see Documents hub — built-in printable templates below).

• rent_revision_email_subject_template — optional mailto: subject for rent revision / indexation shares.
• rent_revision_email_body_template — optional mailto: body for rent revision / indexation shares.
• rent_revision_whatsapp_body_template — optional WhatsApp message body for rent revision / indexation shares.

Blank values are normalized to NULL; the dashboard hides the related email or WhatsApp share action when the required template is missing. Supported placeholders are rendered client-side from the current lease/revision context (plain text, separate from printable document templates): {{today}}, {{agencyName}}, {{agentName}}, {{tenantName}}, {{tenantPhone}}, {{tenantEmail}}, {{tenantAddress}}, {{propertyAddress}}, {{propertyTitle}} (alias of property address), {{unitLabel}}, {{propertyCity}}, {{signatureDate}}, {{startDate}}, {{endDate}}, {{baseRent}}, {{newRent}}, {{effectiveDate}}, {{indexBase}}, {{indexCurrentYear}}, {{indexBaseYear}}, {{peb}}, {{leaseType}}, {{residence}}, {{indexationYear}}, {{landlordName}}, {{landlordEmail}}, {{landlordAddress}}, {{landlordPhone}}, {{leaseId}}, {{revisionId}}, and {{leaseUrl}}.

Documents hub — built-in printable templates

The dashboard Documents area (/documents) lets each authenticated user edit HTML letter/contract templates, preview them with merge fields, and print. This is separate from communication_template_settings (plain-text mailto: / WhatsApp bodies) and from property_documents (building PDFs for RAG).

builtin_document_templates

Per-user, per–document-type rows. One saved template per (user_id, document_type_id).

Column	Notes
user_id	FK → users, cascade on delete
document_type_id	string, max 50 — see built-in types below
body_html	nullable longText — Quill/TinyMCE HTML; merge fields as {{fieldName}}
logo	nullable JSON — enabled, src (data URL or URL), alt, maxHeightMm, align (left | right)

API (Sanctum):

Method	Route	Notes
GET	/api/document-templates/{documentTypeId}	404 if the user has never saved this type
PUT	/api/document-templates/{documentTypeId}	Upsert body_html + logo

Backend: BuiltinDocumentTemplateController, BuiltinDocumentTemplateService, BuiltinDocumentTemplateResource. Frontend: builtinTemplateService, useBuiltinTemplate / useUpsertBuiltinTemplate, DTO dashboard/src/types/builtinDocumentTemplate.ts.

Persistence history: templates were previously stored in localStorage (documentHub.template.v1, documentHub.template.v2). Those keys are removed on startup by cleanupLegacyLocalStorage(); only server rows are used now.

Built-in document types (DocumentTypeId)

Defined in dashboard/src/types/documentHub.ts and registered in dashboard/src/documents/documentRegistry.tsx:

document_type_id	Hub status	Default HTML seed (client)
indexation_letter	available	createDefaultIndexationLetterBodyHtml() in dashboard/src/documents/templates/indexationLetterTemplate.ts
residential_lease_contract	available	createDefaultResidentialLeaseContractBodyHtml() in dashboard/src/documents/templates/residentialLeaseContractTemplate.ts
notice_of_default	placeholder	No body seed yet — editor uses empty bodyHtml until the user saves

When GET returns 404, the dashboard seeds the editor from the functions above (DocumentDetailPage, createSeedDefaultTemplate). After the first PUT, the API copy is authoritative.

In-app usage (besides the hub):

• Indexation letter — LeaseRentRevisionsPanel loads useBuiltinTemplate('indexation_letter') and prints via RevisionLetterSheet.
• Residential lease contract — LeaseDetailsPage loads useBuiltinTemplate('residential_lease_contract') and prints via ResidentialLeaseContractSheet (falls back to the same default HTML if nothing saved).

notice_of_default is listed in the hub but not wired to a lease workflow yet.

Client-only preview layout (documentHub.ts)

dashboard/src/types/documentHub.ts does not store template bodies. It defines:

• DocumentTypeId — union of the three built-in type strings above
• DocumentHubLayout — preview frame CSS (density, previewMaxWidth, previewPadding, previewScale, showBorder)
• DOCUMENT_HUB_LAYOUT_STORAGE_KEY = documentHub.layout.v1 — per-type layout overrides in localStorage via dashboard/src/documents/documentLayoutStorage.ts

Template content shape (editor + print) lives in dashboard/src/types/documentTemplate.ts (DocumentTemplate, version 3).

Printable merge fields

Placeholders in body_html use {{camelCase}} syntax. The canonical field list is TemplateFieldId in documentTemplate.ts (e.g. tenantName, propertyAddress, baseRent, newRent, landlordName, rooms, bankAccount, clauses, …). The editor exposes tokens via templateEditorTokens.ts; at render/print time, values are filled from the current lease/revision (or mock data on the hub preview).

Not every field applies to every document type; unknown tokens in HTML are left unchanged.

custom_templates

User-created templates (name, description, optional icon) with the same body_html + logo shape as built-ins, but without a fixed document_type_id. Stored in custom_templates (scoped by user_id). API: GET/POST /api/custom-templates, GET/PUT/DELETE /api/custom-templates/{id}. Hub route: /documents/custom/{id}.

tenants.email optional

Tenant email is optional and may be NULL (creation without email is allowed).

units.spaces

units.spaces is a nullable JSON field describing the physical composition of a unit. It is the source used to prefill future entry/exit condition reports.

Shape:

{
  "version": 1,
  "spaces": [
    {
      "type": "bedroom",
      "name": "Chambre 1",
      "items": ["Murs", "Sol", "Plafond", "Portes", "Fenêtres"]
    }
  ]
}

The dashboard stores expanded space instances rather than quantities: adding the same space type twice creates separate entries such as Chambre 1 and Chambre 2, each with its own editable item list. The legacy room-count columns were removed; derive any needed counts from spaces.

owners and properties.owner_id

Owners are parties (landlords) scoped by user_id (same pattern as tenants): name, address, email (optional), phone (optional).

A owner may optionally store a bce value (string, nullable) for Belgian company identification details when relevant.

Buildings/properties can have one or many owners through a pivot table owner_property (property_id, owner_id). Deleting a property or owner cascades to remove pivot links.

knowledge_documents and knowledge_chunks

Global store for Belgian legislation PDFs used by the AI assistant (RAG). Admin-only upload/delete/index via API or Settings → General.

• knowledge_documents — metadata: filename, original_name, status enum (pending | processing | indexed | failed), chunk_count, indexed_at, uploaded_by (FK → users).
• knowledge_chunks — RAG chunks: knowledge_document_id (FK), chunk_index, content, start_page, end_page, token_count, embedding (JSON, optional).

Files are stored on the local disk at knowledge-base/belgium-legislation/{filename}. Indexing uses the same ParsesAndChunksPdf pipeline as property documents (PDF text extraction, Tesseract OCR fallback for scans, optional Ollama embeddings).

During POST /ai/chat, KnowledgeRetrievalService retrieves the top 5 relevant chunks for the latest user message and appends them to the system prompt. Configure chunk size/overlap and embedding model via KNOWLEDGE_* env vars (see README). Artisan: php artisan knowledge:index.

property_documents and property_document_chunks

Per-property document store for building-related PDFs (assurances, sinistres, entretiens, etc.).

• property_documents — metadata: property_id (FK), filename, original_name, category enum (assurance | sinistre | entretien | autre), status enum (pending | processing | indexed | failed), chunk_count, indexed_at, uploaded_by.
• property_document_chunks — RAG chunks derived from the PDF: property_document_id (FK), chunk_index, content, start_page, end_page, token_count, embedding (JSON, optional).

Files are stored on the local disk at property-documents/{property_id}/{filename}. Checksum deduplication is scoped per property (UNIQUE property_id + checksum). The chunking and embedding pipeline reuses the ParsesAndChunksPdf trait (shared with KnowledgeDocumentService).

Text extraction: indexing uses native PDF text extraction (smalot/pdfparser) first. When that yields little or no text (typical for scanned PDFs), the API falls back to Tesseract OCR in French (fra). The API host must have tesseract-ocr, tesseract-ocr-fra, and poppler-utils (pdftoppm) installed. Configure via PDF_OCR_ENABLED and PDF_OCR_MIN_EXTRACTED_CHARS in api/.env.

When property_id is included in a /api/ai/chat request, AiChatController retrieves the top-5 most relevant chunks from PropertyDocumentRetrievalService and injects them into the system prompt (same RAG pattern as the global knowledge base).

payments.cash_received_amount

The billed rent (payments.amount) is never mutated by a cash confirmation. Instead, a separate nullable column stores the actual cash received:

• cash_received_amount — decimal(15,2), nullable. Set by POST /api/payments/{id}/confirm-cash. Null means no cash has been recorded.

Status logic after cash confirmation (computed by PaymentMatchingService::recomputePaymentStatus):

Bank sum	Cash amount	Combined	Status
0	≥ billed − tolerance	full	confirmed
0	< billed	partial	partial
0	> billed + tolerance	over	overpaid
> 0	any	≥ billed − tolerance	matched
> 0	any	> billed + tolerance	overpaid
> 0	any	< billed	partial

total_matched in PaymentResource includes both bank lines and cash_received_amount.

Revert (POST /api/payments/{id}/revert-cash) clears cash_received_amount and resets status to pending. Works on confirmed rows and partial rows that have cash recorded (no linked bank transactions allowed in both cases).

Backfill: legacy confirmed rows with no bank transactions have cash_received_amount backfilled to amount via migration 2026_05_14_120000_….

lease_condition_reports — État des lieux (inventory of fixtures)

Structured move-in / move-out inspection records captured on the mobile PWA and synced to the server.

Tables

lease_condition_reports

Column	Type	Notes
id	bigint PK
client_uuid	uuid UNIQUE	Assigned on mobile for idempotent push
lease_id	FK → leases	cascadeOnDelete
unit_id	FK → units	Denormalized from lease at creation; cascadeOnDelete
type	enum entry|exit
status	enum draft|completed	Default draft
conducted_at	timestamp nullable	Date/time of on-site visit
general_notes	text nullable	Report-level free text
generated_description	text nullable	AI narrative used in the generated PDF
generated_document_path	string nullable	Draft PDF on public disk (tenant folder)
generated_document_at	timestamp nullable	When the draft PDF was last generated
signed_document_path	string nullable	User-uploaded signed PDF on public disk
signed_document_uploaded_at	timestamp nullable	When the signed PDF was uploaded
signed_document_uploaded_by	FK → users nullable	Dashboard user who uploaded the signed copy
created_by	FK → users nullable	nullOnDelete
completed_at	timestamp nullable	Set when status → completed
created_at, updated_at	timestamps

Unique constraint: (lease_id, type) — one report per lease per type (entry or exit).

lease_condition_report_rooms

Column	Notes
id, client_uuid UNIQUE
lease_condition_report_id FK	cascadeOnDelete
name	e.g. “Salon”, “Chambre 1”
sort_order	smallint

lease_condition_report_items

Column	Notes
id, client_uuid UNIQUE
room_id FK	cascadeOnDelete
label	e.g. “Murs”, “Sol”
condition	enum good|fair|poor|damaged|not_applicable nullable
notes	text nullable
sort_order	smallint

lease_condition_report_media

Column	Notes
id, client_uuid UNIQUE
item_id FK	cascadeOnDelete
media_type	photo | video
path	Storage path on local disk: condition-reports/{property-slug}/{unit-slug}/{tenant-slug}/{entry|exit}/{filename}
original_name, mime_type, size	Metadata

v1 video limits: max 1 video per item, 25 MB, 30 s (enforced on mobile); formats mp4, mov, webm.

Default checklist template

LeaseConditionReportService::createFromTemplate() seeds rooms/items only from units.spaces. The generated report owns a snapshot of those spaces/items, so later changes to the unit composition affect only future reports.

Units without configured spaces create reports with no rooms/items. There is no generic fallback checklist.

API routes

All under auth:sanctum:

Method	Route
GET	/api/leases/{lease}/condition-reports
POST	/api/leases/{lease}/condition-reports
GET	/api/leases/{lease}/condition-reports/{report}
PUT	/api/leases/{lease}/condition-reports/{report}
DELETE	/api/leases/{lease}/condition-reports/{report}
POST	/api/leases/{lease}/condition-reports/{report}/items/{item}/media
DELETE	/api/leases/{lease}/condition-reports/{report}/items/{item}/media/{media}
GET	/api/leases/{lease}/condition-reports/{report}/items/{item}/media/{media}/download
POST	/api/leases/{lease}/condition-reports/{report}/generate-document
POST	/api/leases/{lease}/condition-reports/{report}/signed-document
GET	/api/leases/{lease}/condition-reports/{report}/generated-document
GET	/api/leases/{lease}/condition-reports/{report}/signed-document

Draft PDFs are generated server-side (DomPDF) per qualifying room (item has condition and/or media). Gemini vision runs per room with photos first and an expert prompt aligned to Belgian inventory-of-fixtures style (GEMINI_API_KEY, model CONDITION_REPORT_VISION_MODEL default gemini-2.5-flash). The PDF shows each item’s photo(s) with état constaté and note de l’expert (item notes) underneath the room narrative. Only rooms/items with condition or media are included. Workflow: generate → download draft → sign offline → re-upload signed PDF.

LAN mobile sync routes

Method	Route	Auth
GET	/api/sync/health	public
GET	/api/sync/pull?property_ids[]=…&updated_since=…	Sanctum
POST	/api/sync/push (multipart: payload JSON + media[{client_uuid}] files)	Sanctum

Relationships

• Lease → hasMany(LeaseConditionReport)
• Unit → hasMany(LeaseConditionReport)
• LeaseConditionReport → hasMany(rooms) → hasMany(items) → hasMany(media)

Conflict resolution (sync push)

If the server already holds a completed report for (lease_id, type) and the incoming push has status draft, the push is rejected with a conflicts entry (reason: server_completed). Dashboard (server) wins. Users must resolve on the dashboard before re-syncing.

Property ↔ Building naming convention

The real-estate asset is called Property in the backend and Building in the frontend UI. This is intentional — Property is kept generic so future types (parking, commercial, land) can be added via a type column without renaming the entire stack. See core.mdc for the full naming table.