BUG: Database Integrity Issues - Foreign Key Constraints Not Enforced

### Is there an existing issue for this?

- [x] I have searched the existing issues

### What happened?

**Description**

SQLite disables foreign key constraints **by default**, meaning all foreign key rules (such as `ON DELETE CASCADE` and `ON DELETE SET NULL`) are ignored unless explicitly enabled.

Several database functions directly call:

```python
conn = sqlite3.connect(DATABASE_PATH)
```

without running:

```sql
PRAGMA foreign_keys = ON;
```

As a result, foreign key relationships are **silently ignored**, and the database accepts invalid references.

If the foreign key enforcement remains disabled:

* **Data integrity** will continue degrading silently.
* **Cascading deletes** and **SET NULL behaviors** will never trigger.
* The database will accumulate **orphaned and invalid records**.
* **JOIN queries** and **application logic** relying on relationships will fail or produce incorrect results.
* Over time, the application data becomes **irrecoverably inconsistent**, potentially requiring full data cleanup or migration.

### Record

- [x] I agree to follow this project's Code of Conduct

### Checklist before Submitting

- [ ] Have you updated docs for it?
- [ ] Have you added unit tests?
- [ ] Have you made sure unit tests pass?
- [x] Have you made sure code formatting is correct?
- [ ] Do Your changes passes all tests?