The Database

What This Does

The database has 6 tables (called 'models' in Prisma). Session stores Shopify login info. Shop stores each merchant's settings and credentials. ProcessedOrder tracks every order we touch. ProcessedLineItem stores per-item customs values. AuditLog records everything that happens for compliance. BetaSignup is just a waitlist.

Why It Matters

Every important piece of data lives here. Understanding the database schema is understanding the entire data model of the app. If you know what fields exist on a ProcessedOrder, you understand what the pipeline actually does.

How It Works

1. 1.When a merchant installs the app, a Session record is created (Shopify handles this) and a Shop record is created with default settings.
2. 2.When an international order with an order-level discount comes in, a ProcessedOrder record is created with status PENDING.
3. 3.As the pipeline processes the order, the ProcessedOrder is updated through statuses: PENDING → CALCULATING → PUSHING → VERIFYING → COMPLETED.
4. 4.Each customs item correction is saved as a ProcessedLineItem with before/after values.
5. 5.Every significant action creates an AuditLog entry — this is our compliance paper trail.
6. 6.ShipStation API credentials are stored ENCRYPTED (never in plain text) in the Shop record.

Key Decisions

• ●All money values are stored as strings, not numbers. This avoids floating point precision issues. The string '29.99' is exact; the number 29.99 in a database could become 29.990000000000002.
• ●ProcessedOrder has a compound unique index on (shopId, shopifyOrderId) — this prevents the same order from being processed twice for the same shop.
• ●The status field uses an enum with 12 possible values, each representing a specific point in the pipeline lifecycle.
• ●Billing counters (ordersProcessedThisCycle) live on the Shop model and reset every 30 days.

What Could Go Wrong

• ●If two workers try to process the same order simultaneously, the optimistic lock (status check before update) prevents double-processing.
• ●If the database goes down mid-pipeline, the order stays in CALCULATING or PUSHING status and can be retried.
• ●If we stored money as floating point numbers, rounding errors would compound across items and cause discrepancies.