# Property Financing Platform ↔ Partner ERP Integration Specification ## 1. Executive Summary This document specifies the technical architecture and implementation plan for a production-grade two-way integration between our property financing platform and a real-estate partner's ERP system. **Recommended Architecture:** - **Asynchronous Event-Driven Webhooks:** For propagating state changes (new projects, updated plots, completed transactions) between systems, heavily guarded by idempotency keys and signature validation. - **Synchronous REST APIs:** For critical checkout operations where immediate consistency is required (e.g., locking a plot during the booking flow to prevent race conditions). - **Scheduled Reconciliation:** Nightly cron jobs to detect missed webhook deliveries or mismatched statuses, ensuring eventual consistency. **Major Integration Flows:** 1. **ERP → Platform:** Project and plot synchronization (Creation, Updates, Availability). 2. **Platform → ERP:** Booking/Reservation locking, Customer KYC (minimal), and Transaction/Payment confirmations. **Major Security Decisions:** - All API and Webhook requests must be sent over HTTPS/TLS. - **Platform → ERP (API):** OAuth 2.0 Client Credentials or secure API Keys. - **ERP → Platform (Webhooks):** HMAC-SHA256 request signatures with timestamp validation to prevent replay attacks. - Strict data minimization for KYC info. **Major Risks & Mitigations:** - **Race conditions (Double bookings):** Mitigated by implementing a synchronous plot lock/reservation check with the ERP before confirming a customer's order. - **Missed/Duplicate Webhooks:** Mitigated by `webhook_events` tracking idempotency and a nightly reconciliation job. **Realistic Integration Duration:** 16 weeks (See timeline scenario details). **Critical Path:** ERP Sandbox Setup → External ID Strategy → Webhook Resiliency Infrastructure → Reservation/Locking API Implementation → End-to-End Testing. --- ## 2. Integration Objectives - Maintain synchronized real-estate inventory between the ERP and the Platform. - Prevent duplicate sales or race conditions on property plots. - Ensure accurate financial transaction reporting in the ERP for orders made on the Platform. - Minimize exposure of Personally Identifiable Information (PII) / KYC data. - Build a resilient architecture capable of surviving network outages and transient errors. ## 3. Business Context The partner uses their ERP to manage property developments, land plots, and pricing. Our platform allows customers to view these properties, reserve plots, and make full or installment payments via mobile money or card. The integration must ensure both systems reflect the same reality regarding plot availability and financial balances without manual data entry. ## 4. Existing Platform Architecture Based on a detailed analysis of the existing Laravel codebase: - **Properties & Plots:** Stored in `properties` and `property_plots` tables. Pricing is managed via `PropertyPlotPricing`. - **Customers & Orders:** `customers` table stores demographic info. The `orders` table handles bookings/purchases linking `userid`, `propertyid`, `plotid`, and amounts. - **Transactions:** Handled by `TransactionService` and recorded in `transactions` and `transaction_logs`. Order statuses transition from `0` (Generated) → `1` (Initialized) → `Completed`. Plot statuses transition from `1/0` (Available) → `2` (Booked) → `3` (Purchased). - **KYC Data:** The system currently collects significant KYC info (IDs, Next of Kin, TIN) on checkout via the `update_order_owner_info` flow. ## 5. Integration Architecture The integration will utilize a hybrid approach: 1. **Webhooks (Event-Driven):** Used for non-blocking state propagation (e.g., ERP creating a project, or Platform notifying ERP of a completed payment). 2. **REST APIs (Synchronous):** Used strictly for operations requiring strong consistency, specifically **Plot Reservation** during checkout. 3. **Reconciliation (Batch):** Periodic polling/diffing to catch any messages dropped due to sustained outages. ### System Context Diagram ```mermaid graph TD Customer(Customer / Frontend) MNO(Payment Providers / MNO) subgraph Our Platform API[Platform API] DB[(Platform Database)] Jobs[Background Jobs / Queue] end subgraph Partner ERP ERP_API[ERP API / Webhooks] ERP_DB[(ERP Database)] end Customer -->|Browses / Books| API MNO -->|Payment Webhook| API API <--> DB API --> Jobs Jobs -->|Outgoing Webhooks| ERP_API ERP_API -->|Incoming Webhooks| API API -->|Synchronous API Calls| ERP_API ``` ### Project/Plot Synchronization Sequence (ERP to Platform) ```mermaid sequenceDiagram participant ERP participant API as Platform API participant Queue as Platform Queue participant DB as Platform DB ERP->>API: POST /api/integrations/v1/webhooks/erp (project.created) API->>API: Validate HMAC Signature API->>DB: Check idempotency (webhook_events) API-->>ERP: 202 Accepted API->>Queue: Dispatch Job (ProcessErpWebhook) Queue->>DB: Upsert Project/Plot ``` ### Booking & Payment Sequence (Platform to ERP) ```mermaid sequenceDiagram participant Customer participant API as Platform API participant DB as Platform DB participant ERP participant MNO as Payment Gateway Customer->>API: Initiates Checkout (Plot A) API->>ERP: POST /reservations (Lock Plot A) ERP-->>API: 200 OK (Plot locked temporarily) API->>DB: Create Order (Status 0), Update Plot (Status 2) API-->>Customer: Proceed to Payment Customer->>MNO: Authorizes Payment MNO->>API: Payment Webhook Callback API->>DB: Update Order (Completed), Plot (Status 3) API->>API: Dispatch Webhook Job API-->>MNO: 200 OK API->>ERP: POST Webhook (payment.completed) ERP-->>API: 202 Accepted ``` ## 6. System Responsibilities ### OUR PLATFORM TEAM - Develop secure Webhook Receiver to process incoming ERP project/plot events. - Implement idempotency and retry mechanisms for outgoing events. - Modify the checkout flow to perform synchronous reservations with the ERP. - Create nightly reconciliation jobs. ### PROPERTY PARTNER / ERP TEAM - Develop secure Webhook Sender to notify us of project/plot changes. - Expose a synchronous Reservation REST endpoint to lock plots during checkout. - Expose endpoints for transaction reconciliation. - Consume our transaction/webhook events securely. ## 7. Source-of-Truth Matrix | Domain | Source of Truth | Notes | | :--- | :--- | :--- | | **Project Details** | ERP | Creation and master metadata lives in ERP. | | **Plot Details & Size** | ERP | Plot numbering and dimensions. | | **Plot Base Pricing** | ERP | Initial listing price. (Platform manages its own internal markup/installment logic if applicable, but base is ERP). | | **Plot Availability** | Joint | ERP drives initial availability. Platform temporarily locks during checkout. Final state is merged. | | **Customer KYC** | Platform | Collected during our checkout. Sent to ERP with minimization. | | **Order/Booking** | Platform | The financial agreement happens on the platform. | | **Transactions** | Platform | Verified via our Payment Gateways (MNO/Card). | ## 8. Data Model & Database Changes To decouple internal IDs from ERP IDs and support multiple future partners, we propose the following changes. *(Note: These are recommendations. Do not execute these migrations yet.)* ### Proposed Tables: 1. **`integration_partners`**: Stores partner credentials and settings. 2. **`external_identifiers`**: Polymorphic table mapping our `properties.id`, `property_plots.id`, `customers.id`, and `orders.id` to the ERP's UUIDs. - Columns: `id`, `partner_id`, `model_type`, `model_id`, `external_id`. 3. **`webhook_events`**: Stores incoming webhooks for idempotency and debugging. - Columns: `id`, `event_id` (unique), `event_type`, `payload` (json), `status` (pending, processed, failed), `error_log`. ## 9. External ID Strategy Do not alter primary keys on `properties` or `property_plots`. Use the proposed `external_identifiers` table. When a webhook arrives with `external_project_id = "ABC"`, the platform queries the mapping table to find the internal `property.id`. ## 10. API Architecture - **Base URL:** `/api/integrations/v1/` - **Format:** JSON `application/json` - **Error Model:** Standardized JSON error response. ```json { "success": false, "error": { "code": "PLOT_NOT_AVAILABLE", "message": "The requested plot is already reserved.", "reference_id": "req_123456" } } ``` ## 11. API Specification (Synchronous) ### Platform → ERP: Reserve Plot (Synchronous) Called right before the Platform generates an order (`OrderGeneratedStatus`). - **Endpoint:** `POST /api/v1/reservations` (On ERP side) - **Purpose:** Temporarily locks a plot in the ERP to prevent double-booking. - **Payload:** ```json { "external_project_id": "erp_proj_789", "external_plot_id": "erp_plot_101", "platform_reference_id": "PLAT-ORD-555", "expiry_minutes": 15 } ``` - **Response:** `200 OK` if successful. `409 Conflict` if already booked. ## 12. Webhook Specification (Asynchronous) ### ERP → Platform: Project/Plot Synchronization - **Trigger:** ERP creates/updates a project or plot. - **Event:** `project.created`, `plot.updated`, `plot.status_changed` - **Receiver:** `POST /api/integrations/v1/webhooks/erp` - **Expected Acknowledgement:** `202 Accepted` (Platform will process async). ### Platform → ERP: Transaction/Order Synchronization - **Trigger:** Customer completes payment (initial, full, or installment). - **Event:** `order.created`, `payment.completed` - **Receiver:** ERP Webhook URL. - **Expected Acknowledgement:** `202 Accepted`. ## 13. Project & Plot Synchronization Example Payload (ERP → Platform) ```json { "event_id": "evt_909090", "event_type": "plot.created", "timestamp": "2026-08-19T07:30:00Z", "data": { "external_project_id": "erp_proj_789", "external_plot_id": "erp_plot_101", "plot_number": "Block A - 15", "area_size_sqm": 400, "land_use": "Residential", "price_tsh": 5000000, "status": "available" } } ``` ## 14. Customer & Transaction Synchronization Example Payload (Platform → ERP) *(Note: Data minimization applied. We do not send Next of Kin unless legally required by the partner).* ```json { "event_id": "evt_88888", "event_type": "payment.completed", "timestamp": "2026-08-19T07:45:00Z", "data": { "platform_order_id": "PLAT-ORD-555", "external_plot_id": "erp_plot_101", "transaction_type": "installment", "amount_paid": 500000, "currency": "TZS", "customer": { "platform_customer_id": "CUST-999", "first_name": "John", "last_name": "Doe", "phone": "+255700000000", "id_number": "NIN-12345" } } } ``` ## 15. Booking & Reservation Flow (Critical Race Condition Mitigation) 1. **Customer:** Clicks "Confirm Booking" on the platform. 2. **Platform:** Calls ERP `POST /reservations` (Synchronous). 3. **ERP:** Checks availability. If available, marks as `locked_temporarily` and returns `200 OK`. 4. **Platform:** Creates the `Order` in DB (Status 0). Plot status updated to 2 (Booked). Customer proceeds to payment Gateway. 5. **Customer:** Pays via Mobile Money / Card. 6. **Platform:** Receives payment callback. Order status → `Completed`. Plot status → 3 (`Purchased`). 7. **Platform:** Fires async webhook `payment.completed` to ERP. 8. **ERP:** Receives webhook, updates plot status to `Sold`. *(If payment fails/expires, Platform calls ERP `DELETE /reservations/{id}` to unlock).* ## 16. Security - **HMAC Signatures:** All webhooks must contain an `X-Signature` header computed as `HMAC-SHA256(timestamp + "." + raw_body, secret)`. - **Validation:** Receiver checks if `abs(current_time - timestamp) > 5 minutes` to prevent replay attacks. - **PII:** KYC Data transmitted over TLS only. Do not log customer ID numbers or raw phone numbers in `webhook_events` payloads (redact them before storing). ## 17. Idempotency & Retry Handling - **Idempotency:** The receiver must check `webhook_events` for `event_id`. If it exists and status is `processed`, return `200 OK` immediately without processing again. - **Retries:** If the receiver fails (returns `500` or timeout), the sender should use exponential backoff (e.g., retry at 1m, 5m, 15m, 1h, 6h). ## 18. Reconciliation A cron job (`ReconcileErpInventoryJob`) will run nightly at 02:00 AM. - It will query the ERP for a list of all plot statuses for active projects. - It will compare them against the local `property_plots` table. - Mismatches will generate an alert for the operations team. --- ## Deliverable 8: Open Questions for the ERP Team ### Blocking Questions 1. Does the ERP support exposing a synchronous REST API for temporary plot reservation/locking during our checkout flow? 2. What authentication mechanism does the ERP prefer for incoming API requests from us? 3. What is the exact data schema and data types for plots and pricing in the ERP? ### Important but Non-Blocking 1. Does the ERP have a staging/sandbox environment we can connect to immediately? 2. Can the ERP consume standard HTTP Webhooks with HMAC signatures, or do they require an alternative push mechanism (e.g., Azure Service Bus, SQS)? 3. What are the ERP's rate limits? --- ## Deliverable 9: Executive Summary **Recommended Architecture:** A hybrid system using Webhooks for async data syncing (projects, plots, completed payments) and a synchronous REST API specifically for plot reservation to prevent race conditions. **What our team needs to build:** 1. A mapping layer (`external_identifiers`) to cleanly separate ERP IDs from our primary keys. 2. A secure webhook receiving controller with idempotency (`webhook_events` table). 3. A modification to the checkout flow to lock the plot in the ERP before generating the order. 4. Outgoing webhooks to push payment confirmations to the ERP. **What the ERP team needs to build:** 1. Webhooks to push Project and Plot creations/updates to us. 2. A Reservation REST API for us to call during checkout. 3. A secure webhook receiver to accept our payment/transaction completions. **Integration Duration:** Estimated 16 weeks (Realistic). - **Aggressive:** 10 weeks (Requires 100% availability from ERP team). - **Realistic:** 16 weeks (Accounts for standard UAT, feedback loops, and deployment overhead). - **Conservative:** 24 weeks. See `docs/ERP_INTEGRATION_TIMELINE.xlsx` for the detailed project plan, RACI matrix, and data mappings.