DataBase

📦 Database Overview

Welcome to the StateForce database wiki page. This section serves as the central hub for understanding how our database is structured, how to run it locally, and how to contribute to its data model.

---

🛠️ Database Engine

We use PostgreSQL as our relational database engine.

• Version: >= 17.4
• Chosen for: Stability, strong data integrity, and support for geospatial data.

---

📁 DB Schema & Documentation

The complete ERD and schema documentation are hosted on dbdocs.io:

👉 View the full schema on dbdocs.io

This includes:

• Tables and attributes
• Relationships (foreign keys, associations)
• Descriptions for each model
• Table index usage

---

🚀 Getting Started Locally

To spin up the database in your local environment:

1. Install PostgreSQL

Ensure PostgreSQL is installed on your system:

psql --version
brew install postgresql
brew services start postgresql

2. Database Configuration

Check config/database.yml for credentials and connection settings. Copy the environment variables file:

cp .env.example .env

3. Set Up the Database

In your Rails project, run:

rails db:setup

This will create the database, run migrations, and seed it from scratch.

---

🌱 Seed Data

We use the db/seeds.rb file to prepopulate the development database.

To seed the database:

rails db:seed

---

🔒 Security & Backups

Security

• Access Control Database access is restricted to authorized users and applications. Credentials are securely managed using environment variables and are never hardcoded or committed to version control.

• Role-Based Permissions PostgreSQL roles and privileges are configured to enforce the principle of least privilege for both application and administrative users.

• Network Security All database connections use SSL/TLS to protect data in transit. Access is restricted to trusted IPs or verified Heroku apps.

• Sensitive Data Sensitive information is encrypted at rest when supported by the hosting provider. The application avoids logging or exposing confidential data.

• Environment Separation Separate databases are used for development, testing, and production to prevent data leaks and accidental modifications.

• Monitoring Database logs and access patterns are monitored to detect suspicious activity or unauthorized access attempts.

---

Backups

• Automated Backups Backups are performed automatically and periodically using Heroku's built-in backup tools (Heroku Postgres), ensuring recoverability in case of accidental data loss or corruption.

• Backup Frequency Regular snapshots are scheduled base use and changes.

• Backup Retention Retention policies are managed by Heroku. Backups can be restored to any available point within the retention window.

---

For more details about Heroku Postgres backups, see: Heroku Postgres Documentation

If you suspect a security breach or data loss, contact the project administrators immediately.

---

📌 Notes

• If you add or change models, always run rails db:migrate and ensure schema.rb is updated.
• Migrations should be atomic and reversible.
• Keep seed data minimal, clean, and realistic.