# Database Documentation

MiauInv utilizes an embedded SQLite database instance for persistent data storage. Foreign key constraints are strictly enforced at the database level.

## Configuration
To ensure data integrity, every database connection initialization explicitly executes the following command before handling queries:
```sql
PRAGMA foreign_keys = ON;
```

---

## Schema Architecture

### Entity-Relationship Summary

The database consists of primary entity tables (`users`, `items`, `locations`, `projects`) and relational junction tables (`stock`, `project_items`, `refresh_tokens`) designed to track stock distribution and access sessions.

```
[users] <--- (1:N) ---> [refresh_tokens]
[items] <--- (1:N) ---> [stock] <--- (N:1) ---> [locations]
[items] <--- (1:N) ---> [project_items] <--- (N:1) ---> [projects]
```

---

## Table Definitions

### 1. users

Stores user credentials and operational roles within the system.

* **id (TEXT, PK):** Unique UUID
* **username (TEXT, Unique):** Unique account identifier.
* **password (TEXT):** Hashed user password.
* **role (TEXT):** Access control flag (e.g., admin, user).

### 2. refresh_tokens

Tracks valid extended sessions linked to specific user accounts.

* **id (TEXT, PK):** Unique identifier.
* **user_id (TEXT, FK):** References `users(id)`.
* **token_hash (TEXT):** Cryptographic hash of the active refresh token.
* **expires_at (INTEGER):** Unix timestamp indicating token expiration.
* **created_at (INTEGER):** Unix timestamp indicating session creation.
* **revoked (INTEGER):** Boolean flag (0 or 1) indicating if the session was manually invalidated.
* **device_info (TEXT, Optional):** Client metadata for auditing.

### 3. items

Represents individual tracked assets.

* **id (INTEGER, PK, Autoincrement):** Primary key.
* **name (TEXT):** Asset designation.
* **category (TEXT, Optional):** Grouping classification.
* **description (TEXT, Optional):** Detailed asset context.
* **total_quantity (INTEGER):** Absolute global stock baseline counter.

### 4. locations

Defines logical or physical facilities.

* **id (INTEGER, PK, Autoincrement):** Primary key.
* **name (TEXT, Unique):** Unique facility naming constraint.

### 5. projects

Defines distinct tasks or allocation targets.

* **id (INTEGER, PK, Autoincrement):** Primary key.
* **name (TEXT, Unique):** Unique operational tracking name.
* **description (TEXT, Optional):** Scope description.

### 6. stock

Junction table mapping physical asset distributions across facilities.

* **id (INTEGER, PK, Autoincrement):** Primary key.
* **item_id (INTEGER, FK):** References `items(id)`.
* **location_id (INTEGER, FK):** References `locations(id)`.
* **quantity (INTEGER):** Specific quantity present at this location node.

### 7. project_items

Junction table tracking asset assignments dedicated to specific ongoing project environments.

* **id (INTEGER, PK, Autoincrement):** Primary key.
* **item_id (INTEGER, FK):** References `items(id)`.
* **project_id (INTEGER, FK):** References `projects(id)`.
* **quantity (INTEGER):** Quantity allocated to this project context.
---

## Data Integrity Constraints

* **Foreign Keys:** Because standard `ON DELETE` cascades are not defined explicitly in the schema rules, SQLite blocks parent deletion actions if dependent rows exist in `stock` or `project_items`. You must clear out stock allocations and project associations manually before deleting an item, location, or project.
* **Uniqueness:** String uniqueness constraints protect against duplicate namespace registration on `users(username)`, `locations(name)`, and `projects(name)`.