Data Model

Data Model

The business schema lives in schema.sql and is mirrored by the SQLAlchemy models in shyne_app/models.py. SQLite runs with PRAGMA foreign_keys = ON, so the foreign-key rules below are enforced. Staff accounts and the access audit log live in a separate auth database and are described at the end.

ER diagram

erDiagram
    customers ||--o{ orders : places
    orders ||--o{ order_items : contains
    orders ||--o{ order_status_events : logs
    orders ||--o| shipments : ships_via
    products ||--o{ order_items : referenced_by
    products ||--o{ product_batches : produced_as
    batches ||--o{ product_batches : yields
    batches ||--o{ batch_ingredients : consumes
    ingredients ||--o{ batch_ingredients : used_in
    product_batches ||--o{ order_items : fulfills

    customers {
        int id PK
        text first_name
        text last_name
        text email UK
        text phone
        text source
        datetime created_at
    }
    products {
        int id PK
        text sku UK
        text name
        numeric price
        int active
        int reorder_threshold
    }
    ingredients {
        int id PK
        text name UK
        numeric stock_quantity
        text unit
        numeric reorder_threshold
    }
    batches {
        int id PK
        text batch_code UK
        text status
        datetime started_at
        datetime ended_at
    }
    product_batches {
        int id PK
        int batch_id FK
        int product_id FK
        text lot_number UK
        int units_produced
        int units_available
        date expiry_date
    }
    orders {
        int id PK
        int customer_id FK
        text order_number UK
        text platform
        numeric total_amount
        text status
        datetime placed_at
    }
    order_items {
        int id PK
        int order_id FK
        int product_id FK
        int product_batch_id FK
        int quantity
        numeric unit_price
    }
    batch_ingredients {
        int id PK
        int batch_id FK
        int ingredient_id FK
        numeric quantity_used
        text unit
    }
    order_status_events {
        int id PK
        int order_id FK
        text event_status
        text message
        datetime created_at
    }
    shipments {
        int id PK
        int order_id FK,UK
        text carrier
        text tracking_number UK
        datetime shipped_at
        datetime delivered_at
    }

Loading

Business tables

customers

People who place orders. Email is unique. source records where the customer came from (Fiverr, Square, or Manual Entry) and is validated in the model. Address fields and country (default USA) support shipping. A customer can have many orders.

products

Sellable items. sku is unique. price is a two-place decimal, active flags whether a product shows up in order entry, and reorder_threshold drives low-stock checks. A product links to its order line items and its production lots.

ingredients

Raw materials and supplies used to make products. name is unique. stock_quantity and reorder_threshold are three-place decimals with a unit (g, kg, L, units). The dashboard flags any ingredient at or below its threshold.

batches

A production run. batch_code is unique. status tracks the run (Open, Closed, Completed), with started_at and optional ended_at. A batch consumes ingredients (batch_ingredients) and yields product lots (product_batches). Both child relationships cascade on delete.

product_batches

A specific lot of a product produced in a batch. lot_number is unique. units_produced and units_available track quantity, and expiry_date is optional. Foreign keys: batch_id cascades on delete, product_id restricts deletion so a product with lots cannot be removed.

orders

A customer order. order_number is unique and auto-generated (ORD-<year>-<seq>). platform is the sales channel (Fiverr, Square, Google Sheets, Direct), status is Placed, Ready, or Completed, and total_amount is summed from line items. customer_id restricts deletion. An order owns its items, status events, and a single shipment, all of which cascade on delete.

order_items

A line on an order: a product, a quantity, and a unit price. Optionally references the product_batch (lot) it was filled from. Deleting the order removes the item; deleting the product is restricted; deleting the source lot sets product_batch_id to NULL so the line survives.

batch_ingredients

The amount of an ingredient consumed by a batch, with its own unit. Deleting the batch removes the row; deleting an ingredient that has been used is restricted.

order_status_events

An append-only history of status changes for an order, each with an event_status and an optional message. The first event is written when the order is created. Cascades on delete with the order.

shipments

Carrier and tracking detail for an order. order_id is unique (one shipment per order) and tracking_number is unique. Holds shipped_at and delivered_at. Cascades on delete with the order.

Indexes and integrity

schema.sql adds indexes on the columns the list pages search and filter on: customer email and source, product SKU, ingredient name, batch and lot codes, order number, order status and customer, and the foreign keys on the join tables. The unique constraints on email, SKU, order number, lot number, and tracking number stop duplicates at the database level rather than only in form validation.

Auth database (separate file)

The auth database holds staff accounts and security records, bound under the auth key:

• admin_users: staff login accounts. Email, hashed password, role, account status (invited, active, suspended), MFA fields, lockout counters, and the access-grant audit columns.
• admin_access_events: an audit trail of who changed which account and the before/after state, written for invites, role changes, status changes, and password resets.
• admin_login_throttles: per-IP failed-login counters and lock windows.
• admin_login_events: a log of login attempts with outcome and failure reason.