Skip to main content

Database Scripts

Utility scripts for database management and maintenance.

Script Location​

tellus/docs/db_scripts/
├── init_database.sql          # Initial schema setup
├── delete_all.sql             # Delete all data
├── delete_company_data.py     # Delete company-specific data
├── delete_company_data.sql    # SQL version
├── delete_all_transactional_data.py
└── README.md

Common Operations​

Connect to Database​

# Development database
PGPASSWORD="your-password" psql \
  -h db-host.ondigitalocean.com \
  -p 25060 \
  -U tellus_ehs_dev_user \
  -d tellus_ehs_dev

Run SQL File​

PGPASSWORD="your-password" psql \
  -h host -p 25060 -U user -d database \
  -f script.sql

One-off Query​

PGPASSWORD="your-password" psql \
  -h host -p 25060 -U user -d database \
  -c "SELECT COUNT(*) FROM companies;"

Data Deletion Scripts​

Delete All Company Data​

Use with caution! This removes all transactional data.

# delete_company_data.py
from app.db.session import get_db

def delete_company_data(company_id: str):
    """Delete all data for a specific company."""
    db = next(get_db())

    # Delete in order (respecting foreign keys)
    db.execute("DELETE FROM chemiq_inventory WHERE company_id = :id", {"id": company_id})
    db.execute("DELETE FROM chemiq_company_product_catalog WHERE company_id = :id", {"id": company_id})
    db.execute("DELETE FROM users WHERE company_id = :id", {"id": company_id})
    # ... more tables

    db.commit()

Delete All Transactional Data​

Useful for resetting a development environment:

-- delete_all_transactional_data.sql
TRUNCATE TABLE chemiq_inventory CASCADE;
TRUNCATE TABLE chemiq_company_product_catalog CASCADE;
TRUNCATE TABLE chemiq_sds_documents CASCADE;
-- Keeps: companies, users, roles, modules

Useful Queries​

Check Company Data​

-- Count records per company
SELECT
    c.name,
    (SELECT COUNT(*) FROM users WHERE company_id = c.company_id) as users,
    (SELECT COUNT(*) FROM chemiq_inventory WHERE company_id = c.company_id) as inventory,
    (SELECT COUNT(*) FROM chemiq_company_product_catalog WHERE company_id = c.company_id) as products
FROM companies c
ORDER BY c.name;

Check SDS Parsing Status​

SELECT
    sds_parsed,
    COUNT(*) as count
FROM chemiq_sds_documents
GROUP BY sds_parsed;

Find Orphaned Records​

-- SDS documents not linked to any company
SELECT sds_id, product_name
FROM chemiq_sds_documents d
WHERE NOT EXISTS (
    SELECT 1 FROM chemiq_company_sds_mappings m
    WHERE m.sds_id = d.sds_id
);

Seed Data​

Load Development Seed Data​

psql -f docs/data_model/init_seed_data.sql

Populate Features and Modules​

psql -f docs/data_model/populate_features_v2.sql

Safety Guidelines​

Production Safety
  • Never run delete scripts on production
  • Always take a backup before major operations
  • Use transactions for multi-step operations
  • Test on staging first

Backup Before Operations​

# Create backup
pg_dump -h host -U user -d database > backup_$(date +%Y%m%d).sql

# Restore from backup
psql -h host -U user -d database < backup_20240101.sql