Purpose
This document provides a comprehensive overview of data available through the Parlevel Enterprise Data Service (EDS), a Snowflake-based data warehouse solution for Parlevel VMS customers. The EDS aggregates data from multiple VMS database instances into unified views, providing enterprise customers with consolidated reporting and analytics capabilities.
Please see attached .pdf for a downloadable version of this article.
Overview
Common Fields
All tables include a standard vms column as the first field:
- Field: vms
- Type: varchar(200)
- Description: VMS database name identifier
- Purpose: Identifies the source VMS database for multi-tenant aggregation
- Applies to Vending: Yes
- Applies to Kiosks: Yes
Dimension Tables
Dimension tables contain descriptive attributes about business entities such as machines, locations, products, and users.
machines_and_kiosks
The table outlines the characteristics of machines and micro market kiosks.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the machine or kiosk in the database | |
| name | Yes | Yes | Name of the machine or kiosk | |
| serial_number | Yes | No | Serial number of the hardware | |
| device_id | Yes | No | ID of the telemeter or kiosk used by our software for identification | |
| type | Yes | Yes | Type of machine or kiosk | |
| connected_status | Yes | Yes | Indicates if the machine or kiosk is connected or disconnected | |
| make_model | Yes | No | Machine make or model | |
| location_id | Yes | Yes | Foreign key; relates the machine or kiosk to a location in the locations table | |
| last_sale_at | Yes | Yes | Date and time of the last recorded sale for this machine or kiosk | All times are in UTC |
| last_service_at | Yes | Yes | Date and time of the last recorded service for this machine or kiosk | All times are in UTC |
| offline | Yes | Yes | Indicates if the machine or kiosk is online or offline | |
| last_comm_at | Yes | Yes | Date and time of the last recorded communication for this machine or kiosk | All times are in UTC |
| cst_id | Yes | Yes | Custom ID that can be set by the operator; it represents the Asset ID | |
| kiosk_model | No | Yes | Kiosk model | |
| vmc_model | Yes | No | Control board model; pulled from DEX | |
| paired | No | Yes | In cases where there is more than one kiosk at a micro market, this column will be populated for the secondary kiosk, with the value being the ID of the primary kiosk | |
| last_audit_at | Yes | Yes | Date and time of the last recorded audit for this machine or kiosk | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this machine or kiosk was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| subsidy_group_id | Yes | Yes | Foreign key; if applicable, relates the machine to a subsidy group in the subsidy_groups table that defines which products receive subsidies and at what rates | |
| effective_commission_rate | Yes | Yes | The effective commission rate that applies to this machine after resolving the hierarchical commission logic (broker > machine config > location config > account config > direct values) | |
| effective_commission_type | Yes | Yes | The type of commission that applies (text: “Percentage” or “Flat Rate”) | |
| card_reader | Yes | Yes | Flag indicating if the machine has a card reader installed | |
| cc_commission | Yes | Yes | Flag indicating if card sales should be included in commission calculations (null means use system default) | |
| tax_percent | Yes | Yes | Static tax percentage for this machine if not using dynamic tax calculations | |
| bottler_asset_number | Yes | Yes | Bottler asset number assigned to the machine | |
| total_in_coinmech | Yes | No | Dollar value of money in the machine as of the last DEX file received |
routes
The table contains all existing routes.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the route in the database | |
| name | Yes | Yes | Name of the route | |
| driver_id | Yes | Yes | Foreign key; relates the route to a driver in the users table | |
| truck_id | Yes | Yes | Foreign key; relates the route to a truck in the trucks table | |
| deleted_at | Yes | Yes | If this column is not null, it means this route was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
scheduled_routes
This table contains services, which are occurrences of a route in the routes table.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the scheduled route in the database | |
| route_id | Yes | Yes | Foreign key; relates the scheduled route to a route in the routes table | |
| driver_id | Yes | Yes | Foreign key; relates the route occurrence to the driver it was assigned to at the time it happened, referencing the users table | |
| truck_id | Yes | Yes | Foreign key; relates the route occurrence to the truck it was assigned to at the time it happened, referencing the trucks table | |
| status | Yes | Yes | Status of the route occurrence | |
| created_at | Yes | Yes | Date and time the route occurrence was planned | All times are in UTC |
| started_at | Yes | Yes | Date and time the route occurrence actually started | All times are in UTC |
| finished_at | Yes | Yes | Date and time the route occurrence was actually completed | All times are in UTC |
| service_at | Yes | Yes | Date and time the route occurrence was planned for | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this route occurrence was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
locations
This table lists locations.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the location in the database | |
| name | Yes | Yes | Name of the location | |
| type_name | Yes | Yes | Type of location (text: e.g. “Hospital”, “Office”, “School”, “Factory”, etc.) | |
| account_id | Yes | Yes | Foreign key; relates the location to an intermediary client account in the accounts table | |
| route_id | Yes | Yes | Foreign key; relates the location to a route in the routes table | |
| created_at | Yes | Yes | Date and time the location was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the location was last updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this location was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| tax_group_id | Yes | Yes | Foreign key; relates the location to a tax group in the tax_groups table which defines which tax rates apply at this location | |
| effective_commission_rate | Yes | Yes | The effective commission rate that applies to this location after resolving the hierarchical commission logic | |
| effective_commission_type | Yes | Yes | The type of commission that applies (text: “Percentage” or “Flat Rate”) | |
| sales_tax | Yes | Yes | Static sales tax percentage for this location if not using tax groups | |
| ocs_status | Yes | Yes | Status indicating if this location uses OCS (Office Coffee Service) | |
| ocs_route_id | Yes | Yes | Foreign key; relates the location to the route that services OCS at this location in the routes table | |
| street | Yes | Yes | Street address of the location | Retrieved from addresses table via polymorphic join; NULL if no address on file |
| city | Yes | Yes | City of the location | Retrieved from addresses table via polymorphic join; NULL if no address on file |
| state | Yes | Yes | State of the location | Retrieved from addresses table via polymorphic join; NULL if no address on file |
| zipcode | Yes | Yes | Zip code of the location | Retrieved from addresses table via polymorphic join; NULL if no address on file |
| country | Yes | Yes | Country of the location | Retrieved from addresses table via polymorphic join; defaults to USA |
| lat | Yes | Yes | Latitude coordinate of the location | Retrieved from addresses table via polymorphic join; NULL if address not geocoded |
| lng | Yes | Yes | Longitude coordinate of the location | Retrieved from addresses table via polymorphic join; NULL if address not geocoded |
accounts
This table contains information about accounts—these are the clients of operators.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the account in the database | |
| name | Yes | Yes | Name of the account | |
| number | Yes | Yes | Number assigned to the account | |
| created_at | Yes | Yes | Date and time the account was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the account was last updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this account was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| effective_commission_rate | Yes | Yes | The effective commission rate that applies to this account after resolving the hierarchical commission logic | |
| effective_commission_type | Yes | Yes | The type of commission that applies (text: “Percentage” or “Flat Rate”) | |
| cost_of_goods_commissions | Yes | Yes | Flag indicating if cost of goods should be deducted from gross sales when calculating commissions (1=yes, 0=no) | |
| broker_name | Yes | Yes | Name of the broker who manages this account (if applicable) |
users
This table contains information on system users, including roles such as admin, manager, and
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the user in the database | |
| username | Yes | Yes | Username of the user | |
| role | Yes | Yes | Role assigned to the user | |
| active | Yes | Yes | Flag indicating if the user is active | |
| created_at | Yes | Yes | Date and time the user was created | All times are in UTC |
| updated_at | Yes | Yes | Date and time the user was last updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this user was soft deleted, it won’t be possible to use it to access our systems; it indicates the date and time it was deleted | All times are in UTC |
trucks
This table lists trucks.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the truck in the database | |
| name | Yes | Yes | Name of the truck | |
| created_at | Yes | Yes | Date and time the truck was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the truck was last updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this truck was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
products
This table lists products along with some of their characteristics.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the product in the database | |
| name | Yes | Yes | Name of the product | |
| brand | Yes | Yes | Brand of the product | Retrieved from operator_product_brands table via brand_id; products without a valid brand are excluded from this view |
| manufacturer | Yes | Yes | Manufacturer of the product | Retrieved from operator_product_manufacturers table via manufacturer_id; products without a valid manufacturer are excluded from this view |
| type | Yes | Yes | Type of product | Retrieved from products table and mapped via eds_enum_mappings; shows product type classification |
| case_quantity | Yes | Yes | Units per case of this product | |
| price | Yes | Yes | Current price of the product | |
| cost | Yes | Yes | Current cost of the product | |
| status | Yes | Yes | Status of the product | Retrieved from wh_inventory table; shows warehouse inventory status for the product; products without warehouse inventory are excluded from this view |
| custom_name | Yes | Yes | Flag that indicates if the name of the product was modified by the operator | |
| cst_id | Yes | Yes | Customer code for the product | |
| tax_class_id | Yes | Yes | Foreign key; relates the product to a tax class in the product_tax_classes table which defines how the product is taxed | |
| created_at | Yes | Yes | Date and time the product was created | All times are in UTC |
| updated_at | Yes | Yes | Date and time the product was last updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this product was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| size | Yes | Yes | Size of the product (e.g. “12 Fluid Ounce CAN”, “20 oz”) | Retrieved from product_sizes table via product_size_id; defaults to “Not Specified” if no size on file |
| container | Yes | Yes | Container type of the product (e.g. Can, Bottle, Bag, Bar, Box) | Retrieved from products table and mapped via eds_enum_mappings (table_name=‘products’, column_name=‘container_type’) |
product_upcs
This table stores product UPCs.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the product_upc in the database | |
| product_id | Yes | Yes | Foreign key; relates the UPC to a product in the products table | |
| upc | Yes | Yes | UPC (12-digit barcode used to identify product) | |
| created_at | Yes | Yes | Date and time the UPC was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the UPC was last updated | All times are in UTC |
mm_users
This table lists micro market users, those with an account and a Parlevel Wallet.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | No | Yes | Primary key; ID of the mm_user in the database | |
| account_id | No | Yes | Foreign key; relates the user to an account in the accounts table | |
| first_name | No | Yes | First name of the user | |
| last_name | No | Yes | Last name of the user | |
| No | Yes | Email of the user | ||
| user_number | No | Yes | User number input by the user when creating their account | |
| last_activity | No | Yes | Date and time of last registered activity | All times are in UTC |
| balance | No | Yes | Current account balance | |
| email_receipts | No | Yes | Flag indicating if the user opted for email receipts | |
| created_at | No | Yes | Date and time of user creation | All times are in UTC |
| updated_at | No | Yes | Date and time of user last update | All times are in UTC |
| deleted_at | No | Yes | If this column is not null, it means this user was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
mm_assets
This table contains information about micro market assets.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | No | Yes | Primary key; ID of the mm_asset in the database | |
| cst_id | No | Yes | Custom ID assigned to the asset by the operator | |
| machine_id | No | Yes | Foreign key; relates the asset to a machine or kiosk in the machines_and_kiosks table | |
| location_id | No | Yes | Foreign key; relates the asset to a location in the locations table | |
| type | No | Yes | Type of asset | |
| name | No | Yes | Name assigned to the asset | |
| created_at | No | Yes | Date and time the asset was created in the VMS | All times are in UTC |
| updated_at | No | Yes | Date and time the asset was last updated | All times are in UTC |
| deleted_at | No | Yes | If this column is not null, it means this asset was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC Fact Tables Fact tables contain transactional and event data, recording business activities and measurements. |
Fact Tables
services
This table contains information about the planning and service of individual machines or micro
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the service in the database | |
| scheduled_route_id | Yes | Yes | Foreign key; relates the service to a route occurrence in the scheduled_routes table | |
| machine_id | Yes | Yes | Foreign key; relates the service to a machine or kiosk in the machines_and_kiosks table | |
| location_id | Yes | Yes | Foreign key; relates the service to the location it was assigned to at the time, referencing the locations table | |
| status | Yes | Yes | Status of the service to the machine or micro market | |
| type | Yes | Yes | Type of service planned for the machine or micro market | |
| bag_number | Yes | Yes | Number on the money bag | |
| amount_sold | Yes | Yes | Total amount of money sold from previous service to this one, and expected to be picked up | |
| amount_collected_bills | Yes | Yes | Amount of money in bills collected from service; input during consolidation | |
| amount_collected_coins | Yes | No | Amount of money in coins collected from service; input during consolidation | |
| amount_refunds | Yes | Yes | Amount of money refunded to client or consumer during service; input by the driver | |
| amount_change_added | Yes | No | Amount of money added to the machine as change during service; input by the driver | |
| overshort | Yes | Yes | Difference between expected amount of money and collected amount of money; calculated after consolidation | |
| amount_card_sales | Yes | Yes | Amount of money sold in cashless sales from previous service to this one | |
| route_order | Yes | Yes | The position or sequence in the route where this machine or micro market will be serviced | |
| dex_expected_bills | Yes | No | Amount of money expected to be collected in bills, according to DEX | |
| dex_expected_coins | Yes | No | Amount of money expected to be collected in coins, according to DEX | |
| audit | Yes | Yes | Flag indicating if an audit was scheduled | |
| audit_at | Yes | Yes | Date and time machine or micro market was audited | All times are in UTC |
| wallet_purchases_total | No | Yes | Amount of money sold in wallet credit between last service and this one | |
| created_at | Yes | Yes | Date and time the service was planned | All times are in UTC |
| updated_at | Yes | Yes | Date and time the service was updated | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this service was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| user_id | Yes | Yes | User that executed this service | |
| serviced_at | Yes | Yes | Date and time the service was executed | All times are in UTC |
fill_rate
This table describes the changes in inventory of a specific slot because of a service.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the fill_rate record in the database | |
| daily_route_id | Yes | Yes | Foreign key; relates the inventory change to a scheduled route in the scheduled_routes table | |
| service_id | Yes | Yes | Foreign key; relates the inventory change to a service in the services table | |
| machine_id | Yes | Yes | Foreign key; relates the inventory change to a machine or micro market in the machines_and_kiosks table | |
| slot_id | Yes | Yes | Foreign key; relates the inventory change to a slot in the slots table | |
| product_id | Yes | Yes | Foreign key; relates the inventory change to a product in the products table | |
| former_product_id | Yes | Yes | Foreign key; if there was a product change, it indicates the previous product, referencing the products table | |
| paired | Yes | Yes | Flag indicating if this was a paired slot at the time of the inventory change | |
| parlevel | Yes | Yes | Parlevel of the slot | |
| inventory_before | Yes | Yes | Inventory before service | |
| inventory_after | Yes | Yes | Inventory after service | |
| price | Yes | Yes | Price of an individual product at the time | |
| inventory_stocked_final | Yes | Yes | Quantity of units stocked in the machine or micro market during service |
slots
This table contains information of each slot in a machine or micro market, and its current settings
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the slot in the database | |
| machine_id | Yes | Yes | Foreign key; relates the slot to a machine or kiosk in the machines_and_kiosks table | |
| row | Yes | Yes | Row of the slot | |
| column | Yes | Yes | Column of the slot | |
| parlevel | Yes | Yes | Current parlevel of the slot | |
| capacity | Yes | Yes | Current capacity of the slot | |
| quantity | Yes | Yes | Current inventory of the slot | |
| price | Yes | Yes | Current price of the slot, per the VMS | |
| price_dex | Yes | No | Current price of the slot, per the last received DEX | |
| product_id | Yes | Yes | Foreign key; relates the slot to product in the products table | |
| cst_alias | Yes | Yes | Custom name that the operator can give the slot | |
| mdb_alias | Yes | Yes | MDB protocol alias for the slot | |
| paired | Yes | Yes | If the slot is paired to another one, this column will contain the id of the primary slot | |
| daily_consumption_rate | Yes | Yes | Average units purchased from this slot per day | |
| mm_asset_id | No | Yes | Foreign key; relates the slot to an asset in the mm_assets table | |
| created_at | Yes | Yes | Date of creation | All times are in UTC |
| updated_at | Yes | Yes | Date of last update | All times are in UTC |
| deleted_at | Yes | Yes | If this column is not null, it means this slot was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
sales
This table records individual slot sales. In vending, sales are tracked by the total number of units
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the sale in the database | |
| slot_id | Yes | Yes | Foreign key; relates the sale to a slot in the slots table | |
| product_id | Yes | Yes | Foreign key; relates the sale to a product in the products table | |
| quantity | Yes | Yes | Number of units sold | |
| price | Yes | Yes | Price per unit at the time of the sale | |
| total_tax | No | Yes | Total amount of taxes charged | |
| total_price | Yes | Yes | Total amount of money received (quantity*price + taxes) | |
| cost_per_item | Yes | Yes | Cost per unit at the time of the sale | |
| created_at | Yes | Yes | Date and time the sale was recorded | All times are in UTC |
| mm_transaction_id | No | Yes | Foreign key; relates the sale to a transaction in the mm_transactions table | |
| machine_id | Yes | Yes | Foreign key; relates the sale to a machine or kiosk in the machines_and_kiosks table | |
| location_id | Yes | Yes | Foreign key; relates the sale to a location in the locations table | |
| route_id | Yes | Yes | Foreign key; relates the sale to a route in the routes table |
alerts
This table registers alerts.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the alert in the database | |
| machine_id | Yes | Yes | Foreign key; relates the alert to a machine or kiosk in the machines_and_kiosks table | |
| type | Yes | Yes | Type of alert | |
| category | Yes | Yes | Category of the alert | |
| status | Yes | Yes | Status of the alert | |
| message | Yes | Yes | Message displayed to the operator when the alert is triggered | |
| slot_id | Yes | Yes | Foreign key; if applicable, it relates the alert to a slot in the slots table | |
| created_at | Yes | Yes | Date and time the alert was first registered | All times are in UTC |
| updated_at | Yes | Yes | Date and time the alert was last updated; if the alert has been solved, this will reflect the time of resolution | All times are in UTC |
slot_shrinkage
This table records shrinkage in a specific slot, typically noted by a driver during the servicing of the
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the shrinkage record in the database | |
| user_id | Yes | Yes | Foreign key; user that recorded the shrinkage, referencing the users table | |
| product_id | Yes | Yes | Foreign key; relates the shrinkage to a product in the products table | |
| created_at | Yes | Yes | Date and time the shrinkage was recorded | All times are in UTC |
| updated_at | Yes | Yes | Date and time the shrinkage was last updated | All times are in UTC |
| add | Yes | Yes | If there was an increase in the inventory, this column indicates the number of units added | |
| reentry | Yes | Yes | Units that need to go back into the warehouse | |
| returned | Yes | Yes | Amount of units that actually came back into the warehouse | |
| total_difference | Yes | Yes | Difference between units recorded as returned and units that actually came back into the warehouse | |
| service_id | Yes | Yes | Foreign key; relates the shrinkage to a service in the services table | |
| verified_by | Yes | Yes | Foreign key; user that verified the shrinkage, referencing the users table | |
| verified_at | Yes | Yes | Date and time the shrinkage was verified | All times are in UTC |
| slot_id | Yes | Yes | Foreign key; relates the slot to a service in the slots table | |
| cost | Yes | Yes | Total cost of the shrinkage (individual cost*quantity) | |
| loss | Yes | Yes | Total sales loss of the shrinkage (individual price*quantity) | |
| deleted_at | Yes | Yes | If this column is not null, it means this shrinkage record was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
| machine_id | Yes | Yes | Foreign key; relates the shrinkage to a machine or kiosk in the machines_and_kiosks table |
mm_transactions
This table lists micro market transactions and their details.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | No | Yes | Primary key; ID of the mm_transaction in the database | |
| type | No | Yes | Type of transaction | |
| subtotal | No | Yes | Total to pay before adding taxes | |
| tax | No | Yes | Amount to pay in taxes | |
| deposit | No | Yes | Amount to pay for deposit tax (applicable in certain regions when purchasing specific packages, such as bottles) | |
| two_tier_fee | No | Yes | Configurable charge applied to consumers for various reasons, such as service type or payment method (commonly when paying by card) | |
| paid_wallet | No | Yes | Amount paid with wallet credit | |
| paid_cash | No | Yes | Amount paid with cash | |
| paid_cashless | No | Yes | Amount paid with card | |
| credit_to_wallet | No | Yes | Amount credited to the wallet after the transaction (applicable for wallet reloads or when change from a cash payment is credited to the wallet) | |
| balance_after_charge | No | Yes | Wallet balance after the transaction | |
| mm_user_id | No | Yes | Foreign key: if applicable, relates the transaction to a user in the mm_users table | |
| machine_id | No | Yes | Foreign key; relates the transaction to a machine or kiosk in the machines_and_kiosks table | |
| location_id | No | Yes | Foreign key; relates the transaction to a location in the locations table | |
| route_id | No | Yes | Foreign key; relates the transaction to a route in the routes table | |
| status | No | Yes | Status of the transaction | |
| exit_btn | No | Yes | Method of exiting the transaction (completed, timeout, cancellation, etc.) | |
| source | No | Yes | Source of the transaction (kiosk or Koin) | |
| created_at | No | Yes | Date and time the transaction was recorded in the VMS | All times are in UTC |
| completed_at | No | Yes | Date and time the transaction was really completed | All times are in UTC |
credit_card_sales
This table records credit card sales totals for machines with card readers; used for commission
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the credit card sale record in the database | |
| machine_id | Yes | Yes | Foreign key; relates the credit card sale to a machine in the machines_and_kiosks table | |
| total_sold | Yes | Yes | Total amount of sales via credit card | |
| two_tier_fee | Yes | Yes | Configurable charge applied to credit card transactions (two-tier processing fee) | |
| created_at | Yes | Yes | Date and time the credit card sale was recorded | All times are in UTC |
| updated_at | Yes | Yes | Date and time the credit card sale was last updated | All times are in UTC |
offline_machine_slot_prekits
This table captures the prekitting plan for offline machines (what to stock), showing the planned
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the offline_machine_slot_prekit record in the database | |
| daily_route_id | Yes | Yes | Foreign key; relates to a scheduled route in the scheduled_routes table | Denormalized from daily_route_machines via service_id |
| service_id | Yes | Yes | Foreign key; relates to a service in the services table | Source column: daily_route_machine_id |
| machine_id | Yes | Yes | Foreign key; relates to a machine in the machines_and_kiosks table | Denormalized from machine_slots via slot_id |
| slot_id | Yes | Yes | Foreign key; relates to a slot in the slots table | Source column: machine_slot_id |
| product_id | Yes | Yes | Foreign key; relates to the product being prekitted in the products table | |
| former_product_id | Yes | Yes | Foreign key; if there was a product change, the previous product in the products table | Nullable field |
| price | Yes | Yes | Price of the product at prekitting time | |
| former_price | Yes | Yes | Previous price if there was a price change | Nullable field |
| start_quantity | Yes | Yes | Quantity in slot at prekitting time (snapshot before service) | |
| prekit_quantity | Yes | Yes | Number of individual units to prekit for this slot | |
| end_quantity | Yes | Yes | Expected quantity after stocking (start_quantity + prekit_quantity) Used for verification | |
| prekit_units | Yes | Yes | Number of cases/boxes to prekit (prekit_quantity ÷ min_prekit_unit) | Decimal field |
| product_change | Yes | Yes | Flag indicating if this prekit involves a product change | Boolean |
| price_change | Yes | Yes | Flag indicating if this prekit involves a price change | Boolean |
| no_stocked | Yes | Yes | Flag indicating product was prekitted but not actually stocked on truck | Boolean |
| modified_quantity | Yes | Yes | Modified prekit quantity if changed from original plan | Nullable field |
| modified_units | Yes | Yes | Modified prekit units if changed from original plan | Nullable field |
| original_prekitting | Yes | Yes | Original calculated prekit quantity before any modifications | Nullable field |
offline_service_slots
This table records manual/offline service slot transactions entered by drivers during service when
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the offline service slot record in the database | |
| daily_route_id | Yes | Yes | Foreign key; relates the inventory change to a scheduled route in the scheduled_routes table | Denormalized from daily_route_machines via service_id |
| service_id | Yes | Yes | Foreign key; relates the inventory change to a service in the services table | Source column: daily_route_machine_id |
| machine_id | Yes | Yes | Foreign key; relates the inventory change to a machine or micro market in the machines_and_kiosks table | |
| slot_id | Yes | Yes | Foreign key; relates the inventory change to a slot in the slots table | Source column: machine_slot_id |
| product_id | Yes | Yes | Foreign key; relates the inventory change to a product in the products table | |
| route_id | Yes | Yes | Foreign key; relates to the master route in the routes table | |
| location_id | Yes | Yes | Foreign key; relates the inventory change to a location in the locations table | |
| parlevel | Yes | Yes | Parlevel of the slot at the time of service | |
| price | Yes | Yes | Price of an individual product at the time | |
| cost_per_item | Yes | Yes | Cost per item at the time of service | |
| inventory_stocked | Yes | Yes | Quantity of units stocked in the machine or micro market during service | Source column: stocked |
| inventory_taken | Yes | Yes | Quantity removed as waste/shrinkage during service | Source column: taken |
| product_change | Yes | Yes | Flag indicating if there was a product change during this service | |
| manual_count | Yes | Yes | Manual inventory count if performed during service | Nullable field |
| created_at | Yes | Yes | Date and time the offline service slot record was created | All times are in UTC Subsidy Tables These tables support subsidy calculations, allowing operators to track and calculate subsidy payments for eligible products. |
Subsidy Tables
subsidy_groups
This table defines subsidy group configurations that can be assigned to machines to specify which
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the subsidy group in the database | |
| name | Yes | Yes | Name of the subsidy group | |
| created_at | Yes | Yes | Date and time the subsidy group was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the subsidy group was last updated | All times are in UTC |
subsidy_sub_groups
This table defines subsidy rates and product categories within each subsidy group (e.g. beverages
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the subsidy sub- group in the database | |
| subsidy_group_id | Yes | Yes | Foreign key; relates the sub-group to a subsidy group in the subsidy_groups table | |
| name | Yes | Yes | Name of the subsidy category (e.g. “Beverages”, “Snacks”, “Healthy Options”) | |
| subsidy_amount | Yes | Yes | Dollar amount subsidized per unit sold (e.g. 0.50 means $0.50 per unit) | |
| created_at | Yes | Yes | Date and time the subsidy sub- group was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the subsidy sub- group was last updated | All times are in UTC |
subsidy_sub_group_items
This table maps which products qualify for subsidies within each subsidy sub-group; join this with
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the subsidy sub-group item in the database | |
| subsidy_sub_group_id | Yes | Yes | Foreign key; relates the item to a subsidy sub-group in the subsidy_sub_groups table | |
| product_id | Yes | Yes | Foreign key; relates to a product in the products table that qualifies for this subsidy rate | |
| created_at | Yes | Yes | Date and time the mapping was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the mapping was last updated | All times are in UTC Tax Tables These tables support multi-jurisdictional tax calculations across products and locations. |
Tax Tables
tax_groups
This table defines tax group configurations that are assigned to locations to specify which tax rates
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the tax group in the database | |
| name | Yes | Yes | Name of the tax group | |
| created_at | Yes | Yes | Date and time the tax group was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the tax group was last updated | All times are in UTC |
product_tax_classes
This table defines tax classifications for products (e.g. food, beverage, non-food).
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the product tax class in the database | |
| name | Yes | Yes | Name of the tax class | |
| created_at | Yes | Yes | Date and time the tax class was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the tax class was last updated | All times are in UTC |
tax_group_items
This table links tax rates to product classes within tax groups; defines the actual tax percentages
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | Yes | Yes | VMS database name | |
| id | Yes | Yes | Primary key; ID of the tax group item in the database | |
| tax_group_id | Yes | Yes | Foreign key; relates to a tax group in the tax_groups table | |
| product_tax_class_id | Yes | Yes | Foreign key; relates to a product tax class in the product_tax_classes table | |
| tax_percent | Yes | Yes | Tax percentage rate (e.g. 8.5 for 8.5% tax) | |
| taxable_percent | Yes | Yes | Percentage of the product price that is taxable (typically 100 but can vary) | |
| surcharge_amount | Yes | Yes | Surcharge amount applied to products in this tax class | |
| surcharge_type | Yes | Yes | Type of surcharge (e.g. percentage or flat amount) | |
| surcharge_taxable | Yes | Yes | Flag indicating if the surcharge amount is taxable | |
| deposit_amount | Yes | Yes | Deposit amount applied to products in this tax class (e.g. bottle deposit) | |
| deposit_taxable | Yes | Yes | Flag indicating if the deposit amount is taxable | |
| ocs_tax_percent | N/A | N/A | Tax percentage rate specifically for OCS products | |
| ocs_taxable_percent | N/A | N/A | Percentage of the OCS product price that is taxable | |
| mm_tax_percent | No | Yes | Tax percentage rate specifically for micro market products | |
| mm_taxable_percent | No | Yes | Percentage of the micro market product price that is taxable | |
| created_at | Yes | Yes | Date and time the tax group item was created in the VMS | All times are in UTC |
| updated_at | Yes | Yes | Date and time the tax group item was last updated | All times are in UTC OCS Tables These tables support OCS (Office Coffee Service) operations, including equipment management, service invoicing, and rental tracking. |
OCS Tables
ocs_stations
This table defines OCS (Office Coffee Service) stations/break rooms where coffee equipment is
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the OCS station in the database | |
| name | N/A | N/A | Name of the OCS station (e.g. “Main Break Room” or “2nd Floor Kitchen”) | |
| location_id | N/A | N/A | Foreign key; relates the station to a location in the locations table | |
| description | N/A | N/A | Description of the station | |
| last_serviced_at | N/A | N/A | Date and time the station was last serviced | All times are in UTC |
| last_maintenance_at | N/A | N/A | Date and time equipment at this station last had maintenance performed | All times are in UTC |
| notes | N/A | N/A | Notes about the station | |
| cst_id | N/A | N/A | Custom ID assigned to the station by the operator | |
| created_at | N/A | N/A | Date and time the station was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the station was last updated | All times are in UTC |
| deleted_at | N/A | N/A | If this column is not null, it means this station was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
ocs_invoices
This table contains OCS invoices generated for each service visit; the main transaction table for OCS
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the OCS invoice in the database | |
| driver_id | N/A | N/A | Foreign key; relates the invoice to the driver who serviced it in the users table | |
| daily_route_id | N/A | N/A | Foreign key; relates the invoice to the scheduled route in the scheduled_routes table | |
| location_id | N/A | N/A | Foreign key; relates the invoice to the location in the locations table | |
| invoice_number | N/A | N/A | Unique invoice number | |
| status | N/A | N/A | Status of the invoice: 1=Draft, 2=Pending, 3=Sent, 4=Authorized, 5=Paid, 6=Partially Paid | |
| type | N/A | N/A | Type of invoice | |
| total_amount | N/A | N/A | Total invoice amount (products + equipment rental) | |
| total_paid | N/A | N/A | Amount paid on this invoice | |
| serviced_at | N/A | N/A | Date and time the service was performed | All times are in UTC |
| payment_at | N/A | N/A | Date and time payment was received | All times are in UTC |
| tax_override_amount | N/A | N/A | Manual tax override amount if operator adjusts tax on the invoice | |
| notes | N/A | N/A | Notes about the invoice | |
| created_at | N/A | N/A | Date and time the invoice was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the invoice was last updated | All times are in UTC |
| deleted_at | N/A | N/A | If this column is not null, it means this invoice was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
ocs_orders
This table groups products delivered to a specific station within an OCS invoice.
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the OCS order in the database | |
| ocs_station_id | N/A | N/A | Foreign key; relates the order to a station in the ocs_stations table | |
| ocs_invoice_id | N/A | N/A | Foreign key; relates the order to an invoice in the ocs_invoices table | |
| notes | N/A | N/A | Notes on the order | |
| daily_route_id | N/A | N/A | Foreign key; relates to the scheduled route in the scheduled_routes table | |
| created_at | N/A | N/A | Date and time the order was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the order was last updated | All times are in UTC |
| deleted_at | N/A | N/A | If this column is not null, it means this order was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
ocs_order_items
This table contains individual products delivered in an OCS order; the source of product revenue for
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the order item in the database | |
| ocs_order_id | N/A | N/A | Foreign key; relates the item to an order in the ocs_orders table | |
| product_id | N/A | N/A | Foreign key; relates the item to a product in the products table | |
| quantity | N/A | N/A | Quantity of the product delivered | |
| price | N/A | N/A | Price per unit | |
| total | N/A | N/A | Total amount for this line item (quantity × price + tax) | |
| parlevel | N/A | N/A | Par level for this product at the station | |
| tax_percent | N/A | N/A | Tax percentage applied to this product | |
| tax_amount | N/A | N/A | Tax amount calculated for this product (tax_percent × total_price) | |
| surcharge | N/A | N/A | Surcharge amount applied to this item | |
| deposit | N/A | N/A | Deposit amount (e.g. bottle deposit) | |
| created_at | N/A | N/A | Date and time the order item was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the order item was last updated | All times are in UTC |
| deleted_at | N/A | N/A | If this column is not null, it means this order item was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
ocs_equipment_order_items
This table contains equipment rental line items on OCS invoices; shows which equipment was billed
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the equipment order item in the database | |
| ocs_order_id | N/A | N/A | Foreign key; relates to an order in the ocs_orders table (which then links to ocs_invoices) | |
| equipment_id | N/A | N/A | Foreign key; relates to equipment in the equipments table | |
| rental_type | N/A | N/A | Type of rental: 1=Rental (charged), 2=Complimentary (no charge) | |
| rent_amount | N/A | N/A | Rental charge amount for this billing period | |
| tax_rate_equipment | N/A | N/A | Tax rate applied to equipment rental (calculated and stored at invoice time) | |
| status | N/A | N/A | Status of the equipment order item | |
| rental_frequency | N/A | N/A | Billing frequency: 1=First Service (one-time), 2=Monthly, 3=Annual, 4=Quarterly | |
| rental_period_end | N/A | N/A | End date of the rental period being billed on this invoice | All times are in UTC |
equipments
This table contains OCS equipment inventory (coffee machines, brewers, etc.).
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the equipment in the database | |
| equipment_type | N/A | N/A | Type of equipment (denormalized from equipment_types.name) | |
| serial_number | N/A | N/A | Serial number of the equipment | |
| brand | N/A | N/A | Brand name of the equipment | |
| model | N/A | N/A | Model name of the equipment | |
| purchased_at | N/A | N/A | Date and time the equipment was purchased | All times are in UTC |
| description | N/A | N/A | Description of the equipment | |
| created_at | N/A | N/A | Date and time the equipment was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the equipment was last updated | All times are in UTC |
| deleted_at | N/A | N/A | If this column is not null, it means this equipment was soft deleted, it won’t appear in our systems; it indicates the date and time it was deleted | All times are in UTC |
ocs_station_equipments
This table links OCS equipment to stations; defines the rental agreements for which coffee
| Field | Vending | Kiosks | Description | Notes |
|---|---|---|---|---|
| vms | N/A | N/A | VMS database name | |
| id | N/A | N/A | Primary key; ID of the station equipment record in the database | |
| ocs_station_id | N/A | N/A | Foreign key; relates to a station in the ocs_stations table | |
| equipment_id | N/A | N/A | Foreign key; relates to equipment in the equipments table | |
| rental_type | N/A | N/A | Type of rental agreement: 1=Rental, 2=Complimentary | |
| rent_amount | N/A | N/A | Monthly rental amount charged for this equipment | |
| status | N/A | N/A | Status of the equipment installation | |
| installed_at | N/A | N/A | Date and time the equipment was installed at the station | All times are in UTC |
| purchased_at | N/A | N/A | Date and time the equipment was purchased (if applicable) | All times are in UTC |
| description | N/A | N/A | Description of the equipment installation | |
| rental_frequency | N/A | N/A | Billing frequency for equipment rental | |
| rental_day | N/A | N/A | Day of month when rental is billed | |
| rental_month | N/A | N/A | Month when annual rental is billed (for annual rentals only) | |
| created_at | N/A | N/A | Date and time the record was created in the VMS | All times are in UTC |
| updated_at | N/A | N/A | Date and time the record was last updated | All times are in UTC |