QC One – Database Model Overview
This document provides an overview of the data model available to customers through the QC One BigQuery connection.
It explains how the main entities relate to each other and what type of information can be retrieved from each table.
The structure supports typical quality-control workflows: inspections, product parameters, remarks, impressions, and unit-level data.
1. Introduction
QC One stores structured inspection data that represents the full lifecycle of a quality check:
products → varieties → units → parameters → impressions → findings
This guide helps you understand:
What data is available
How tables relate
How to query typical QC workflows
Where to find key information such as product attributes, inspection status, or parameter values
2. High-Level Architecture
At a high level, the database consists of the following domains:
Product & Variety Structure
filtered_products
filtered_varieties
These define the product catalog and varieties that inspections are performed on.
Inspection Workflow
filtered_inspections
filtered_inspection_types
filtered_inspection_points
These tables form the core inspection record: who inspected, when, where, and what the result was.
Units & Parameter Checks
filtered_units
filtered_parameters
filtered_parameter_items
filtered_parameter_values
These represent the individual inspected units and their measured parameter values (numeric, string, or boolean).
Impressions (Qualitative Checks)
filtered_impressions
filtered_impression_items
filtered_impression_values
These capture qualitative inspector impressions such as appearance, firmness, smell, or color.
Remarks & Additional Notes
filtered_remarks
filtered_remark_values
Numeric values or percentages linked to quality remarks.
Organisation & User Metadata
filtered_organisations
filtered_inspectors
filtered_licenses
Metadata for customers, suppliers, inspectors, and licenses.
3. Table-by-Table Breakdown
Below is a concise explanation of the key tables.
3.1 Products & Varieties
filtered_products
Contains the list of product categories (e.g., Apples, Peppers, Grapes).
Column | Description |
id | Unique product ID |
name | Product name |
filtered_varieties
Defines varieties associated with each product (e.g., Gala Apples, Red Globe Grapes).
Column | Description |
id | Variety ID |
product_id | Links to filtered_products.id |
name | Variety name |
3.2 Inspections
filtered_inspections
The central table representing each inspection.
Includes:
Inspector (inspector_id)
Status (waiting for planning, arrival, QC, release, etc.)
Scheduled, start, and finish timestamps
Shipment information (shipper, consignee, container, vessel, AWB, etc.)
Assigned users
Inspection type (type_id)
Inspection point (point_id)
This is the main table for inspection-level reporting.
filtered_inspection_types
Dictionary of inspection types (arrival check, intake QC, export inspection, etc.).
filtered_inspection_points
Locations or checkpoints where inspections take place.
3.3 Units (Sample Items)
filtered_units
Represents sample units inspected within an inspection.
One inspection may contain multiple units (different pallets, lots, boxes, etc.).
Contains fields such as:
Size
Grower
Brand
Color
Orchard
Organic flag
Net weight, cartons, GGN, trace data
Links to parameter specifications
Many additional unit attributes
This is the most detailed table in the model.
3.4 Parameters & Values
filtered_parameters
Defines the parameter catalog (e.g., Brix, Size, Defects %, Firmness).
filtered_parameter_items
Optional subitems (e.g., Skin Defects → Scars, Bruises).
filtered_parameter_values
Stores the measured value per unit.
Supports four value types:
Column | Meaning |
numeric_value | Numeric readings (e.g., Brix 14.2) |
string_value | Textual values (e.g., “AA”, “Large”) |
meets_specification | Boolean QC result |
date_value | Date values if applicable |
Links:
unit_id → Unit inspected
parameter_id → Parameter definition
parameter_item_id → Optional subitem
3.5 Impressions (Subjective/Qualitative)
filtered_impressions
Catalog of impression types (e.g., Appearance, Odor, Firmness).
filtered_impression_items
Possible values/scores for each impression (e.g., Good, Fair, Poor).
filtered_impression_values
Stores the selected impression score per inspection and item.
Useful for dashboards and visual QC scoring.
3.6 Remarks
filtered_remarks
Catalog of possible remarks.
filtered_remark_values
Stores:
The remark
The numeric value
The percentage (for defect-related remarks)
Joined via:
unit_id
remark_id
3.7 Organisations, Inspectors & Licenses
filtered_organisations
Represents customer accounts and sub-organisations.
filtered_inspectors
Users who perform inspections.
filtered_licenses
License metadata assigned to organisations.
4. How the Tables Relate
Core relationships:
Product → Variety → Unit → Parameter Values → Remarks
Inspection → Units
Inspection → Impressions
Inspection → Inspector / Organisation / Shipment Info
Visual structure:
Inspection
├── Units
│ ├── Parameter Values
│ └── Remark Values
└── Impression Values
This model supports:
Multiple units per inspection
Multiple parameters per unit
Quantitative and qualitative QC results
5. Common Use Cases & Queries
Get all inspections for a customer
Join filtered_inspections ↔ filtered_organisations.Get all units for an inspection
Join filtered_units on inspection_id.Retrieve parameter results
Join filtered_parameter_values with:filtered_units
filtered_parameters
filtered_parameter_items
Obtain impression scores
Join filtered_impression_values with impression catalog tables.Analyze defects/remarks
Join filtered_remark_values with filtered_remarks.
6. Conclusion
This data model provides QC One customers with full access to detailed, structured inspection information.
It enables:
QC dashboards
Supplier scorecards
Shipment quality history
Automated alerts or anomaly detection
Integrations with ERP, BI, and reporting systems