← Back to presentation

CargoM8 System Blueprint

Complete database schema, workflow diagrams, role permissions, and subscription architecture. Generated from production database (114 tables, 1400+ columns). Verified against live DB — April 2026.

1. Transport Job & Marketplace Workflow CORE

flowchart TD subgraph FF["Freight Forwarder"] A[Create Job] -->|"POST /transportjobs\n-2 credits"| B[Job: OPEN] end subgraph MKT["Marketplace"] B --> C{Carriers see job} C -->|Browse / Match| D[Carrier places bid] D -->|"POST /bids\n-1 credit"| E[Bid: PENDING] end subgraph NEG["Negotiation"] E --> F{FF reviews bids} F -->|Accept| G[Bid: ACCEPTED] F -->|Reject| H[Bid: REJECTED] F -->|Counter-offer| I[Counter: PENDING] I -->|Carrier accepts| G I -->|Carrier rejects| H G --> J[Job: IN_PROGRESS] end subgraph EXEC["Execution"] J -->|Auto-create| K[TransportTask: ASSIGNED] K -->|Driver accepts| L[Task: ACCEPTED] L -->|Pickup done| M[Task: IN_TRANSIT] M -->|Delivered| N[Task: DELIVERED] N -->|POD uploaded| O[Task: COMPLETED] O --> P[Job: DELIVERED] P -->|FF confirms| Q[Job: COMPLETED] end subgraph FIN["Financial"] A -->|"Job creation: -2 credits"| R[credit_transactions] D -->|"Bid placement: -1 credit"| R G -->|Fee calculated| S[billing_ledger] Q -->|Invoice generated| T[invoices] end subgraph SIG["Real-time"] D -.->|SignalR| U[BidNotification] G -.->|SignalR| V[AcceptNotification] M -.->|SignalR| W[GPS LiveTracking] end style FF fill:#0F766E,stroke:#14B8A6,color:#fff style MKT fill:#1E3A5F,stroke:#3B82F6,color:#fff style NEG fill:#78350F,stroke:#F59E0B,color:#fff style EXEC fill:#1E3A5F,stroke:#06B6D4,color:#fff style FIN fill:#064E3B,stroke:#10B981,color:#fff style SIG fill:#312E81,stroke:#8B5CF6,color:#fff

2. Aviation / Flight Workflow ENTERPRISE

flowchart TD subgraph PILOT["Pilot"] A1[Create Flight] -->|"POST /pilot/flights"| B1[Flight: SCHEDULED] B1 --> C1[Assign CoPilot] C1 -->|SignalR notify| D1[CoPilot: PENDING] end subgraph PREFLIGHT["Pre-Flight Intelligence"] B1 --> E1[Weather Briefing METAR/TAF] B1 --> F1[Weight & Balance CG calc] B1 --> G1[Fuel Planning + Range] end subgraph CREW["Crew Workflow"] D1 -->|Accept| H1[CoPilot: ACCEPTED] H1 --> I1[CoPilot executes Checklist] H1 --> J1[CoPilot loads Cargo Manifest] I1 -->|Complete| K1[Pilot approves Checklist] J1 -->|Lock| L1[Pilot signs Manifest] end subgraph OPS["Flight Operations"] K1 --> M1[Flight: PREFLIGHT] L1 --> M1 M1 --> N1[Flight: BOARDING] N1 --> O1[Flight: IN_FLIGHT] O1 -->|GPS position updates| P1[flight_telemetry] O1 --> Q1[Flight: LANDED] Q1 --> R1[Flight: COMPLETED] end style PILOT fill:#0F766E,stroke:#14B8A6,color:#fff style PREFLIGHT fill:#1E3A5F,stroke:#06B6D4,color:#fff style CREW fill:#78350F,stroke:#F59E0B,color:#fff style OPS fill:#312E81,stroke:#8B5CF6,color:#fff

3. Subscription & Billing Flow PAYMENTS

flowchart TD subgraph SIGNUP["User Signup"] A2[Register] -->|"handle_new_user trigger"| B2[Profile created] B2 --> C2[Subscription: FREE tier] B2 --> D2[Credits: 0] end subgraph UPGRADE["Upgrade Flow"] C2 --> E2{User wants features} E2 -->|LockedOverlay| F2[/subscription/checkout?tier=X] F2 -->|Stripe Checkout Session| G2[Stripe payment page] G2 -->|Payment success| H2[/checkout/success] end subgraph ACTIVATE["Activation - dual path"] G2 -.->|"Webhook: customer.subscription.created"| I2[PaymentWebhookController] H2 -->|"verify endpoint"| J2[EnsureSubscriptionExistsAsync] I2 --> K2[user_subscriptions: ACTIVE] J2 --> K2 K2 --> L2[profiles.subscription_tier synced] K2 --> M2[Credits provisioned] end subgraph CREDITS["Credit System"] M2 --> N2[user_credits.balance] N2 -->|Place bid: -1| O2[credit_transactions] N2 -->|Create job: -2| O2 N2 -->|AI analysis: -5| O2 N2 -->|Buy package| P2[Stripe PaymentIntent] P2 -->|Success webhook| Q2[Credits added] end style SIGNUP fill:#064E3B,stroke:#10B981,color:#fff style UPGRADE fill:#1E3A5F,stroke:#3B82F6,color:#fff style ACTIVATE fill:#0F766E,stroke:#14B8A6,color:#fff style CREDITS fill:#78350F,stroke:#F59E0B,color:#fff

4. Role Permission Matrix RBAC

Admin

ResourceAccess
All dashboardsFull
User managementCRUD
SubscriptionsManage
Billing / InvoicesFull
Marketplace IntelFull
Platform SettingsFull
Audit LogsRead
Flights (admin)Full

Freight Forwarder

ResourceAccess
Transport JobsCreate/Own
Bids (on own jobs)Accept/Reject
Freight LeadsPRO+
MarketplacePRO+
DocumentsOwn
MessagesOwn threads
AnalyticsPRO+
InvoicesOwn

Carrier (Vognmand)

ResourceAccess
Transport JobsBrowse/Bid
BidsOwn bids
Fleet / VehiclesCRUD
DriversManage
RoutesCRUD
TasksAssign
Fleet TrackingPRO+
Route OptimizationPRO+
AnalyticsPRO+

Driver (Chauffør)

ResourceAccess
Transport Jobs (InProgress road)Read
Assigned TasksExecute
GPS LocationReport
Proof of DeliveryUpload
Vehicle ChecksCreate
Time EntriesOwn
NavigationUse
AI Cargo AnalysisENT

Pilot

ResourceAccess
FlightsENT: CRUD
AircraftsENT: CRUD
Air MarketplaceENT: Browse/Bid
Weather BriefingENT: Read
W&B CalculatorENT: Read
ChecklistENT: Approve
Cargo ManifestENT: Sign
CoPilot assignENT

Co-Pilot

ResourceAccess
Flights (assigned)ENT: Read
Flight invitationsENT: Accept/Decline
Weather BriefingENT: Read
ChecklistENT: Execute
Cargo ManifestENT: CRUD + Sign
W&B data entryENT: Write

5. Complete Database Schema 112 TABLES

erDiagram profiles ||--o{ user_subscriptions : has profiles ||--o{ user_credits : has profiles ||--o{ transport_jobs : creates profiles ||--o{ bids : places profiles ||--o{ messages : sends profiles ||--o{ notifications : receives profiles ||--o{ documents : owns profiles ||--o{ reviews : writes profiles ||--o{ two_factor_auth : configures profiles ||--o{ saved_searches : saves profiles ||--o{ activity_logs : generates transport_jobs ||--o{ bids : receives transport_jobs ||--o{ transport_tasks : spawns transport_jobs ||--o{ job_metrics : tracked_by transport_jobs ||--o{ job_target_carriers : targets bids ||--o{ credit_transactions : consumes bids }o--|| profiles : bidder transport_tasks ||--o{ delivery_confirmations : has transport_tasks ||--o{ proof_of_delivery : has transport_tasks ||--o{ pod_photos : has subscription_tiers ||--o{ user_subscriptions : defines subscription_tiers ||--o{ subscription_tier_features : has user_subscriptions }o--|| profiles : belongs_to user_subscriptions }o--|| subscription_tiers : uses user_credits }o--|| profiles : belongs_to user_credits ||--o{ credit_transactions : tracks invoices }o--|| profiles : billed_to billing_ledger }o--|| profiles : records payments }o--|| profiles : pays vehicle_fleet }o--|| profiles : owned_by vehicle_fleet ||--o{ vehicle_assignments : assigned vehicle_fleet ||--o{ vehicle_verifications : verified carrier_routes }o--|| profiles : owned_by carrier_service_areas }o--|| profiles : covers carrier_specializations }o--|| profiles : has carrier_insurance }o--|| profiles : insured driver_locations }o--|| profiles : reports driver_time_entries }o--|| profiles : logs driver_vehicle_checks }o--|| profiles : checks driver_certifications }o--|| profiles : certified driver_licenses }o--|| profiles : holds flights }o--|| profiles : piloted_by flights ||--o{ flight_assignments : assigns flights ||--o{ flight_telemetry : tracks flights ||--o{ preflight_checklists : requires flights ||--o{ cargo_manifests : loads flight_assignments }o--|| profiles : copilot cargo_manifests ||--o{ cargo_items : contains aircrafts }o--|| profiles : registered_by aircrafts }o--|| aircraft_catalog : type_of freight_leads }o--|| profiles : owned_by freight_leads ||--o{ lead_conversions : converts match_results }o--|| transport_jobs : matches match_results }o--|| profiles : matched_carrier geofence_zones }o--|| profiles : monitors marketplace_alerts }o--|| profiles : alerts experiments ||--o{ experiment_assignments : assigns feature_trials }o--|| profiles : trials

6. Table Groups 112 TABLES

Core Identity (2)

profiles46 colsUser profiles, roles, subscription
notifications15 colsIn-app + push notifications

Transport (6)

transport_jobs29 colsJobs with geocoding
transport_tasks29 colsExecution tasks
bids20 colsCarrier bids + counter-offers
freight_leads26 colsLead funnel
lead_conversions8 colsConversion tracking
transport_classification_audit6 colsClassification log
job_target_carriers6 colsTargeted carrier lists
job_targeting_state3 colsTargeting system state

Aviation (10)

flights26 colsFlight records
flight_assignments8 colsCoPilot assignments
flight_telemetry10 colsGPS positions
aircrafts12 colsRegistered aircraft
aircraft_catalog20 colsType specs + CG
pilot_licenses10 colsPilot certifications
preflight_checklists12 colsDual sign-off
checklist_templates8 colsPer-aircraft-type
cargo_manifests14 colsCargo loading
cargo_items10 colsIndividual items

Fleet & Drivers (17)

vehicle_fleet42 colsVehicles + ADR
vehicle_assignments12 colsDriver-vehicle links
vehicle_verifications13 colsInspection records
carrier_routes22 colsRoute definitions
carrier_service_areas11 colsCoverage zones
carrier_specializations5 colsExpress, hazmat etc
carrier_insurance16 colsInsurance records
driver_locations15 colsLive GPS
driver_location_history14 colsPartitioned (5 monthly slices)
driver_time_entries15 colsTimesheet
driver_vehicle_checks13 colsPre-trip checks
driver_certifications15 colsADR, CPC etc
driver_licenses15 colsLicense records
driver_imports11 colsBulk driver import
driver_invitations12 colsDriver invite links
carrier_preferences7 colsCarrier settings
carrier_notification_responses7 colsResponse tracking

Billing & Subscriptions (15)

subscription_tiers16 colsFREE/STARTER/PRO/ENT/ADMIN
subscription_tier_features7 colsFeature-to-tier mapping
user_subscriptions14 colsActive subscriptions
user_credits9 colsCredit balances
credit_transactions17 colsCredit ledger
feature_trials6 cols14-day feature trials
invoices17 colsPDF invoices (immutable)
billing_ledger9 colsFinancial records
payments17 colsStripe/MobilePay
payment_plans6 colsFlexible payment plans
payment_plan_installments8 colsPlan breakdown
payment_reconciliations6 colsReconciliation records
fee_configurations9 colsFee rules
fee_transactions10 colsFee ledger
fee_events10 colsFee triggers
stripe_webhook_events11 colsWebhook log

Communication (4)

messages16 colsAES-256-GCM encrypted
notifications14 colsIn-app + push
notification_templates8 colsTemplates
contact_submissions10 colsContact form

Marketplace Intelligence (14)

match_results23 cols100-point scoring
matching_metrics18 colsMatch quality
marketplace_temperature_metrics10 colsHealth score
match_efficiency_metrics8 colsEfficiency
carrier_liquidity_metrics8 colsSupply/demand
carrier_utilization_metrics8 colsUtilization %
carrier_win_rate_metrics8 colsWin rates
bid_velocity_metrics8 colsTime to bid
demand_heatmap_cells10 colsGeographic demand
corridor_profitability_metrics10 colsRoute margins
marketplace_health_trends10 colsTrend data
price_intelligence12 colsPricing analytics
job_metrics10 colsJob performance
carrier_statistics12 colsCarrier stats

Security & System (19)

audit_logs11 colsImmutable audit trail
activity_logs16 colsUser activity
two_factor_auth13 cols2FA settings
two_factor_login_attempts9 colsLogin attempt log
two_factor_verification_codes11 colsOTP codes
security_scanner_exceptions8 colsScanner whitelist
encryption_key_metadata10 colsKey rotation
platform_settings32 colsApp config
platform_control_flags3 colsFeature flags
marketplace_control_flags7 colsMarketplace flags
marketplace_control_log8 colsFlag audit
marketplace_alerts5 colsAlert rules
database_performance_metrics5 colsDB perf
query_performance_logs5 colsSlow queries
schema_migrations2 colsMigration history
webhook_failures14 colsFailed webhooks
extensions6 colsPG extensions
experiments17 colsA/B tests
experiment_assignments5 colsUser assignments

Documents & Delivery (7)

documents14 cols17 doc types, 100MB max
profile_documents8 colsProfile attachments
proof_of_delivery14 colsPOD records
pod_photos8 colsDelivery photos
delivery_confirmations17 colsDigital signatures
reviews20 cols1-5 stars + moderation
cargo_analyses12 colsAI vision results

Other (11)

geofence_zones9 colsGeoJSON polygons
green_score_balances7 colsCO2 tracking
green_score_events13 colsEmission events
carrier_embeddings3 colsML vectors
user_favorite_carriers5 colsFavorites
euro_class_reference10 colsEuro emission classes
search_history12 colsSearch log
saved_searches11 colsSaved search queries
features9 colsFeature definitions
feature_usage_tracking15 colsFeature analytics
tenants15 colsMulti-tenancy

7. Subscription Tier Matrix 5 TIERS

flowchart LR FREE["FREE\n0 kr\n5 features\n0 credits"] --> STARTER["STARTER\n379 kr/md\n18 features\n50 credits\n5% fee"] STARTER --> PRO["PRO\n1,129 kr/md\n29 features\n500 credits\n2.5% fee"] PRO --> ENTERPRISE["ENTERPRISE\nKontakt os\n41 features\n9,999 credits\n1% fee"] ENTERPRISE --> ADMIN["ADMIN\nSystem\nAlle features\n999,999 credits"] style FREE fill:#334155,stroke:#64748B,color:#CBD5E1 style STARTER fill:#064E3B,stroke:#10B981,color:#A7F3D0 style PRO fill:#1E3A5F,stroke:#3B82F6,color:#93C5FD style ENTERPRISE fill:#312E81,stroke:#8B5CF6,color:#C4B5FD style ADMIN fill:#7F1D1D,stroke:#EF4444,color:#FCA5A5

Generated from production database · 112 tables · 1,400+ columns · April 2026
© CargoM8.dev — MickMind