Data Dictionary

Reference for the database objects behind the Three Bears Alaska applications - what exists, what it is for, and which side of the BRData boundary it lives on.

---

Ours vs BRData

BRData and Costco each push into Three Bears Alaska's own databases, which also carry tables Three Bears maintains by hand. Those databases replicate into ours - we never read from BRData or Costco directly. On top of that replicated copy sit objects of our own, mostly SmartPO and ordering related. See the Replication and BRData entries in the Glossary for the background, and Logic Apps for how the replication is monitored.

Database migrations for Smart Tools Hub live in the client-three-bears-smart-tools-db-management repo.

A note on casing: the predictive query objects are inconsistently cased. The functions and one stored procedure use PQ_, while the rest use pq_. Both are listed below as they actually appear.

Functions

All user-defined functions are ours. All three are scalar functions, and all three belong to the predictive query - the logic behind the quantity SmartPO suggests to a PIC. See Suggestion in the glossary and SmartPO for where those suggestions surface.

Function	Purpose
PQ_GetHistoricSalesForRange	Returns the sum of sales between two dates at a specific store
PQ_GetPredictionForRange	Returns a predicted amount of sales between two dates. Built on the historic sales function.
PQ_GetBackupPredictionForRange	The fallback prediction, using the last couple of weeks of data rather than historic data

PQ_GetHistoricSalesForRange is the base of the predictive query. Given two dates and a store, it returns the sum of sales across that range. It does no predicting itself - it is the source of the actuals the prediction is built from.

PQ_GetPredictionForRange is the main prediction. Given a date range, it returns a predicted sales amount for that range, leaning on PQ_GetHistoricSalesForRange for the historic figures it works from.

PQ_GetBackupPredictionForRange is the fallback. Where the main prediction works from historic data, this one relies on only the last couple of weeks, making it the option when deeper history is not available or is not usable.

Stored Procedures

Procedure	Purpose
PQ_GetPredictionForOrderItem	Entry point for the predictive query
pq_BaseDataQuery	The base item information SmartPO displays
pq_BaseDataQueryCostco	The same, by item number, for Costco Exclusives
pq_YetToArriveOrders	Ordered stock not yet estimated to have arrived
pq_YetToArriveOrdersCostco	The same, by item number, for Costco Exclusives
POGetContent	The contents of a purchase order, shaped for the report
POGetFilename	The filename for a purchase order file
SearchItems	Search results across all items
SearchProductsOrderRequired	Search results scoped to an order
getItemList	The orderable warehouse product list, used by WOG
GetItemStats	[Placeholder: not yet described]
sp_MSupd_dboWages	Replication-generated, see Sync & Replication

Predictive Query

PQ_GetPredictionForOrderItem is the main starting point of the predictive query. It triggers the other predictive procedures and returns the final prediction along with supporting information such as the accuracy level and debug output. Start here when tracing how a suggestion was produced.

pq_BaseDataQuery returns the base information SmartPO displays for an item - current on hand, price, size, name, and the like. It backs the scan result page and every item returned by search.

pq_BaseDataQueryCostco returns the same information, but takes an item number instead of a UPC. It covers Costco Exclusives and new to vendor items, which have no UPC. See Costco Exclusives.

pq_YetToArriveOrders returns items on orders that have been placed but whose estimated arrival date, based on lead time, has not come yet. This is what shows a PIC that more of an item is already on the way.

pq_YetToArriveOrdersCostco is the same, by item number, for Costco Exclusives without a UPC.

Purchase Orders

POGetContent returns the contents of a purchase order and is called by the Azure Function that builds purchase order files. Whatever this procedure returns is what the function turns into the report, in exactly that shape - so any formatting or restructuring of purchase order contents belongs in this procedure, not in the function.

POGetFilename produces the filename for a purchase order file. It is used by the Logic App when the file contents come back from the Azure Function, and by SmartPO itself to preview the filename before the order is sent.

If you change POGetFilename, check the application as well. The app is believed to call this procedure, but if it builds the name itself instead, the two have to be kept in parity or the preview will stop matching the file that actually goes out.

Search

SearchItems and SearchProductsOrderRequired both back the SmartPO search page. Broadly, one returns all items and the other returns only items an order can see, but which is used at which point takes some digging in the code to confirm.

SearchItems - the one that does not require an order number - is believed to have been used by the printer setup, so that items could be listed without an order ID to scope them. It returns everything, which makes it outdated and less efficient, since nothing filters the result.

Legacy

getItemList is used by the Warehouse Order Guide. It returns the information needed to display the full list of products orderable from the warehouse. The WOG-exclusive views all culminate here.

GetItemStats exists in the database but has not been described yet.

Views

There are only three views in total, which is worth knowing before planning any WOG cleanup - the set is far smaller than "a collection of views" suggests.

View	Notes
WarehouseData	Created the same day as getItemList, which suggests it is the WOG view. Not confirmed.
COGS	[Placeholder: not yet described]
Inventory	[Placeholder: not yet described]

Tables

Roughly 130 tables exist, the large majority replicated from BRData. The ones documented below are those our applications depend on directly.

The remaining tables are not understood on our side. Documenting them would need help from Three Bears Alaska - most likely Mike - rather than exploration on our end.

Ordering

These are SmartPO's own tables.

Table	Notes
Orders	Top-level order records
OrderItems	The items on an order
OrderItemsCostco	The same, for Costco Exclusives with no UPC
OrderStatusTypes	The statuses an order can be in
OrderGuide	Believed to belong to WOG

Orders tracks the orders themselves - who created the order, when it was created, the estimated delivery date, and the order's current status. The estimated delivery date is written by the Logic App after the order is submitted, not by the app. Status covers whether the order is still open and being added to, pending while waiting on the Logic App, or submitted.

OrderItems lists what items are on which order and at what quantity. It also records what prediction was made and how much the user changed it by, which is what makes it possible to tell how far PICs drift from the suggestions. A null prediction means no prediction was offered, so whatever the user entered stands on its own.

OrderItemsCostco is the Costco Exclusive equivalent, existing because those items have no UPC. See Costco Exclusives.

OrderGuide is believed to have been used by the Warehouse Order Guide, though its contents have not been confirmed. It may be ours, or it may be one Mike created on their side that replicates to us.

SmartPO Configuration

The TB_ tables were all created by Mike on the Three Bears Alaska database and replicate to ours. They are reference tables used across the tools we build.

Table	Notes
TB_SmartPO_Vendor_Groups	Vendor groupings by geography and type - heavily referenced
TB_SmartPO_Store_Zones	The geographic zones stores fall under
TB_SmartPO_OH_LeadTime	Lead times by store and vendor zone, plus days on hand goals
TB_SmartPO_CostcoMemberID	Each store's Costco membership ID
TB_SmartPO_OH_LeadTime_temp	Believed unused - a broken earlier version
TB_SmartPO_VendorLead	Believed unused - an earlier attempt at the lead time table
TB_PowerAppDeptReference	WOG only - retires with WOG

TB_SmartPO_Vendor_Groups does far more work than its name suggests, and you will see queries against it throughout the codebase. It lists vendors with the geographic zone they fall under, and also classifies them by type. Those classifications drive behaviour well beyond lead times:

• One group is the warehouse group, which all warehouses fall under
• Another set of groups covers Costco, which is how the app knows an order is a Costco order

That distinction is checked in the predictive algorithm when deciding whether to return a prediction in units or cases, in the search query when deciding whether to return warehouse or Costco items, and in most places that decide whether to pull Costco Exclusives at all.

TB_SmartPO_OH_LeadTime holds how long an order takes to reach a store, keyed by which zone the store is in and which group the vendor is in. It also carries the days on hand goals for the different departments at each store.

TB_SmartPO_CostcoMemberID maps each store to a Costco membership ID. Orders sent to Costco have to be accompanied by one so Costco can confirm the store is authorised and knows which card to charge. In practice, the ID appears to surface only in the name of the purchase order files sent to Costco.

TB_PowerAppDeptReference was built for WOG to categorise items better under major departments. It is no longer used by anything else and should be removed when WOG retires.

TB_SmartPO_OH_LeadTime_temp and TB_SmartPO_VendorLead are both believed to be dead - earlier or broken attempts at the lead time table. They live on the Three Bears Alaska side, which is why they have not been removed. Neither is doing any harm, but both are candidates for cleanup once confirmed unused.

Costco

Table	Notes
CostcoItemListCatalog	The current Costco catalog, updated daily
CostcoWHtoBRDataVndr	Bridge from a Costco warehouse to its BRData vendor
CBCItemListCatalog	Outdated - superseded by CostcoItemListCatalog

CostcoItemListCatalog is the catalog Costco provides, covering every item we can order from them. It updates daily and carries the connective tissue that lets us bridge back to BRData information. See the Costco Item List Catalog entry in the Glossary.

CostcoWHtoBRDataVndr is the bridge between that catalog and BRData. Each item in the catalog comes from a specific Costco warehouse, and the catalog gives you that warehouse's on hand for the item. This table is what tells you which BRData vendor that Costco warehouse corresponds to.

CBCItemListCatalog is the older version and should not be used. CBC stands for Costco Business Center - when we first started consuming the Costco catalog it came only from the Business Center, and CostcoItemListCatalog was created once we began receiving data from multiple Costco locations. The old table appears never to have been deleted.

Check for existing code against CBCItemListCatalog before assuming it is safe to drop.

Items & Vendors

Table	Notes
Item	Every item in BRData, with its UPC and base vendor
StoreOverride	Per store and item overrides, primarily the preferred vendor
Vendors	Every vendor Three Bears Alaska orders from, by name and ID
VendorCost	Bridge between vendors and their items in BRData
InventoryExtraction	Common stock relationships

Item contains every item in BRData along with its UPC and basic information, including the base vendor the item should be ordered from. Costco Exclusives are not in this table, which is the root of most of the UPC trouble described on the SmartPO page.

StoreOverride specifies information about a given item at a given store, and is primarily a vendor override. If a store and item combination has a row here, that vendor is the preferred vendor for the item instead of the default on Item. Anything reading the base vendor from Item without checking this table will be wrong for those combinations.

VendorCost bridges vendors to the version of their items in BRData. Its most important use today is connecting UPCs to Costco item numbers, which also requires CostcoWHtoBRDataVndr to resolve which warehouse is which vendor. It is also a source of more accurate size information, such as how many units are in a case coming from Costco.

VendorCost has collation issues. Joining it incorrectly can severely slow down or outright break queries. There are many existing examples of correct joins against this table throughout the database - copy one of those rather than writing a join from scratch.

InventoryExtraction describes common stock, recording which items are the main item and which are the ingredient, and how they connect. Presence on this table is itself the test: if an item appears here in either position it is common stock, and if it does not appear at all, it is not.

Inventory & Movement

Table	Notes
InventorySummary	Current on hands by item and store
InventoryAdjustment	Changes made to inventory, such as spoilage and transfers
InventoryDetail	Every event affecting an item, including sales
DSDDetail	Receiving information for deliveries against an order

InventorySummary gives the current on hand for any item at any store, including the internal warehouse, which is store 95.

InventoryAdjustment records changes made to inventory - spoilage, transfers between departments, and similar.

InventoryDetail is the broadest of the three. It records essentially every movement of an item, sales included, covering everything InventoryAdjustment does but with less detail per row. Use it when you need every event that has touched an item.

DSDDetail holds receiving information. When an order actually arrives at a store, what was delivered against that purchase order is recorded here.

Checkout

Table	Notes
BasketAnalysis, BasketTenders, BasketTotal, BasketTotalAnalytics	Direct purchases at checkout

A basket here is a shopping basket - the products scanned at checkout and what they cost. Together these tables should give you actual purchases as they happen and direct information about the checkout itself.

What each individual basket table holds is not clear, and sorting that out will take exploration plus questions to Mike.

Stores & Departments

Table	Notes
Stores	Every store, with details such as address
Departments	The granular department an item belongs to
MajorDept	The broad accounting-facing grouping

Departments is the smaller, more granular list and the one most employees mean when they say department. It connects to Item, and every item belongs to a department.

MajorDept holds the broadest categories an item can fall under - fewer groups, each covering more items. It is used primarily by accounting and finance to see how different sections of the operation are performing.

Tax Exemption

Table	Notes
TaxExemptions	The captured exemption records
TaxExemptionTypes	Senior Citizen, Non-Profit, Government, Resale

See Tax Exemption for how these are captured and reported.

Sync & Replication

A handful of objects relate to data synchronisation rather than to any application:

Object	Type	Notes
scope_info_dss, scope_config_dss, schema_info_dss, provision_marker_dss	Tables	The only objects in the DataSync schema - sync framework bookkeeping
sp_MSupd_dboWages	Stored procedure	An auto-generated SQL Server replication procedure for the Wages table
SequelizeMeta	Table	Sequelize migration tracking, from Smart Tools Hub

These are either BRData's or vestigial from an internal replication process we attempted, to move data from the production database into a staging database so test data would match live. Not confirmed either way.