UPDATED - Cloud-Based Vehicle Export Management System (Database + Dashboard) -- 2
Budget: $3,000 – $5,000 USD
Data Size: ~10 000 records per year
Read / Write Load:
• Write: regular daily inserts (vehicles, documents, invoices)
• Read: high-concurrency dashboards, filtering, Excel/PDF exports
Schema Flexibility: relational (fully normalised, ready for 100 k+ rows)
Geolocation: single-region (with 2-AZ HA; option to add second region in Year-2)
Attachment Storage: object storage (S3 / Azure Blob / GCS) – metadata only in DB
1. Objective
Deliver a single-tenant, cloud-native platform that reduces manual keystrokes per vehicle from ±180 to <20 and removes pricing errors by automating Maker, Model, Year, Fee and Document status lookups. The system must be operable by non-IT staff, auditable for GDPR/local regulations, and portable across AWS, Azure or GCP without code changes.
2. Core Modules & Tables
A. Full Stock (Master Inventory) – Field Types & Automation Rules
Field Name | Field Type / Format | Notes / Automation
Chassis No | Text / PK | Auto-validated (length, check-digit); triggers VIN-decoder API if unknown.
Country | Single Select | Pre-load list; drives downstream “Country Tariff” rule set.
Auction Site … Yard | Single Select | Admin-maintainable dropdowns.
Maker | Single Select | Auto-filled from chassis-rules table; user can override.
Model | Single Select | Cascaded from Maker + chassis lookup; search-select for >8 k values.
Grade … Fuel Type | Single Select | Standard lists.
Year, Month, Engine CC, Mileage | Integer | Auto-extracted where possible; numeric validation.
Auction Push … Yard Fee | Currency (JPY) | Base manual inputs.
Auction Push 10 % … RECSOV Fee 10 % | Formula | Locked formulas; visible but read-only.
Transport Fee, Yard Fee, Inspection Fee | Currency | Auto-populated from “Country Tariff” table; editable checkbox to override.
Total Cost | Formula | Sum of all cost components; recalculates on every change.
Document Status | Lookup | Real-time roll-up from Document Management (Received / Pending / N/A).
Feasibility Notes
• All FK columns indexed; composite index on (Country, Yard, Status) for dashboards.
• Pricing rules effective-dated; history table keeps old rates for audit.
B. Document Management (metadata only)
Fields: Chassis FK, Document Type, Status, File Name, Hash, Object-Store URL, Uploaded By, Uploaded At, Version.
Workflow: drag-and-drop or e-mail relay; file streamed directly to object storage; DB never stores BLOB.
Audit: immutable version row on each re-upload; retention policy configurable per document type.
C. Invoice
Fields: Chassis FK, Maker-Model-Year-Color (lookup), Price (incl. country markup %), Container No, Booking No, From/To, Invoice Date, Payment Status, PDF URL.
Automation: PDF generated on status=Confirmed; bulk-create 200 invoices <3 min; e-mail optional.
D. Bookings & Shipments
Fields: Booking No, Container No, Invoice FK, From/To, Agents, ETD/ETA (timezone-aware), Shipping Cost, Document Send Date, Surrendered Date, Attachments (array of metadata objects).
Features: container utilization gauge, automatic ETA countdown on dashboard, link-out to shipping-line tracking page.
E. Country Stock & Reporting (virtual module)
A set of materialised views on top of Full Stock + Invoice + Bookings; no extra data entry.
Adds: Sold/Unsold flag, Sold price, Parts count, TT Received Date, Exchange Rate, Total USD.
Export: Excel, PDF or API JSON; filter by date-range, country, agent, payment status.
3. System Design Considerations
A. Data Integrity & Types
• Explicit data types enforced at DB layer; dropdown values stored in reference tables.
• Monetary columns use NUMERIC(14,0) for JPY and NUMERIC(12,2) for USD to avoid rounding.
B. Role-Based Access Control
Roles: Viewer, Data-Entry, Accountant, Country-Manager, Admin, Super-Admin.
Row-Level Security: users see only countries assigned to their profile.
C. Automation & Workflows
• Status propagation via PostgreSQL NOTIFY + lightweight pub/sub (no constant polling).
• Scheduled jobs: nightly currency update, overdue payment alert, container ETA shift alert.
D. Security & Audit
• AES-256 at rest (RDS/TDE), TLS 1.3 in flight.
• Immutable audit table: who, what, old-val, new-val, time-stamp, IP.
• GDPR delete job: hard delete possible after 30-day soft-delete quarantine.
E. Backup & Recovery
• Daily snapshot + continuous WAL archiving to second cloud provider.
• One-click restore tested monthly; documented RPO ≤ 15 min, RTO ≤ 1 h.
F. Scalability
• Connection pooling via PgBouncer; read replica for BI queries ready from Day 1.
• Partition template for Full Stock by year; trigger-based auto-creation.
G. Reporting & Dashboard
Real-time widgets: Remaining Japan stock, Docs pending by country, Container pipeline, Cash-flow (TT received).
All widgets exportable; colour-coded thresholds user-configurable.
H. Integration & Extensibility
• REST & GraphQL endpoints; OpenAPI 3.0 spec delivered.
• Web-hook for shipping-line status push; plug-in slots for payment gateway (Stripe, PayPal, Wise).
• Feature flags for new modules (CRM, Spare-Parts) without downtime.
4. Dashboard & Views (same as original, plus)
E. Cash-Flow Dashboard
• TT received vs expected per country (weekly roll-up).
F. Operational KPI
• Average minutes per vehicle data-entry (target <2 min).
• Auto-detection accuracy % (target ≥ 98 %).
5. Deliverables
• Terraform / Bicep / Deployment Manager scripts for AWS, Azure, GCP.
• CI-CD pipeline (GitHub Actions or GitLab) with blue-green DB migration.
• Run-book & video library for admins, accountants, developers.
• 90-day hyper-care: bug-fixes, performance tuning, tariff rule tweaks at no extra cost.
Please share (or re-share) a light sample of the simplest PostgreSQL (or any SQL) vehicle or inventory DB you’ve built before—this is a re-post of our RFP from 3 months ago on the same freelancer site; we’re not starting immediately and expect further revisions, so before any schema work begins let us know your preferred cloud, proposed RDS tier, data-dictionary template, naming conventions, and any chassis/VIN decoder API tokens you already hold.
Thank you for your consideration; we look forward to your technical and commercial response.
Read / Write Load:
• Write: regular daily inserts (vehicles, documents, invoices)
• Read: high-concurrency dashboards, filtering, Excel/PDF exports
Schema Flexibility: relational (fully normalised, ready for 100 k+ rows)
Geolocation: single-region (with 2-AZ HA; option to add second region in Year-2)
Attachment Storage: object storage (S3 / Azure Blob / GCS) – metadata only in DB
1. Objective
Deliver a single-tenant, cloud-native platform that reduces manual keystrokes per vehicle from ±180 to <20 and removes pricing errors by automating Maker, Model, Year, Fee and Document status lookups. The system must be operable by non-IT staff, auditable for GDPR/local regulations, and portable across AWS, Azure or GCP without code changes.
2. Core Modules & Tables
A. Full Stock (Master Inventory) – Field Types & Automation Rules
Field Name | Field Type / Format | Notes / Automation
Chassis No | Text / PK | Auto-validated (length, check-digit); triggers VIN-decoder API if unknown.
Country | Single Select | Pre-load list; drives downstream “Country Tariff” rule set.
Auction Site … Yard | Single Select | Admin-maintainable dropdowns.
Maker | Single Select | Auto-filled from chassis-rules table; user can override.
Model | Single Select | Cascaded from Maker + chassis lookup; search-select for >8 k values.
Grade … Fuel Type | Single Select | Standard lists.
Year, Month, Engine CC, Mileage | Integer | Auto-extracted where possible; numeric validation.
Auction Push … Yard Fee | Currency (JPY) | Base manual inputs.
Auction Push 10 % … RECSOV Fee 10 % | Formula | Locked formulas; visible but read-only.
Transport Fee, Yard Fee, Inspection Fee | Currency | Auto-populated from “Country Tariff” table; editable checkbox to override.
Total Cost | Formula | Sum of all cost components; recalculates on every change.
Document Status | Lookup | Real-time roll-up from Document Management (Received / Pending / N/A).
Feasibility Notes
• All FK columns indexed; composite index on (Country, Yard, Status) for dashboards.
• Pricing rules effective-dated; history table keeps old rates for audit.
B. Document Management (metadata only)
Fields: Chassis FK, Document Type, Status, File Name, Hash, Object-Store URL, Uploaded By, Uploaded At, Version.
Workflow: drag-and-drop or e-mail relay; file streamed directly to object storage; DB never stores BLOB.
Audit: immutable version row on each re-upload; retention policy configurable per document type.
C. Invoice
Fields: Chassis FK, Maker-Model-Year-Color (lookup), Price (incl. country markup %), Container No, Booking No, From/To, Invoice Date, Payment Status, PDF URL.
Automation: PDF generated on status=Confirmed; bulk-create 200 invoices <3 min; e-mail optional.
D. Bookings & Shipments
Fields: Booking No, Container No, Invoice FK, From/To, Agents, ETD/ETA (timezone-aware), Shipping Cost, Document Send Date, Surrendered Date, Attachments (array of metadata objects).
Features: container utilization gauge, automatic ETA countdown on dashboard, link-out to shipping-line tracking page.
E. Country Stock & Reporting (virtual module)
A set of materialised views on top of Full Stock + Invoice + Bookings; no extra data entry.
Adds: Sold/Unsold flag, Sold price, Parts count, TT Received Date, Exchange Rate, Total USD.
Export: Excel, PDF or API JSON; filter by date-range, country, agent, payment status.
3. System Design Considerations
A. Data Integrity & Types
• Explicit data types enforced at DB layer; dropdown values stored in reference tables.
• Monetary columns use NUMERIC(14,0) for JPY and NUMERIC(12,2) for USD to avoid rounding.
B. Role-Based Access Control
Roles: Viewer, Data-Entry, Accountant, Country-Manager, Admin, Super-Admin.
Row-Level Security: users see only countries assigned to their profile.
C. Automation & Workflows
• Status propagation via PostgreSQL NOTIFY + lightweight pub/sub (no constant polling).
• Scheduled jobs: nightly currency update, overdue payment alert, container ETA shift alert.
D. Security & Audit
• AES-256 at rest (RDS/TDE), TLS 1.3 in flight.
• Immutable audit table: who, what, old-val, new-val, time-stamp, IP.
• GDPR delete job: hard delete possible after 30-day soft-delete quarantine.
E. Backup & Recovery
• Daily snapshot + continuous WAL archiving to second cloud provider.
• One-click restore tested monthly; documented RPO ≤ 15 min, RTO ≤ 1 h.
F. Scalability
• Connection pooling via PgBouncer; read replica for BI queries ready from Day 1.
• Partition template for Full Stock by year; trigger-based auto-creation.
G. Reporting & Dashboard
Real-time widgets: Remaining Japan stock, Docs pending by country, Container pipeline, Cash-flow (TT received).
All widgets exportable; colour-coded thresholds user-configurable.
H. Integration & Extensibility
• REST & GraphQL endpoints; OpenAPI 3.0 spec delivered.
• Web-hook for shipping-line status push; plug-in slots for payment gateway (Stripe, PayPal, Wise).
• Feature flags for new modules (CRM, Spare-Parts) without downtime.
4. Dashboard & Views (same as original, plus)
E. Cash-Flow Dashboard
• TT received vs expected per country (weekly roll-up).
F. Operational KPI
• Average minutes per vehicle data-entry (target <2 min).
• Auto-detection accuracy % (target ≥ 98 %).
5. Deliverables
• Terraform / Bicep / Deployment Manager scripts for AWS, Azure, GCP.
• CI-CD pipeline (GitHub Actions or GitLab) with blue-green DB migration.
• Run-book & video library for admins, accountants, developers.
• 90-day hyper-care: bug-fixes, performance tuning, tariff rule tweaks at no extra cost.
Please share (or re-share) a light sample of the simplest PostgreSQL (or any SQL) vehicle or inventory DB you’ve built before—this is a re-post of our RFP from 3 months ago on the same freelancer site; we’re not starting immediately and expect further revisions, so before any schema work begins let us know your preferred cloud, proposed RDS tier, data-dictionary template, naming conventions, and any chassis/VIN decoder API tokens you already hold.
Thank you for your consideration; we look forward to your technical and commercial response.