# mahajanyash42/yara-police-database architecture

> Architecture diagram of the GitHub repository mahajanyash42/yara-police-database, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-10-30). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/mahajanyash42/yara-police-database
- Repository: https://github.com/mahajanyash42/yara-police-database

## Overview

##### 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.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Presentation Layer
    subgraph "Presentation Layer"
        UI["Streamlit UI<br>(app.py)"]:::presentation
    end

    %% Application Layer
    subgraph "Application & ETL Services"
        BL["Business Logic Layer<br>(app.py)"]:::service
        ETL["ETL Pipeline<br>(seed_yara_data.py)"]:::service
    end

    %% Data Layer
    subgraph "Data Access & Persistence"
        DA["Data Access<br>(pyodbc + .env)"]:::driver
        DB["SQL Server<br>(YARA schema, 7 tables)"]:::database
    end

    %% External Resources
    subgraph "Resources & Documentation"
        DEP["Dependencies<br>(Requirements.txt)"]:::external
        DDL["DDL Script<br>(Project_0502_04_SQL.sql)"]:::external
        DOC["Documentation<br>(README.md)"]:::external
        SS["UI Mockups<br>(Screenshots/)"]:::external
    end

    %% Data Flows
    UI -->|"user actions"| BL
    BL -->|"queries & commands"| DA
    DA -->|"executes SQL"| DB
    DB -->|"results/status"| DA
    DA -->|"returns data"| UI

    ETL -->|"batch inserts"| DA
    DA -->|"inserts into"| DB

    %% External dependencies
    DEP --> UI
    DEP --> BL
    DEP --> ETL
    DDL --> DB
    DOC -.-> UI
    DOC -.-> BL
    SS -.-> UI

    %% Click Events
    click UI "https://github.com/mahajanyash42/yara-police-database/blob/main/app/app.py"
    click BL "https://github.com/mahajanyash42/yara-police-database/blob/main/app/app.py"
    click ETL "https://github.com/mahajanyash42/yara-police-database/blob/main/app/seed_yara_data.py"
    click DA "https://github.com/mahajanyash42/yara-police-database/blob/main/app/app.py"
    click DEP "https://github.com/mahajanyash42/yara-police-database/blob/main/app/Requirements.txt"
    click DDL "https://github.com/mahajanyash42/yara-police-database/blob/main/Project_0502_04_SQL.sql"
    click DOC "https://github.com/mahajanyash42/yara-police-database/blob/main/README.md"
    click SS "https://github.com/mahajanyash42/yara-police-database/tree/main/Screenshots/"

    %% Styling
    classDef presentation fill:#AED6F1,stroke:#1B4F72,color:#1B2631;
    classDef service fill:#A9DFBF,stroke:#145A32,color:#0B5345;
    classDef driver fill:#F5B041,stroke:#7D6608,color:#4D2600;
    classDef database fill:#D5D8DC,stroke:#626567,color:#1C2833;
    classDef external fill:#F9E79F,stroke:#7D6608,color:#784212;
```
