Developer Toolkit
Practical tools to detect and prevent schema weaknesses
60-Second Detection Queries
Quick queries to check your database for common schema weaknesses
SWE-0001: Missing Indexes
Find columns frequently used in WHERE clauses without indexes
-- Find missing indexes on foreign key columns
SELECT
tc.table_name,
kcu.column_name,
'No index on FK column' as issue
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes pi
ON pi.tablename = tc.table_name
AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
AND pi.indexname IS NULL
AND tc.table_schema = 'public';SWE-0002: Integer Overflow Risk
Check for INT columns approaching overflow
-- Check INT columns approaching overflow
WITH int_columns AS (
SELECT
schemaname,
tablename,
attname as column_name,
format('SELECT MAX(%I) FROM %I.%I',
attname, schemaname, tablename) as check_query
FROM pg_stats
WHERE schemaname = 'public'
AND atttypid = 'int4'::regtype
AND (attname LIKE '%_id' OR attname = 'id')
)
SELECT
tablename,
column_name,
check_query,
'2,147,483,647' as max_int_value
FROM int_columns
ORDER BY tablename;SWE-3001: Weak Authentication
Find tables storing passwords without proper hashing
-- Find potential plaintext password columns
SELECT
table_name,
column_name,
data_type,
character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
AND (
column_name ILIKE '%password%'
OR column_name ILIKE '%pwd%'
OR column_name ILIKE '%pass%'
)
AND data_type IN ('character varying', 'text')
AND (
character_maximum_length < 60
OR character_maximum_length IS NULL
);SWE-4001: Data Partitioning Issues
Identify tables that may need partitioning
-- Find large tables without partitioning
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
n_live_tup as row_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
AND n_live_tup > 10000000
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;Technical Debt Cost Calculator
Estimate the potential cost of schema weaknesses in your system
Your System Profile
Architecture Decision Helper
Make informed decisions based on SWE patterns
🗄️ Database Selection
Relational vs NoSQL
Key considerations based on SWE patterns
- Relational: Better for complex relationships (avoid SWE-0003)
- NoSQL: Beware of SWE-4002 (Schema-less chaos)
- Hybrid: Consider SWE-5003 (Sync complexity)
Sharding Strategy
Avoid common partitioning pitfalls
- By User: Watch for SWE-4001 (Hotspot keys)
- By Time: Beware SWE-2003 (Uneven growth)
- By Geography: Consider SWE-6001 (Compliance)
🔐 Security Architecture
Authentication Design
Prevent auth-related incidents
- MFA: Prevents 90% of SWE-3001 incidents
- Token Storage: Avoid SWE-3002 patterns
- Session Management: Watch SWE-3003
Integrations & Tools
Integrate SWE detection into your development workflow
GitHub Actions
Automated SWE pattern detection in PRs
name: SWE Pattern Check
on: [pull_request]
jobs:
swe-check:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- name: Run SWE Scanner
uses: fetchschema/swe-scanner@v1
with:
database-type: postgresql
fail-on-high: true
API Access
Programmatic access to SWE data
# Get all patterns
curl https://swe.fetchschema.com/api/swe-catalog.json
# Get specific pattern
curl https://swe.fetchschema.com/api/patterns/SWE-0001.json
# Get pattern relationships
curl https://swe.fetchschema.com/api/pattern-relationships.jsonCLI Tool
Command-line SWE scanner
# Install CLI
npm install -g @fetchschema/swe-cli
# Scan your database
swe scan --database postgres://localhost/mydb
# Generate report
swe report --format html > swe-report.html