EOS_Schema/README.md

README — PostgreSQL Database Setup for EOS

This README provides instructions for creating and managing the EOS PostgreSQL database schema. The schema includes multiple tables (e.g., tickets_open_additional_info and others). Each table DDL is stored in separate .sql files such as test.txt.

---

Table of contents

1. Purpose
2. Prerequisites
3. Database & schema overview
4. Files included
5. Quick start — create EOS database & load schema
6. Detailed schema notes (example: tickets_open_additional_info)
7. Adding more tables
8. Best practices (migrations, backups, security)
9. Troubleshooting
10. Contact / maintainers

---

1. Purpose

The EOS database stores application data related to ticketing, feedback, and operational workflows. The repository contains SQL DDL files (one per table or per module). These define table structures, indexes, constraints, and comments.

---

2. Prerequisites

• PostgreSQL server (recommended: v12+).
• psql client.
• Privileges to create database, schema, and tables.

---

3. Database & schema overview

• Database name: EOS
• Schema: default is public, unless otherwise specified.
• Tables: multiple (tickets_open_additional_info, tickets_open, …). Each is defined in .sql files.

---

4. Files included

• test.txt — SQL DDL for tickets_open_additional_info.
• Additional .sql files — each will hold the DDL for other tables in EOS.

---

5. Quick start — create EOS database & load schema

1) Create the EOS database

createdb EOS

# or via psql
psql -U postgres -c "CREATE DATABASE EOS;"

2) Create application user (optional)

psql -U postgres -c "CREATE ROLE eos_user WITH LOGIN PASSWORD 'strong_password';"
psql -U postgres -c "GRANT CONNECT ON DATABASE EOS TO eos_user;"

3) Run schema files

# Run for a single table
psql -U postgres -d EOS -f /path/to/test.txt

# Run all tables (assuming all SQL files are in ./schema)
for f in ./schema/*.sql; do
  psql -U postgres -d EOS -f "$f"
done

---

6. Detailed schema notes (example: tickets_open_additional_info)

The tickets_open_additional_info table is defined in test.txt. Highlights:

• Primary key: ticket_id.
• Important columns: state, city, vehicle_tagging, trip times, feedback fields.
• Indexes: on ticket_id, state_name, city_name.
• Comments: explain references (e.g., ticket_id → tickets_open.ticket_id).

Similar notes should be documented for each table when new .sql files are added.

---

7. Adding more tables

• Place new .sql files in the schema/ directory (or alongside test.txt).
• Each .sql should contain CREATE TABLE + indexes + constraints.
• Example:
  • tickets_open.sql
  • tickets_closed.sql
  • tickets_feedback.sql
  • etc.

To apply all:

psql -U postgres -d EOS -f tickets_open.sql
psql -U postgres -d EOS -f tickets_closed.sql
psql -U postgres -d EOS -f tickets_feedback.sql

---

8. Best practices

• Keep schema files under version control (Git).
• Use migration tools (Flyway, Liquibase) if schema changes are frequent.
• Define foreign keys between related tables (e.g., tickets_open_additional_info.ticket_id → tickets_open.ticket_id).
• Remove redundant indexes (avoid duplicate PK + unique index).
• Use least-privilege users for the application (SELECT/INSERT/UPDATE only).

---

9. Troubleshooting

• "relation already exists" → drop the table or add IF NOT EXISTS.
• foreign key issues → ensure referenced table exists before dependent table.
• index duplication warnings → simplify schema.

---

10. Contact / maintainers

• Maintainer: (team/person name, email)
• Database: EOS