How mahajanyash42/yara-police-database works
Project Type and Purpose
• Full-stack, three-layer web application for police data management.
• Frontend: Streamlit UI in Python.
• Backend: SQL Server relational database under the YARA schema.
• ETL/Seeding: Python script using Faker + pyodbc to populate sample data.
File Structure
Top level:
• Project_0502_04_SQL.sql (DDL script for schema & tables)
• README.md (project overview & workflow)
• Screenshots (UI mockups)
• app/
• Requirements.txt (dependencies)
• app.py (Streamlit UI + CRUD logic)
• seed_yara_data.py (ETL/seeding script)
Patterns & Config:
• Single-process Streamlit app handles UI and business logic.
• .env (loaded via python-dotenv) contains DB connection parameters.
• pyodbc for database connectivity.
• Faker for synthetic data generation.
README Insights
• Seven tables under YARA schema: Incident, Officer, Person, Evidence, Assign (Incident⇄Officer), Contain (Incident⇄Evidence), Arrest (Incident⇄Officer⇄Person).
• Real-time CRUD operations in UI.
• ETL pipeline to seed and refresh sample data.
Main Components & Relationships
a. Streamlit UI Layer (app.py)
• Hosts pages/components for Incident, Officer, Person, Evidence, Assign, Contain, Arrest.
• Handles user actions (Create, Read, Update, Delete).
b. Application Logic Layer (inside app.py & seed_yara_data.py)
• Business logic for form validation, query building, transaction management.
• ETL script for batch data generation.
c. Data Access Layer
• pyodbc driver + python-dotenv for connection pooling and credentials.
d. SQL Server Database
• YARA schema with seven normalized tables.
• Referential integrity (PK/FK, cascades).
e. External Libraries
• Faker (data generation)
• Streamlit (UI framework)
• Pandas (data handling, if used)
Data Flows and Interactions
• User → Streamlit UI: submits form or clicks a button.
• UI → Business Logic: validate inputs, construct SQL statements.
• Business Logic → Data Access Layer: open pyodbc connection.
• Data Access Layer → SQL Server: execute queries/transactions.
• SQL Server → Data Access Layer → UI: return result sets (SELECT) or status codes (INSERT/UPDATE/DELETE).
• ETL Script (seed_yara_data.py) runs periodically or on demand → uses Faker → Data Access Layer → bulk insert into SQL Server.
Architectural Patterns & Principles
• Three-tier architecture (Presentation, Logic, Data).
• Layered separation: UI concerns vs. data persistence.
• Database normalization and referential integrity.
• Single-responsibility per script: app.py for CRUD UX, seed_yara_data.py for ETL.