forked from dsutanto/chicken-counting-sukawarna-det
andrew
11eddf3edb
- USER_ACCESS: use central password field plus display_name, last_login, is_superuser, is_staff, is_active - CYCLES: nullable end_date plus proposed_end_date, close_requested_at/by_id, feed_initial_balance(_date) - MANUAL_INPUT: add feed_out_manual(_total) - Add API_KEYS machine-credential table and relationships - Remove ERD_central_dashboard.json snapshot (ERD.md is source of truth)
15 KiB
15 KiB
Entity Relationship Diagram (ERD) & Unified Master Architecture
This document defines the integrated Main Dashboard ERD and how the Chicken Counting & Weight Estimation Vision System connects directly into the master farm database.
🗺️ Unified Master Mermaid ERD (Main Dashboard Integration)
erDiagram
%% ==========================================
%% 1. CORE ENTERPRISE HIERARCHY (Main Dashboard)
%% ==========================================
USER_ACCESS ||--|{ SITES : "manages"
USER_ACCESS ||--|{ API_KEYS : "owns"
SITES ||--|{ KANDANG : "contains"
KANDANG ||--|{ CYCLES : "runs"
CYCLES ||--|{ FLOCK : "houses"
CYCLES }|--o| USER_ACCESS : "close_requested_by"
%% ==========================================
%% 2. OUR DOMAIN: EDGE TOPOLOGY & VISION
%% ==========================================
KANDANG ||--|{ FLOOR : "has_levels"
FLOOR ||--|{ CAMERA : "equipped_with"
FLOOR ||--o{ BATCH_RUNS : "executes"
CAMERA ||--o{ BATCH_RUNS : "records"
BATCH_RUNS ||--o{ DETECTIONS : "captures_tracks"
%% ==========================================
%% 3. VISION ROLLUPS -> MAIN DASHBOARD TABLES
%% ==========================================
CYCLES ||--o{ CHICKEN_COUNTING : "records_daily_count"
CYCLES ||--o{ CHICKEN_WEIGHT : "records_daily_weight"
BATCH_RUNS ||--o{ CHICKEN_COUNTING : "aggregates_into_cc"
BATCH_RUNS ||--o{ CHICKEN_WEIGHT : "aggregates_into_cw"
%% ==========================================
%% 4. NON-VISION EXTERNAL STREAMS (DO NOT TOUCH)
%% ==========================================
FLOCK ||--o{ IOT_PANEL : "monitored_by"
CYCLES ||--o{ FEED_SACKS : "tracks_inventory"
CYCLES ||--o{ MANUAL_INPUT : "logs_ground_truth"
CYCLES ||--o{ AI_INSIGHT : "generates_alerts"
%% ==========================================
%% 5. MASTER KPI AGGREGATION (Main Dashboard)
%% ==========================================
CYCLES ||--o{ KPI : "computes_daily_kpi"
CHICKEN_COUNTING ||--o{ KPI : "feeds_headcount"
CHICKEN_WEIGHT ||--o{ KPI : "feeds_vision_weight"
FEED_SACKS ||--o{ KPI : "feeds_feed_usage"
MANUAL_INPUT ||--o{ KPI : "feeds_manual_weights"
%% ==========================================
%% ENTITY DEFINITIONS
%% ==========================================
USER_ACCESS {
int user_id PK "NOT NULL"
varchar user_name "30 NOT NULL"
varchar password "30 NOT NULL (hashed in backend)"
varchar display_name "30 NOT NULL"
timestamp last_login "NOT NULL"
boolean is_superuser "NOT NULL"
boolean is_staff "NOT NULL"
boolean is_active "NOT NULL"
varchar status "30 NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
API_KEYS {
int id PK "NOT NULL"
int user_id FK "References USER_ACCESS"
varchar name "100 NOT NULL"
varchar prefix "12 NOT NULL"
varchar key_hash "64 NOT NULL"
boolean is_active "NOT NULL"
timestamp last_used_at "NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
SITES {
int site_id PK "NOT NULL"
varchar site_name "30 NOT NULL"
int user_id FK "References USER_ACCESS"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
KANDANG {
int kandang_id PK "NOT NULL"
varchar kandang_name "30 NOT NULL"
int site_id FK "References SITES"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
CYCLES {
int cycle_id PK "NOT NULL (Flock Batch Run)"
int kandang_id FK "References KANDANG"
int total_days "NOT NULL"
date start_date "NOT NULL (Day 0)"
date end_date "NULL"
date proposed_end_date "NULL"
timestamp close_requested_at "NULL"
int close_requested_by_id FK "NULL, References USER_ACCESS"
int doc_in_weight "NOT NULL (Initial DOC weight for this cycle)"
int doc_in_count "NOT NULL (Stocking inventory)"
int feed_initial_balance "NOT NULL"
date feed_initial_balance_date "NOT NULL"
varchar status "30 NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
FLOCK {
int flock_id PK "NOT NULL"
varchar flock_name "30 NOT NULL"
int cycle_id FK "References CYCLES"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
FLOOR {
string floor_id PK "e.g. K1-L1, K1-L2"
int kandang_id FK "References KANDANG"
string level "Floor level designation (L1, L2, L3)"
json roi_points "Counting gate coordinates"
float px_per_cm "Spatial calibration constant"
}
CAMERA {
string camera_id PK "e.g. CC1, CC2, CC3, CC4"
string floor_id FK "References FLOOR"
int camera_num "1 to 4"
float px_per_cm "Camera pixel to cm calibration"
string stream_url "Source video / RTSP stream"
}
BATCH_RUNS {
integer id PK "AUTOINCREMENT (SQLite edge runs)"
string date "YYYY-MM-DD"
string location FK "References FLOOR(floor_id)"
string camera_id FK "References CAMERA(camera_id) or 'FUSED'"
integer total_entered "Headcount count validated"
integer frames_processed
real elapsed_seconds
string stopped_reason
string source_video
string generated_at
integer cycle_day "0 = DOC count only, 1+ = Weight active"
real area_cm2_median "Median body area (cm^2)"
real minor_axis_cm_median "Median width (cm)"
real major_axis_cm_median "Median length (cm)"
real predicted_weight_g "Estimated weight (g)"
real gompertz_baseline_g "Expected curve weight (g)"
real vision_gain_g "Vision allometric gain (g)"
integer n_weight_samples "Valid sample count"
real rejection_rate "MAD outlier filter rejection rate"
}
DETECTIONS {
string date "YYYY-MM-DD"
string coop "Parent Coop (e.g. K1)"
string location "Floor Location FK (e.g. K1-L1)"
integer cycle_day "Cycle Day (0 = DOC Arrival)"
string camera_id FK "CC1..CC4"
integer frame_index "Count trigger frame"
integer track_id "BoT-SORT track identifier"
integer class_id "0 = chicken"
float confidence "YOLO confidence score"
string geometry_source "mask | bbox"
float bbox_x1 "Top-left X px"
float bbox_y1 "Top-left Y px"
float bbox_x2 "Bottom-right X px"
float bbox_y2 "Bottom-right Y px"
float bbox_area "Pixel area px^2"
float centroid_x "Centroid X px"
float centroid_y "Centroid Y px"
float px_per_cm "Calibration scale (px/cm)"
float area_cm2 "Physical projected area cm^2"
float perimeter_cm "Perimeter in cm"
float major_axis_cm "Length / major axis cm"
float minor_axis_cm "Width / minor axis cm"
float eccentricity "Elongation eccentricity metric"
float aspect_ratio "Width / Height ratio"
float equiv_diameter_cm "Equivalent circular diameter cm"
float centroid_x_cm "Centroid X in cm"
float centroid_y_cm "Centroid Y in cm"
string source_video "Input video filename"
integer sequence_number "Counting entry sequence index"
boolean is_validated "True if crossed counting gate"
boolean is_outlier "True if rejected as outlier"
string outlier_reason "Rejection reason or null"
boolean kept_for_aggregation "True if used for flock weight"
}
CHICKEN_COUNTING {
int cc_id PK "NOT NULL"
int cycle_id FK "References CYCLES"
string coop "Parent Coop (e.g. K1)"
string location "Floor ID (e.g. K1-L1)"
date date "NOT NULL"
int total_count "NOT NULL (Aggregated total from edge BATCH_RUNS)"
int mortality_count "NOT NULL (Carcasses from vision segmentation)"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
CHICKEN_WEIGHT {
int cw_id PK "NOT NULL"
int cycle_id FK "References CYCLES"
string coop "Parent Coop (e.g. K1)"
string location "Floor ID (e.g. K1-L1)"
string camera_id "Camera ID (CC1..CC4 or FUSED)"
date date "NOT NULL"
int age "NOT NULL (Cycle Day: 1+)"
int doc_weight "NOT NULL (Cycle-specific initial DOC weight)"
float average_weight "NOT NULL (predicted_weight_g from edge)"
int chicken_count "NOT NULL (n_weight_samples / frequency)"
int excluded_count "Discarded outlier / duplicate observations"
float uniformity "NOT NULL (Flock weight uniformity % within +/- 5%)"
float average_daily_gain "NOT NULL (ADG in grams/day)"
float area_cm2_median "Median body area in cm^2"
float minor_axis_cm_median "Median minor axis / width in cm"
float major_axis_cm_median "Median major axis / length in cm"
float gompertz_baseline_g "Theoretical Gompertz curve baseline"
float vision_gain_g "Allometric vision gain residual"
float rejection_rate "MAD outlier filter rejection percentage"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
}
IOT_PANEL {
int panel_id PK "NOT NULL"
date date "NOT NULL"
float wind_speed "NOT NULL"
float humidity "NOT NULL"
float water_total "NOT NULL"
float average_temperature "NOT NULL"
float experience_temperature "NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
int flock_id FK "References FLOCK"
}
FEED_SACKS {
int fs_id PK "NOT NULL (fc_id)"
date date "NOT NULL"
int in_today "NOT NULL"
int out_today "NOT NULL"
int in_total "NOT NULL"
int out_total "NOT NULL"
int feed_use_today "NOT NULL"
int feed_use_total "NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
int cycle_id FK "References CYCLES"
}
MANUAL_INPUT {
int manual_id PK "NOT NULL"
date date "NOT NULL"
int age_manual "NOT NULL"
int mortality_manual "NOT NULL"
int mortality_manual_total "NOT NULL"
int feed_in_manual "NOT NULL"
int feed_use_manual "NOT NULL"
int feed_out_manual "NOT NULL"
int feed_in_manual_total "NOT NULL"
int feed_use_manual_total "NOT NULL"
int feed_out_manual_total "NOT NULL"
int harverst_manual "NOT NULL"
int harverst_manual_total "NOT NULL"
float harvest_weight_manual "NOT NULL"
float harvest_weight_manual_total "NOT NULL"
float manual_weight "NOT NULL"
float average_harvest_day_manual "NOT NULL"
float average_harvest_weight_manual "NOT NULL"
float fcr_manual "NOT NULL"
float eef_manual "NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
int cycle_id FK "References CYCLES"
}
AI_INSIGHT {
int id PK "NOT NULL"
date date "NOT NULL"
text insight_text "NOT NULL"
string alert "NOT NULL"
varchar section "100 NOT NULL"
varchar session "100 NOT NULL"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
int cycle_id FK "References CYCLES"
}
KPI {
int kpi_id PK "NOT NULL"
date date "NOT NULL"
int age "NOT NULL"
int mortality "NOT NULL"
int mortality_total "NOT NULL"
int feed "NOT NULL"
int feed_total "NOT NULL"
int harverst "NOT NULL"
int harverst_total "NOT NULL"
float harvest_weight "NOT NULL"
float harvest_weight_total "NOT NULL"
int chicken_life "NOT NULL"
float chicken_life_percentage "NOT NULL"
float iot_weight "NOT NULL"
float average_harvest_day "NOT NULL"
float average_harvest_weight "NOT NULL"
float fcr "NOT NULL (Feed Conversion Ratio)"
float eef "NOT NULL (European Efficiency Factor)"
timestamp created_at "NOT NULL"
timestamp updated_at "NOT NULL"
int cycle_id FK "References CYCLES"
int cc_id FK "References CHICKEN_COUNTING"
int cw_id FK "References CHICKEN_WEIGHT"
int fs_id FK "References FEED_SACKS"
int manual_id FK "References MANUAL_INPUT"
}
🏗️ Architectural Tier Descriptions & Integration Mapping
Tier 1: Farm Hierarchy (USER_ACCESS \to SITES \to KANDANG \to CYCLES \to FLOCK, plus API_KEYS)
- Belongs to the master Main Dashboard platform.
- Defines farm ownership, coops, operational commercial cycles (
CYCLES), and biological flocks (FLOCK). USER_ACCESSowns machineAPI_KEYScredentials so edge/client machines can call the API via header auth instead of username/password sessions.CYCLEStracks the close-out workflow (proposed_end_date,close_requested_at,close_requested_by_id\toUSER_ACCESS) and opening feed stock (feed_initial_balance,feed_initial_balance_date).
Tier 2: Edge Vision Subsystem (FLOOR \to CAMERA \to BATCH_RUNS \to DETECTIONS)
- Physical Hierarchy: Each
KANDANGhas multiple floor levels (FLOOR), and each floor operates 4 top-down optical counting cameras (CAMERA: CC1, CC2, CC3, CC4) calibrated bypx_per_cm. - Execution & Curation: Daily batch runs process video feeds on the edge and populate SQLite table
BATCH_RUNSand Parquet observation datasets (DETECTIONS). - Cycle Day 0 Rule: Day 0 is strictly for initial stocking headcount (
total_entered), while Day 1+ calculates both headcount and vision-derived geometric body weights.
Tier 3: Vision Aggregations to Main Dashboard Tables (CHICKEN_COUNTING & CHICKEN_WEIGHT)
Our edge pipeline aggregates floor camera runs and pushes clean daily rollups directly into the two vision tables of the Main Dashboard:
CHICKEN_COUNTING(cc_id):total_count\leftarrow \sum \text{total\_entered}across all floor cameras for that date.mortality_count\leftarrow \text{total dead carcasses detected by mortality segmentation}.
CHICKEN_WEIGHT(cw_id):average_weight\leftarrow \text{fused predicted weight in grams}.chicken_count\leftarrow \sum \text{n\_weight\_samples}(valid non-outlier tracks).uniformity\leftarrow \text{flock size uniformity percentage}.average_daily_gain\leftarrow \text{daily weight gain in grams/day}.area_cm2_median,minor_axis_cm_median,major_axis_cm_median\leftarrow \text{calibrated physical dimensions}.gompertz_baseline_g,vision_gain_g\leftarrow \text{audited growth model residual breakdown}.
Tier 4: Master Analytics & KPI Synthesis (KPI)
KPIis the central analytics ledger that combines:- Vision counts from
CHICKEN_COUNTING(cc_id). - Vision body weights from
CHICKEN_WEIGHT(cw_id). - Daily feed intake from
FEED_SACKS(fs_id). - Human ground truth logs from
MANUAL_INPUT(manual_id).
- Vision counts from
- Computes macro biological & economic efficiency indicators:
\text{FCR} = \frac{\text{Total Feed Consumed (kg)}}{\text{Total Harvest / Live Weight (kg)}}\text{EEF} = \frac{\text{Livability \%} \times \text{Average Weight (kg)}}{\text{Age (Days)} \times \text{FCR}} \times 100