← 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
| Resource | Access |
| All dashboards | Full |
| User management | CRUD |
| Subscriptions | Manage |
| Billing / Invoices | Full |
| Marketplace Intel | Full |
| Platform Settings | Full |
| Audit Logs | Read |
| Flights (admin) | Full |
Freight Forwarder
| Resource | Access |
| Transport Jobs | Create/Own |
| Bids (on own jobs) | Accept/Reject |
| Freight Leads | PRO+ |
| Marketplace | PRO+ |
| Documents | Own |
| Messages | Own threads |
| Analytics | PRO+ |
| Invoices | Own |
Carrier (Vognmand)
| Resource | Access |
| Transport Jobs | Browse/Bid |
| Bids | Own bids |
| Fleet / Vehicles | CRUD |
| Drivers | Manage |
| Routes | CRUD |
| Tasks | Assign |
| Fleet Tracking | PRO+ |
| Route Optimization | PRO+ |
| Analytics | PRO+ |
Driver (Chauffør)
| Resource | Access |
| Transport Jobs (InProgress road) | Read |
| Assigned Tasks | Execute |
| GPS Location | Report |
| Proof of Delivery | Upload |
| Vehicle Checks | Create |
| Time Entries | Own |
| Navigation | Use |
| AI Cargo Analysis | ENT |
Pilot
| Resource | Access |
| Flights | ENT: CRUD |
| Aircrafts | ENT: CRUD |
| Air Marketplace | ENT: Browse/Bid |
| Weather Briefing | ENT: Read |
| W&B Calculator | ENT: Read |
| Checklist | ENT: Approve |
| Cargo Manifest | ENT: Sign |
| CoPilot assign | ENT |
Co-Pilot
| Resource | Access |
| Flights (assigned) | ENT: Read |
| Flight invitations | ENT: Accept/Decline |
| Weather Briefing | ENT: Read |
| Checklist | ENT: Execute |
| Cargo Manifest | ENT: CRUD + Sign |
| W&B data entry | ENT: 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)
| profiles | 46 cols | User profiles, roles, subscription |
| notifications | 15 cols | In-app + push notifications |
Transport (6)
| transport_jobs | 29 cols | Jobs with geocoding |
| transport_tasks | 29 cols | Execution tasks |
| bids | 20 cols | Carrier bids + counter-offers |
| freight_leads | 26 cols | Lead funnel |
| lead_conversions | 8 cols | Conversion tracking |
| transport_classification_audit | 6 cols | Classification log |
| job_target_carriers | 6 cols | Targeted carrier lists |
| job_targeting_state | 3 cols | Targeting system state |
Aviation (10)
| flights | 26 cols | Flight records |
| flight_assignments | 8 cols | CoPilot assignments |
| flight_telemetry | 10 cols | GPS positions |
| aircrafts | 12 cols | Registered aircraft |
| aircraft_catalog | 20 cols | Type specs + CG |
| pilot_licenses | 10 cols | Pilot certifications |
| preflight_checklists | 12 cols | Dual sign-off |
| checklist_templates | 8 cols | Per-aircraft-type |
| cargo_manifests | 14 cols | Cargo loading |
| cargo_items | 10 cols | Individual items |
Fleet & Drivers (17)
| vehicle_fleet | 42 cols | Vehicles + ADR |
| vehicle_assignments | 12 cols | Driver-vehicle links |
| vehicle_verifications | 13 cols | Inspection records |
| carrier_routes | 22 cols | Route definitions |
| carrier_service_areas | 11 cols | Coverage zones |
| carrier_specializations | 5 cols | Express, hazmat etc |
| carrier_insurance | 16 cols | Insurance records |
| driver_locations | 15 cols | Live GPS |
| driver_location_history | 14 cols | Partitioned (5 monthly slices) |
| driver_time_entries | 15 cols | Timesheet |
| driver_vehicle_checks | 13 cols | Pre-trip checks |
| driver_certifications | 15 cols | ADR, CPC etc |
| driver_licenses | 15 cols | License records |
| driver_imports | 11 cols | Bulk driver import |
| driver_invitations | 12 cols | Driver invite links |
| carrier_preferences | 7 cols | Carrier settings |
| carrier_notification_responses | 7 cols | Response tracking |
Billing & Subscriptions (15)
| subscription_tiers | 16 cols | FREE/STARTER/PRO/ENT/ADMIN |
| subscription_tier_features | 7 cols | Feature-to-tier mapping |
| user_subscriptions | 14 cols | Active subscriptions |
| user_credits | 9 cols | Credit balances |
| credit_transactions | 17 cols | Credit ledger |
| feature_trials | 6 cols | 14-day feature trials |
| invoices | 17 cols | PDF invoices (immutable) |
| billing_ledger | 9 cols | Financial records |
| payments | 17 cols | Stripe/MobilePay |
| payment_plans | 6 cols | Flexible payment plans |
| payment_plan_installments | 8 cols | Plan breakdown |
| payment_reconciliations | 6 cols | Reconciliation records |
| fee_configurations | 9 cols | Fee rules |
| fee_transactions | 10 cols | Fee ledger |
| fee_events | 10 cols | Fee triggers |
| stripe_webhook_events | 11 cols | Webhook log |
Communication (4)
| messages | 16 cols | AES-256-GCM encrypted |
| notifications | 14 cols | In-app + push |
| notification_templates | 8 cols | Templates |
| contact_submissions | 10 cols | Contact form |
Marketplace Intelligence (14)
| match_results | 23 cols | 100-point scoring |
| matching_metrics | 18 cols | Match quality |
| marketplace_temperature_metrics | 10 cols | Health score |
| match_efficiency_metrics | 8 cols | Efficiency |
| carrier_liquidity_metrics | 8 cols | Supply/demand |
| carrier_utilization_metrics | 8 cols | Utilization % |
| carrier_win_rate_metrics | 8 cols | Win rates |
| bid_velocity_metrics | 8 cols | Time to bid |
| demand_heatmap_cells | 10 cols | Geographic demand |
| corridor_profitability_metrics | 10 cols | Route margins |
| marketplace_health_trends | 10 cols | Trend data |
| price_intelligence | 12 cols | Pricing analytics |
| job_metrics | 10 cols | Job performance |
| carrier_statistics | 12 cols | Carrier stats |
Security & System (19)
| audit_logs | 11 cols | Immutable audit trail |
| activity_logs | 16 cols | User activity |
| two_factor_auth | 13 cols | 2FA settings |
| two_factor_login_attempts | 9 cols | Login attempt log |
| two_factor_verification_codes | 11 cols | OTP codes |
| security_scanner_exceptions | 8 cols | Scanner whitelist |
| encryption_key_metadata | 10 cols | Key rotation |
| platform_settings | 32 cols | App config |
| platform_control_flags | 3 cols | Feature flags |
| marketplace_control_flags | 7 cols | Marketplace flags |
| marketplace_control_log | 8 cols | Flag audit |
| marketplace_alerts | 5 cols | Alert rules |
| database_performance_metrics | 5 cols | DB perf |
| query_performance_logs | 5 cols | Slow queries |
| schema_migrations | 2 cols | Migration history |
| webhook_failures | 14 cols | Failed webhooks |
| extensions | 6 cols | PG extensions |
| experiments | 17 cols | A/B tests |
| experiment_assignments | 5 cols | User assignments |
Documents & Delivery (7)
| documents | 14 cols | 17 doc types, 100MB max |
| profile_documents | 8 cols | Profile attachments |
| proof_of_delivery | 14 cols | POD records |
| pod_photos | 8 cols | Delivery photos |
| delivery_confirmations | 17 cols | Digital signatures |
| reviews | 20 cols | 1-5 stars + moderation |
| cargo_analyses | 12 cols | AI vision results |
Other (11)
| geofence_zones | 9 cols | GeoJSON polygons |
| green_score_balances | 7 cols | CO2 tracking |
| green_score_events | 13 cols | Emission events |
| carrier_embeddings | 3 cols | ML vectors |
| user_favorite_carriers | 5 cols | Favorites |
| euro_class_reference | 10 cols | Euro emission classes |
| search_history | 12 cols | Search log |
| saved_searches | 11 cols | Saved search queries |
| features | 9 cols | Feature definitions |
| feature_usage_tracking | 15 cols | Feature analytics |
| tenants | 15 cols | Multi-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