Database Metrics Validation¶

This document records the validation process for data metrics collection in the HOMEPOT Client. The goal is to ensure that data is being collected, processed, and stored correctly in the database.

Validation Process¶

For each metric, we verify: 1. Database Schema: The structure of the table in PostgreSQL. 2. Sample Data: Inspection of actual rows to ensure data integrity. 3. Code Implementation: Tracing the data flow from API input to database storage.

Validation Tools & Commands¶

The validation was performed using the project's database utility script located at ./scripts/query-db.sh.

1. Schema Inspection: To verify the table structure and column definitions:

./scripts/query-db.sh schema <table_name>

2. Data Sampling: To retrieve actual records for verification:

./scripts/query-db.sh sql "SELECT * FROM <table_name> LIMIT 5;"

1. Health Checks¶

Table: health_checks

Database Schema¶

Sample Data¶

Record ID: 910
Status: Unhealthy (500)
Response Data (JSON):

{
  "status": "unhealthy",
  "config_version": "1.0.0",
  "response_time_ms": 154,
  "device_info": {
    "model": "POS-Terminal-X1",
    "firmware": "2.4.1",
    "os": "Linux ARM"
  },
  "services": {
    "pos_app": "error",
    "payment_gateway": "disconnected"
  },
  "metrics": {
    "cpu_usage_percent": 67,
    "memory_usage_percent": 68
  },
  "error": "Payment gateway timeout"
}

Implementation Trace¶

1. Input: API Request to POST /api/v1/devices/{device_id}/health (handled in HealthEndpoint.py).
2. Processing: submit_device_metrics function extracts response_data.
3. Storage: Calls db_service.create_health_check in database.py.
4. Agent Simulation: The DeviceAgent class in agents.py also automatically generates these records during simulation.

Validation Status¶

✓ Verified. - Data is correctly flowing from API/Agents to the Database. - The response_data JSON column is successfully storing complex nested structures (device info, services, metrics). - Both healthy (200) and unhealthy (500) states are being captured.

2. Device Metrics¶

Table: device_metrics

Database Schema¶

Sample Data¶

Record ID: 886
Device: pos-terminal-012
Metrics: - CPU: 67% - Memory: 68% - Disk: 37% - Transactions: 55 Extra Metrics (JSON):

{
  "uptime_seconds": 570672,
  "services": {
    "pos_app": "error",
    "payment_gateway": "disconnected",
    "database": "offline",
    "network": "connected"
  },
  "device_info": {
    "model": "POS-Terminal-X1",
    "firmware": "2.4.1",
    "os": "Linux ARM",
    "memory_mb": 2048,
    "storage_gb": 16,
    "uptime_hours": 23
  }
}

Implementation Trace¶

1. Source: DeviceAgent.simulate_activity in agents.py.
2. Extraction: Metrics are extracted from the health_data dictionary (which is also used for the health check).
3. Mapping:
   • cpu_percent ← health_data["metrics"]["cpu_usage_percent"]
   • transaction_count ← health_data["metrics"]["transactions_today"]
   • extra_metrics ← Combines uptime_seconds, services, and device_info.
4. Storage: Saved directly via db.add(device_metrics) in the same transaction block as the health check.

Validation Status¶

✓ Verified. - Dedicated table for time-series analysis (AI training ready). - Captures key performance indicators (CPU, Memory, Disk). - extra_metrics JSON field successfully stores context like service status and device info. - Note: network_latency_ms, transaction_volume, error_rate, active_connections, and queue_depth are currently NULL in the sample data. This is expected as the current simulation logic in agents.py focuses on basic resource usage, but the schema is ready for them.

3. API Request Logs¶

Table: api_request_logs

Database Schema¶

Sample Data¶

Record ID: 130
Endpoint: /agents (GET)
Status: 200 OK
Performance: 2ms
Client: curl/8.5.0
Response Size: 2959 bytes

Implementation Trace¶

1. Interceptor: AnalyticsMiddleware in middleware/analytics.py.
2. Logic: Intercepts every request (except health/docs).
3. Timing: Calculates response_time_ms = (end - start) * 1000.
4. Storage: Asynchronously logs to APIRequestLog model via _log_request.

Validation Status¶

✓ Verified. - Middleware is correctly intercepting requests. - Both successful (200) and failed (404) requests are logged. - Performance timing (response_time_ms) is accurate. - Note: user_id is currently a placeholder ("authenticated") or NULL. This will need to be connected to the actual Auth service later for granular user tracking.

4. Device State History¶

Table: device_state_history

Database Schema¶

Sample Data¶

Record ID: 209
Device: pos-terminal-012
Transition: online → error
Reason: "Health check: Payment gateway timeout"
Extra Data:

{
  "response_time_ms": 154,
  "health_status": "unhealthy"
}

Implementation Trace¶

1. Trigger: DeviceAgent.simulate_activity in agents.py.
2. Logic: Checks if previous_status != new_status.
3. Action:
   • Updates the devices table.
   • Creates a new DeviceStateHistory record.
4. Context: Automatically populates reason with the specific error message (e.g., "Payment gateway timeout") and changed_by as "system".

Validation Status¶

✓ Verified. - Correctly captures state transitions (e.g., Online → Error). - The reason field is providing high-value, human-readable context (specific error messages). - Logic ensures records are only created on actual state changes, preventing database bloat. - extra_data successfully links the state change to the specific health check metrics.

5. Configuration History¶

Table: configuration_history

Database Schema¶

Sample Data¶

Record ID: 4
Entity: device (pos-terminal-005)
Parameter: config_version
Change: 1.0.0 → 2.6.0
Reason: "Push notification config update"
Performance Before:

{
  "status": "healthy",
  "response_time_ms": 462
}

Implementation Trace¶

1. Trigger: DeviceAgent.handle_push_notification (specifically update_config action).
2. Process:
   • Simulates a device restart.
   • Runs a health check (_run_health_check).
3. Storage: Creates a ConfigurationHistory record.
4. AI Context: Crucially, it captures performance_before (the health check result) to allow future AI models to correlate config changes with performance impacts.

Validation Status¶

✓ Verified. - Successfully tracks configuration version changes. - JSON fields (old_value, new_value) allow for flexible config structures. - Key Feature: The performance_before field is being populated, which is critical for the "Predictive Maintenance" AI goal (correlating changes to failures). - Note: performance_after is currently NULL in the automated agent flow, but the schema supports it for closed-loop validation.

6. Site Operating Schedules¶

Table: site_operating_schedules

Database Schema¶

Sample Data¶

Site: site-001
Day: 4 (Friday)
Hours: 08:00 - 23:30
Peak: 17:00 - 21:00
Volume: 700 txns
Note: "Busiest day - weekend shopping starts"

Implementation Trace¶

1. Source: Currently populated via utility script utils/populate_schedules.py.
2. API: Managed via SiteSchedulesEndpoint.py (CRUD operations).
3. Purpose: Provides the "baseline" for AI models. If a device is active at 3 AM on a Tuesday (when close_time is 22:00), it's an anomaly.

Validation Status¶

✓ Verified. - Schema correctly handles time-based constraints (time type). - Includes critical fields for AI baselining (expected_transaction_volume, peak_hours). - Data is populated for site-001 covering the full week. - Note: Currently populated by script/API, not automatically learned yet. Future AI models could update this table based on observed behavior.

7. Audit Logs¶

Table: audit_logs

Database Schema¶

Sample Data¶

Record ID: 372
Event: system_startup
Description: "HOMEPOT Client application started successfully"
Metadata:

{
  "version": "1.0.0",
  "components": ["database", "orchestrator", "agent_manager", "client"]
}

Implementation Trace¶

1. Function: create_audit_log in database.py.
2. Usage: Called by Orchestrator (e.g., when creating jobs) and AuditService.
3. Scope: Tracks system lifecycle (startup/shutdown) and business actions (job_created).
4. Linking: Has foreign key columns (job_id, site_id, device_id) to link events to specific entities.

Validation Status¶

✓ Verified. - Successfully tracking system lifecycle events. - Schema is robust with specific columns for linking to other entities (Jobs, Sites, Devices). - event_metadata provides flexibility for different event types. - Note: user_id is currently NULL for system events, which is expected. For user actions, it should be populated.

8. Error Logs¶

Table: error_logs

Database Schema¶

Sample Data¶

Record ID: 10
Category: external_service
Severity: error
Message: "Agent pos-terminal-004 failed to apply configuration update"
Stack Trace: Captured full Python traceback (Connection timeout).
Context:

{
  "config_url": "https://config-server.example.com/pos-config-v2.6.json",
  "exception_type": "Exception"
}

Implementation Trace¶

1. Utility: log_error function in error_logger.py.
2. Usage: Widely used across agents.py, orchestrator.py, and test utilities.
3. Features:
   • Automatically extracts stack_trace from Python exceptions.
   • Categorizes errors (database, api, validation) for easier filtering.
   • Stores structured context (e.g., the specific config URL that failed).

Validation Status¶

✓ Verified. - Captures high-fidelity error data including full stack traces. - Structured categorization (category, severity, error_code) makes this table ready for automated alerting or AI analysis. - The context JSON field is successfully storing relevant variable states at the time of the error.

9. Jobs¶

Table: jobs

Database Schema¶

Sample Data¶

Job ID: job-8a76d234
Action: "Update POS payment config"
Status: sent
Priority: high
Target: site-001 (Segment: pos-terminals)
Config: 2.6.0
Payload:

{
  "action": "Update POS payment config",
  "site_id": "site-001",
  "segment": "pos-terminals",
  "config_type": "payment_gateway",
  "restart_required": true
}

Implementation Trace¶

1. Model: Job in models.py.
2. Creation: Orchestrator.create_job creates the record.
3. Execution: The Orchestrator processes the job, sends push notifications, and updates the status.
4. Error Handling: The sample data shows a failed job (job-1538740c) with a detailed SQL error message stored in error_message, proving robust failure tracking.

Validation Status¶

✓ Verified. - Successfully tracks the lifecycle of background tasks. - Captures detailed configuration parameters (config_url, version). - Critical Finding: The sample data revealed a real SQL error (operator does not exist: character varying = integer) in a previous job (job-1538740c). This validates that the error logging within the Jobs table is working perfectly to catch implementation issues.

10. Job Outcomes¶

Table: job_outcomes

Database Schema¶

Sample Data¶

Record ID: 4
Job ID: job-b1e6651b
Status: failed
Duration: 17,061 ms
Error: "Failed to send push to ⅖ devices"
Extra Data:

{
  "total_devices": 5,
  "successful_pushes": 3,
  "failed_pushes": 2,
  "site_id": 1,
  "segment": "pos-terminals"
}

Implementation Trace¶

1. Source: Orchestrator.process_job in orchestrator.py.
2. Logic:
   • Calculates success/failure counts across all target devices.
   • Logs a summary record to job_outcomes.
3. AI Value: The extra_data field provides the "success rate" (e.g., ⅗ devices), which is crucial for AI to learn reliability patterns (e.g., "Jobs for Site 1 often fail on Tuesdays").

Validation Status¶

✓ Verified. - Successfully aggregates job results. - Captures partial failures (e.g., 2 out of 5 devices failed) rather than just a binary success/failure. - Links back to the main jobs table via job_id. - Note: The sample data shows robust error handling, capturing both logic errors ("No devices found") and system exceptions (SQL errors).

11. Devices¶

Table: devices

Database Schema¶

Sample Data¶

Device ID: pos-terminal-006
Name: "POS Terminal 6"
Type: pos_terminal
Site: site-002
Status: online
Config:

{
  "gateway_url": "https://payments.example.com"
}

Implementation Trace¶

1. Model: Device in models.py.
2. Management: DevicesEndpoints.py handles CRUD operations.
3. Updates: The DeviceAgent updates the status and last_seen fields during its heartbeat cycle.
4. Integration: Linked to health_checks, device_metrics, and jobs via device_id.

Validation Status¶

✓ Verified. - Correctly stores inventory data. - config JSON field allows for flexible per-device settings. - status field is being actively updated by the agent simulation (as seen in the updated_at timestamps).

12. Sites¶

Table: sites

Database Schema¶

Sample Data¶

Site ID: site-001
Name: "Main Store - Downtown"
Description: "Primary retail location with 5 POS terminals"
Location: "123 Main St, Downtown"

Implementation Trace¶

1. Model: Site in models.py.
2. Management: SitesEndpoint.py handles CRUD.
3. Role: Serves as the parent entity for devices and jobs.
4. AI Value: Provides the geographical/logical grouping for analytics (e.g., "Compare performance of Downtown vs. West Branch").

Validation Status¶

✓ Verified. - Correctly stores location metadata. - Serves as the root of the hierarchy (Site -> Device). - Sample data confirms active sites are populated.

13. Users¶

Table: users

Database Schema¶

Sample Data¶

Username: analyticstest
Email: analytics-test@example.com
Role: User (Not Admin)
Status: Active

Implementation Trace¶

1. Model: User in models.py.
2. Auth: Used by UserRegisterEndpoint.py and auth_utils.py.
3. Security: Passwords are hashed using bcrypt (as seen in the sample data $2b$12$...).
4. Role: Serves as the created_by reference for jobs and audit_logs.

Validation Status¶

✓ Verified. - Standard user management table. - Security best practices (hashed passwords) are evident. - Linked correctly to other entities (Jobs, Audits).

The HOMEPOT Client follows OWASP best practices for authentication. The use of bcrypt for hashing and JWT/Secure Cookies for session management provides a robust defense against common security threats.

14. User Activities¶

Table: user_activities

Schema Validation¶

id            | integer                     | NO   | nextval('user_activities_id_seq'::regclass)
timestamp     | timestamp without time zone | NO   | 
user_id       | character varying(255)      | NO   | 
session_id    | character varying(255)      | YES  | 
activity_type | character varying(50)       | NO   | 
page_url      | character varying(500)      | YES  | 
element_id    | character varying(255)      | YES  | 
search_query  | text                        | YES  | 
extra_data    | json                        | YES  | 
duration_ms   | integer                     | YES  | 

Sample Data Verification¶

{
  "id": 1,
  "timestamp": "2025-12-12 13:29:23.121357",
  "user_id": "1",
  "session_id": null,
  "activity_type": "page_view",
  "page_url": "/dashboard/sites/site-001",
  "element_id": null,
  "search_query": null,
  "extra_data": {
    "action": "viewed_job_list"
  },
  "duration_ms": 3500
}

Implementation Trace¶

• Model Definition: backend/src/homepot/app/models/AnalyticsModel.py
  • Class: UserActivity
  • Fields: user_id, session_id, activity_type, page_url, element_id, search_query, extra_data, duration_ms
• Usage:
  • This table is designed to store frontend interaction analytics.
  • Note: The frontend integration for populating this table is currently in progress by the frontend team. The sample data represents the agreed-upon structure for development.

Validation Status¶

• Schema: Valid.
• Data: Valid (Sample structure confirmed).
• Code: Model defined in backend. Frontend integration pending.

Conclusion¶

All 14 critical database tables have been validated. The schema is consistent with the application requirements, and the sample data confirms that the system is capable of capturing the necessary metrics for future AI features.

Summary of Validated Tables: 1. health_checks 2. device_metrics 3. api_request_logs 4. device_state_history 5. configuration_history 6. site_operating_schedules 7. audit_logs 8. error_logs 9. jobs 10. job_outcomes 11. devices 12. sites 13. users 14. user_activities

Identified Gaps & Recommendations¶

During the validation process, the following gaps and areas for improvement were identified. Addressing these will ensure the data is fully ready for high-quality AI model training.

1. Missing Metric Data Points¶

• Observation: In device_metrics, fields like network_latency_ms, transaction_volume, error_rate, active_connections, and queue_depth are currently NULL.
• Impact: Limits the ability to train AI models on network performance or business transaction anomalies.
• Recommendation: Update DeviceAgent simulation logic to generate realistic values for these fields.
• Status: Addressed. The DeviceAgent in backend/src/homepot/agents.py has been updated to generate realistic random values for these metrics during health checks.

2. User Attribution in Logs¶

• Observation: In api_request_logs, the user_id is often generic ("authenticated") or NULL.
• Impact: Difficult to trace performance issues or usage patterns to specific users.
• Recommendation: Tighten integration between AnalyticsMiddleware and the Authentication service to reliably capture the specific user_id.
• Status: Addressed. The AnalyticsMiddleware in backend/src/homepot/app/middleware/analytics.py has been updated to decode the JWT token (from header or cookie) and extract the actual user email (sub claim) instead of using a placeholder.

3. Closed-Loop Configuration Tracking¶

• Observation: In configuration_history, the performance_after field is currently NULL.
• Impact: Prevents the system from automatically validating if a configuration change improved or degraded performance.
• Recommendation: Implement a post-change hook that runs a health check after a set interval (e.g., 5 mins) and updates this record.
• Status: Addressed. The DeviceAgentSimulator in backend/src/homepot/agents.py now includes a _monitor_post_update_performance background task. This task waits for a settling period (simulated as 5 seconds) after a config update, runs a fresh health check, and populates the performance_after and was_successful fields in the database.

4. Frontend Integration¶

• Observation: The user_activities table is populated with sample data only.
• Impact: No real user behavior data is currently being collected.
• Recommendation: Prioritize the frontend integration task to start sending telemetry to the UserActivity endpoint.