Engineering Log #003 — A Database Schema That Separates Perception From Truth
Why the platform's SQLite schema keeps raw model output, ground truth, and decisions in separate tables instead of one.
Mehdi (Masoud) Variji
8/1/20263 min read
Why the Schema Came Before the Numbers
The previous engineering logs described what the platform detects and how those detections are transformed into room-level occupancy and a live digital twin.
This log moves deeper into the system and looks at something that is mostly invisible from the dashboard: the database architecture underneath it.
I designed the database schema before having accuracy figures to report. That order was intentional.
A database schema determines what can actually be measured later. If perception, ground truth and system decisions are mixed together, no amount of model tuning can produce a trustworthy evaluation.
⸻
The Core Split: Output Versus Truth
The architecture is built around one important separation:
What the AI believes happened and what has been independently confirmed to have happened are stored separately.
Perception output — including detections, zone assignments and confidence values — is recorded continuously while the system operates.
Ground-truth observations are stored independently and are created only when an observation is manually confirmed through the annotation system.
This distinction is fundamental.
If model predictions and ground truth were stored together, the platform would effectively be comparing its predictions against itself.
Separating them creates the foundation for measuring real detection and zone-assignment accuracy later.
⸻
What the Schema Covers
The current SQLite database contains sixteen tables covering several different parts of the platform.
They are organised into functional categories:
Perception
Raw model output
Detection information
Confidence values
Frame-level observations
Zone and Home State
Zone occupancy
Home occupancy state
Zone transitions
Presence history
Ground Truth
Manually confirmed observations
Verified occupancy states
Reference data for future accuracy evaluation
Events
Door events
Zone transitions
Other discrete system events
Sensors
Temperature
Humidity
Environmental readings
Devices and Decisions
Device states
Device actions
Decision records
Decision evaluation data
Annotations
Manual review information
Metadata associated with ground-truth observations
Each category answers a different engineering question.
Perception asks:
“What did the model see?”
Ground truth asks:
“What actually happened?”
The decision layer asks:
“What did the system conclude, and why?”
Keeping these layers structurally separate prevents one part of the system from quietly becoming the evidence used to validate itself.
⸻
Why This Matters More Than It Looks
Most of this architecture is invisible on the dashboard.
It does not immediately produce an impressive chart, prediction or percentage.
Instead, it creates something more important for the development process: the ability to eventually produce measurements that can be trusted.
At the current stage, the platform already has the database structure and annotation infrastructure required for evaluation.
What it does not yet have is enough manually labelled data to publish a reliable accuracy figure.
Building the measurement infrastructure comes first.
Generating meaningful measurements comes next.
⸻
Current Development Stage
The database is actively receiving information from the running platform.
Perception events, occupancy states, environmental information and other system events can be recorded locally while the building-intelligence system operates.
Ground-truth data remains deliberately separate from those automatically generated records.
This creates a dataset that can later be used to compare what the system believed against what actually occurred.
All of this remains local to the edge platform.
⸻
Current Limitations
The evaluation dataset is still being built.
There is not yet enough labelled ground-truth data to publish a reliable accuracy figure for person detection or zone assignment.
The decision-evaluation layer is also still developing. The current records are closer to an engineering trace of system behaviour than a complete long-term evaluation history.
The database architecture was designed before large-scale data collection and may evolve as the dataset grows and new requirements become visible.
⸻
Why SQLite
SQLite was selected because the current platform operates as a local edge system.
It provides persistent structured storage without requiring a separate database server or cloud infrastructure.
For the current development stage, this keeps deployment simple while allowing perception, occupancy, sensor, event and evaluation data to remain queryable and persistent directly on the edge device.
As the platform evolves, the storage architecture can be expanded without changing the principle that perception and verified truth remain separate.
⸻
What’s Next
The next stage is where this architecture starts producing measurable results.
As enough manually labelled observations are collected, model predictions can be compared against verified ground truth.
That will allow the platform to move from statements such as:
“The system detects occupancy.”
to measurable engineering questions such as:
“How accurately does it detect occupancy?”
“How accurately does it assign people to zones?”
“Where does the perception pipeline fail?”
Those measurements will form the basis for improving the next versions of the system.
⸻
Status
Status: in progress
TechnovaMind
Independent engineering practice focused on Edge AI, Computer Vision, Robotics and AI Building Intelligence..
Contact
© 2025. All rights reserved.
Solutions
AI Building Intelligence
Assistive Robotics
Edge AI & Embedded Systems
AI Workflow Automation
Telegram Commerce Automation
Available for international AI engineering opportunities, technical collaborations and pilot projects.