| Item |
Value |
| Engine |
PostgreSQL 17 (production) / 16 (dev) |
| Database |
yealinbilling |
| User |
yealinbilling |
| Host |
localhost |
| Port |
5432 |
| Password |
In Bitwarden: "Yealin Billing — PostgreSQL DB" |
# Connect as the app user
psql -U yealinbilling -d yealinbilling -h localhost
# Connect as postgres superuser
sudo -u postgres psql
# Connect to specific database as postgres
sudo -u postgres psql yealinbilling
-- Count CDR records
SELECT COUNT(*) FROM cdr_callrecord;
-- Count by status
SELECT status, COUNT(*) FROM cdr_callrecord GROUP BY status;
-- Count matched vs unmatched CDRs
SELECT
CASE WHEN customer_id IS NULL THEN 'Unmatched' ELSE 'Matched' END as match_status,
COUNT(*)
FROM cdr_callrecord
GROUP BY match_status;
-- List all customers
SELECT id, name, billing_email, status FROM customers_customer;
-- List all invoices
SELECT invoice_number, total_aud, status FROM billing_invoice ORDER BY issue_date DESC;
-- List all settings
SELECT key, value FROM core_setting ORDER BY key;
-- Check current FX rate
SELECT key, value FROM core_setting WHERE key LIKE 'USD%' OR key LIKE 'FX%';
-- List portal users
SELECT email, active FROM portal_customerportaluser;
¶ Django Database Commands
# Run all pending migrations
python manage.py migrate
# Check migration status
python manage.py showmigrations
# Create new migration after model changes
python manage.py makemigrations
# Show SQL for a migration (without running it)
python manage.py sqlmigrate billing 0001
# Open Django database shell
python manage.py dbshell
# Full database backup
pg_dump -U yealinbilling -h localhost yealinbilling > backup_$(date +%Y%m%d_%H%M%S).sql
# Compressed backup
pg_dump -U yealinbilling -h localhost yealinbilling | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz
# Backup to specific location
pg_dump -U yealinbilling -h localhost yealinbilling > /home/crip/backups/yealin_$(date +%Y%m%d).sql
# Drop and recreate database (DESTRUCTIVE — use with care)
sudo -u postgres psql -c "DROP DATABASE yealinbilling;"
sudo -u postgres psql -c "CREATE DATABASE yealinbilling OWNER yealinbilling;"
# Restore from backup
psql -U yealinbilling -h localhost yealinbilling < backup_20260608.sql
# Restore from compressed backup
gunzip -c backup_20260608.sql.gz | psql -U yealinbilling -h localhost yealinbilling
# As postgres superuser
sudo -u postgres psql
-- Inside psql:
CREATE USER yealinbilling WITH PASSWORD 'YOUR_PASSWORD';
CREATE DATABASE yealinbilling OWNER yealinbilling;
GRANT ALL PRIVILEGES ON DATABASE yealinbilling TO yealinbilling;
\q
# Then run migrations
cd /var/www/yealinbilling
source venv/bin/activate
export DJANGO_SETTINGS_MODULE=config.settings.production
python manage.py migrate
# Create admin user
python manage.py createsuperuser
# Seed required data
python manage.py shell
# In Django shell — seed settings
from apps.core.models import Setting
defaults = {
'COMPANY_NAME': 'Yealin Communications',
'COMPANY_ABN': '43 663 151 835',
'COMPANY_ADDRESS': 'L12 145 Eagle Street, Brisbane City QLD 4000',
'COMPANY_PHONE': '+61 3 9000 0001',
'COMPANY_EMAIL': 'info@yealin.com.au',
'COMPANY_WEBSITE': 'https://yealin.com.au',
'DEFAULT_PAYMENT_TERMS': '14',
'DEFAULT_GST_RATE': '10',
'USD_TO_AUD_RATE': '1.58',
'USD_TO_AUD_UPDATED_AT': '',
'FX_MARKUP_PERCENT': '0',
}
for key, value in defaults.items():
Setting.objects.get_or_create(key=key, defaults={'value': value})
print(f'Seeded: {key}')
# Seed AU destinations
exit()
# Seed destinations via API
TOKEN=$(curl -s -X POST https://billing.yealin.com.au/api/auth/token/ \
-H "Content-Type: application/json" \
-d '{"username":"admin","password":"YOUR_PASSWORD"}' \
| python3 -c "import sys,json; print(json.load(sys.stdin)['access'])")
curl -s -X POST https://billing.yealin.com.au/api/destinations/seed-australian/ \
-H "Authorization: Bearer $TOKEN"
cd /var/www/yealinbilling
source venv/bin/activate
export DJANGO_SETTINGS_MODULE=config.settings.production
# Change admin password
python manage.py changepassword admin
# Or change any user's password
python manage.py changepassword USERNAME