DataBase - martinmendozadev/StateForce GitHub Wiki

📦 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, support for geospatial data, and robust scalability.

📁 DB Schema & Documentation

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

👉 View the full schema on dbdocs.io

Schema Highlights:

  • Tables: Users, Incidents, Resources, Audit Logs, etc.
  • Relationships: Foreign keys and associations between tables.
  • Indexes: Optimized for search and query performance.
  • Descriptions: Detailed explanations of each table and attribute.

🚀 Getting Started Locally

Follow these steps to set 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 example environment variables file:

cp .env.example .env

3. Set Up the Database

Run the following Rails command to set up the database:

rails db:setup

This will:

  • Create the database.
  • Run migrations.
  • Seed it with initial data.

🌱 Seed Data

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

To seed the database:

rails db:seed

Seed Data Includes:

  • Default user roles (Admin, Guest, etc.).
  • Sample incidents for testing.
  • Mock resources like ambulances, hospitals, and rescue units.

🔒 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 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 accidental modifications or data leaks.

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 Postgres tools, ensuring recoverability in case of data loss or corruption.

Backup Frequency

  • Regular snapshots are scheduled based on usage and changes.

Backup Retention

  • Heroku manages retention policies, allowing restoration to any available point within the retention window.

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

🔄 Best Practices for Database Updates

Migrations

  • Always ensure migrations are atomic and reversible.
  • Use rails db:migrate to update the schema and confirm changes.

Schema Management

  • After running migrations, ensure the schema.rb file is updated and committed to version control.

Seed Data

  • Keep seed data minimal, clean, and realistic.
  • Avoid overloading the database with unnecessary test data.

📌 Notes

  • If you suspect a security breach or data loss, contact the project administrators immediately.
  • Regularly test database backups to ensure they can be restored successfully.
  • For contributions to the data model, follow the established coding standards and conventions.

This page serves as a comprehensive resource for managing and maintaining the StateForce database. If you have questions or need assistance, feel free to reach out to the project team.