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.