Database Service

Overview

The database service uses PostgreSQL with TimescaleDB extension for time-series data management. It provides the foundation for all data storage and retrieval operations in the SSA backend application.

What This Service Does

PostgreSQL Database: Primary relational database for all application data
TimescaleDB Extension: Time-series data management for historical data
Data Storage: Stores satellite data, TLEs, news articles, user data, and more
Query Optimization: Optimized queries for time-series and spatial data
Backup and Recovery: Automated backup and recovery procedures
Connection Pooling: Efficient connection management for high concurrency

How It Works

Initialization: Database starts with TimescaleDB extension enabled
Schema Creation: Automatic table creation from SQL scripts
Data Storage: Structured storage of all application data
Query Processing: Optimized query execution with indexes
Time-series Management: Automatic partitioning and retention policies
Backup: Regular automated backups to persistent storage

Database Schema

Core Tables

Users and Authentication

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    is_admin BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Satellite Data

CREATE TABLE maneuver_app_norad_db (
    id SERIAL PRIMARY KEY,
    norad_id VARCHAR(10) NOT NULL,
    satellite_name VARCHAR(255),
    country_operator VARCHAR(100),
    detailed_purpose VARCHAR(255),
    constellation_name VARCHAR(255),
    launch_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE maneuver_app_tle_data (
    id SERIAL PRIMARY KEY,
    norad_id VARCHAR(10) NOT NULL,
    tle_date DATE NOT NULL,
    tle TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(norad_id, tle_date)
);

News Data

CREATE TABLE space_news_prod (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(500) NOT NULL,
    short_description TEXT,
    source VARCHAR(100) NOT NULL,
    mission_name VARCHAR(200),
    date TIMESTAMP WITH TIME ZONE,
    url TEXT,
    slug VARCHAR(200),
    last_updated TIMESTAMP WITH TIME ZONE,
    country_code VARCHAR(10),
    mission_description TEXT,
    off_url TEXT,
    insertion_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(title, source, date)
);

Favorites and Groups

CREATE TABLE favorite_groups (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    color VARCHAR(7),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE favorites (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    norad_id VARCHAR(10) NOT NULL,
    group_id INTEGER REFERENCES favorite_groups(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, norad_id, group_id)
);

TimescaleDB Tables

Time-series Data

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Convert tables to hypertables
SELECT create_hypertable('maneuver_app_tle_data', 'tle_date');
SELECT create_hypertable('space_news_prod', 'date');

-- Create time-based indexes
CREATE INDEX idx_tle_data_norad_date ON maneuver_app_tle_data (norad_id, tle_date DESC);
CREATE INDEX idx_news_date_source ON space_news_prod (date DESC, source);

Configuration

Environment Variables

Variable	Default	Description
`POSTGRES_USER`	`maneuver_db`	Database username
`POSTGRES_PASSWORD`	`secret@796`	Database password
`POSTGRES_DB`	`maneuver`	Database name
`POSTGRES_PORT`	`5432`	Database port
`POSTGRES_HOST`	`postgres`	Database host

Docker Configuration

postgres:
  image: timescale/timescaledb:2.11.2-pg15
  container_name: sss_db_postgres
  restart: always
  ports:
    - "${POSTGRES_PORT:-5435}:5432"
  volumes:
    - postgres_data:/var/lib/postgresql/data
    - ./data/01_create_tables.sql:/docker-entrypoint-initdb.d/01_create_tables.sql
    - ./data/maneuver_app_norad_db.sql:/docker-entrypoint-initdb.d/02_maneuver_app_norad_db.sql
    - ./data/02_timescaledb_setup.sql:/docker-entrypoint-initdb.d/03_timescaledb_setup.sql
    - ./data/04_create_news_table.sql:/docker-entrypoint-initdb.d/04_create_news_table.sql
  environment:
    POSTGRES_USER: ${POSTGRES_USER}
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    POSTGRES_DB: ${POSTGRES_DB}
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
    interval: 5s
    timeout: 5s
    retries: 5

Usage

Connect to Database

# Connect using psql
docker exec -it sss_db_postgres psql -U maneuver_db -d maneuver

# Connect using external client
psql -h localhost -p 5435 -U maneuver_db -d maneuver

Common Queries

Satellite Data

-- Get latest TLE for a satellite
SELECT tle, tle_date 
FROM maneuver_app_tle_data 
WHERE norad_id = '25544' 
ORDER BY tle_date DESC 
LIMIT 1;

-- Get satellites by country
SELECT norad_id, satellite_name, country_operator 
FROM maneuver_app_norad_db 
WHERE country_operator = 'USA';

News Data

-- Get latest news articles
SELECT title, source, date 
FROM space_news_prod 
ORDER BY date DESC 
LIMIT 10;

-- Get news by source
SELECT title, date 
FROM space_news_prod 
WHERE source = 'LaunchLibrary2' 
ORDER BY date DESC;

User Data

-- Get user favorites
SELECT f.norad_id, fg.name as group_name 
FROM favorites f 
JOIN favorite_groups fg ON f.group_id = fg.id 
WHERE f.user_id = 1;

Performance Optimization

Indexes

-- Primary indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_norad_db_country ON maneuver_app_norad_db(country_operator);
CREATE INDEX idx_tle_data_norad ON maneuver_app_tle_data(norad_id);
CREATE INDEX idx_news_source_date ON space_news_prod(source, date);

-- Composite indexes
CREATE INDEX idx_favorites_user_norad ON favorites(user_id, norad_id);
CREATE INDEX idx_news_title_source ON space_news_prod(title, source);

Query Optimization

-- Use EXPLAIN ANALYZE for query optimization
EXPLAIN ANALYZE 
SELECT * FROM maneuver_app_tle_data 
WHERE norad_id = '25544' 
AND tle_date >= '2024-01-01';

-- Use time-based partitioning
SELECT create_hypertable('maneuver_app_tle_data', 'tle_date', 
    chunk_time_interval => INTERVAL '1 month');

Backup and Recovery

Automated Backups

# Create backup
docker exec sss_db_postgres pg_dump -U maneuver_db -d maneuver > backup_$(date +%Y%m%d).sql

# Restore backup
docker exec -i sss_db_postgres psql -U maneuver_db -d maneuver < backup_20240115.sql

Backup Script

#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
docker exec sss_db_postgres pg_dump -U maneuver_db -d maneuver > $BACKUP_DIR/backup_$DATE.sql
gzip $BACKUP_DIR/backup_$DATE.sql

Monitoring

Database Metrics

-- Check database size
SELECT pg_size_pretty(pg_database_size('maneuver'));

-- Check table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

Health Checks

# Check database health
docker exec sss_db_postgres pg_isready -U maneuver_db -d maneuver

# Check TimescaleDB status
docker exec sss_db_postgres psql -U maneuver_db -d maneuver -c "SELECT * FROM timescaledb_information.hypertables;"

Troubleshooting

Common Issues

Connection Issues:

# Check if database is running
docker-compose ps postgres

# Check database logs
docker-compose logs postgres

Performance Issues:

-- Check slow queries
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

Storage Issues:

# Check disk usage
docker exec sss_db_postgres df -h

# Check database size
docker exec sss_db_postgres psql -U maneuver_db -d maneuver -c "SELECT pg_size_pretty(pg_database_size('maneuver'));"

Maintenance

-- Vacuum database
VACUUM ANALYZE;

-- Reindex tables
REINDEX TABLE maneuver_app_tle_data;
REINDEX TABLE space_news_prod;

-- Update statistics
ANALYZE;

Security

Access Control

-- Create read-only user
CREATE USER readonly WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE maneuver TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Data Encryption

At Rest: Database files encrypted using filesystem encryption
In Transit: SSL/TLS connections required
Backups: Encrypted backup storage

Integration

With FastAPI Service

SQLAlchemy ORM: Object-relational mapping for database operations
Connection Pooling: Efficient connection management
Migration Support: Alembic for schema migrations

With Other Services

TLE Processor: Direct database access for TLE storage
News Processor: Direct database access for news storage
Monitoring: Prometheus metrics for database performance

Overview​

What This Service Does​

How It Works​

Database Schema​

Core Tables​

Users and Authentication​

Satellite Data​

News Data​

Favorites and Groups​

TimescaleDB Tables​

Time-series Data​

Configuration​

Environment Variables​

Docker Configuration​

Usage​

Connect to Database​

Common Queries​

Satellite Data​

News Data​

User Data​

Performance Optimization​

Indexes​

Query Optimization​

Backup and Recovery​

Automated Backups​

Backup Script​

Monitoring​

Database Metrics​

Health Checks​

Troubleshooting​

Common Issues​

Maintenance​

Security​

Access Control​

Data Encryption​

Integration​

With FastAPI Service​

With Other Services​

Overview

What This Service Does

How It Works

Database Schema

Core Tables

Users and Authentication

Satellite Data

News Data

Favorites and Groups

TimescaleDB Tables

Time-series Data

Configuration

Environment Variables

Docker Configuration

Usage

Connect to Database

Common Queries

Satellite Data

News Data

User Data

Performance Optimization

Indexes

Query Optimization

Backup and Recovery

Automated Backups

Backup Script

Monitoring

Database Metrics

Health Checks

Troubleshooting

Common Issues

Maintenance

Security

Access Control

Data Encryption

Integration

With FastAPI Service

With Other Services