Output Formats#

DBSurveyor generates structured output in multiple formats to support different use cases and workflows.

Schema File Formats#

Standard JSON Format (.dbsurveyor.json)#

The primary output format is a JSON file following the DBSurveyor Schema v1.0 specification:

{
  "format_version": "1.0",
  "database_info": {
    "name": "production_db",
    "version": "13.7",
    "size_bytes": 1073741824,
    "encoding": "UTF8",
    "collation": "en_US.UTF-8",
    "access_level": "Full",
    "collection_status": "Success"
  },
  "tables": [
    {
      "name": "users",
      "schema": "public",
      "columns": [
        {
          "name": "id",
          "data_type": {
            "Integer": {
              "bits": 32,
              "signed": true
            }
          },
          "is_nullable": false,
          "is_primary_key": true,
          "is_auto_increment": true,
          "ordinal_position": 1
        }
      ],
      "primary_key": {
        "name": "users_pkey",
        "columns": [
          "id"
        ]
      },
      "foreign_keys": [],
      "indexes": [],
      "constraints": []
    }
  ],
  "views": [],
  "indexes": [],
  "constraints": [],
  "procedures": [],
  "functions": [],
  "triggers": [],
  "custom_types": [],
  "collection_metadata": {
    "collected_at": "2024-01-15T10:30:00Z",
    "collection_duration_ms": 1500,
    "collector_version": "1.0.0",
    "warnings": []
  }
}

Table Sampling Data#

Tables may optionally include a sampling field containing sampled row data with metadata:

{
  "table_name": "users",
  "schema_name": "public",
  "rows": [
    {
      "id": 1,
      "username": "alice",
      "email": "alice@example.com"
    },
    {
      "id": 2,
      "username": "bob",
      "email": "bob@example.com"
    }
  ],
  "sample_size": 2,
  "total_rows": 1234,
  "sampling_strategy": {
    "MostRecent": {
      "limit": 10
    }
  },
  "collected_at": "2024-01-15T10:30:00Z",
  "warnings": [],
  "sample_status": "Complete"
}

Sample Status Field

The optional sample_status field indicates the outcome of the sampling operation:

"Complete": Sampling completed successfully with no issues
{"PartialRetry": {"original_limit": 10}}: Sampling partially completed with a reduced row limit (may be retried)
{"Skipped": {"reason": "table too large"}}: Sampling was skipped or not performed

Backward Compatibility: The sample_status field is optional and omitted when not set, ensuring older JSON files without this field remain valid.

Compressed Format (.dbsurveyor.json.zst)#

Large schema files can be compressed using Zstandard compression:

# Generate compressed output
dbsurveyor-collect --compress postgres://localhost/large_db

# Postprocessor automatically detects and decompresses
dbsurveyor generate schema.dbsurveyor.json.zst

The collector automatically appends .zst to the output path if missing. For example, --output schema.dbsurveyor.json becomes schema.dbsurveyor.json.zst.

Benefits:

60-80% size reduction for large schemas
Fast compression/decompression
Maintains full data integrity

Encrypted Format (.enc)#

Sensitive schema data can be encrypted using AES-GCM-256:

# Generate encrypted output (prompts for password)
dbsurveyor-collect --encrypt postgres://localhost/sensitive_db

# Non-interactive encryption using environment variable
DBSURVEYOR_ENCRYPTION_PASSWORD='mysecret123' dbsurveyor-collect --encrypt --output schema.json postgres://localhost/mydb

# Postprocessor prompts for decryption password
dbsurveyor generate schema.enc

# Non-interactive decryption
DBSURVEYOR_ENCRYPTION_PASSWORD='mysecret123' dbsurveyor generate schema.json.enc

The collector automatically appends .enc to the output path if missing. For example, --output schema.dbsurveyor.json becomes schema.dbsurveyor.json.enc.

Password Options:

Interactive prompt: Default behavior; includes confirmation on encryption to prevent typos
Environment variable: Set DBSURVEYOR_ENCRYPTION_PASSWORD for non-interactive operation (minimum 8 characters)

Security Features:

AES-GCM-256 authenticated encryption
Argon2id key derivation (64 MiB memory, 3 iterations)
Random 96-bit nonces (never reused)
Embedded KDF parameters for decryption

Encrypted File Structure:

{
  "algorithm": "AES-GCM-256",
  "nonce": [/* 12 bytes */],
  "ciphertext": [/* encrypted data */],
  "auth_tag": [/* 16 bytes */],
  "kdf_params": {
    "salt": [/* 16 bytes */],
    "memory_cost": 65536,
    "time_cost": 3,
    "parallelism": 4,
    "version": "1.3"
  }
}

Combined Compression and Encryption#

The --compress --encrypt flags produce a .enc file containing zstd-compressed JSON data that is then AES-GCM encrypted:

# Generate combined compressed+encrypted output
dbsurveyor-collect --compress --encrypt --output schema.json postgres://localhost/mydb

# Produces schema.json.enc containing encrypted+compressed data
# Postprocessor automatically decrypts and decompresses
DBSURVEYOR_ENCRYPTION_PASSWORD='mysecret123' dbsurveyor schema.json.enc

The postprocessor detects compressed payloads inside decrypted files via the zstd magic bytes (0x28, 0xB5, 0x2F, 0xFD) and decompresses automatically. No special flags are required for decryption—the file extension and content magic determine the processing pipeline.

Benefits:

Maximum file size reduction (70-80% typical)
Security for sensitive schemas
Single file output with automatic format detection

Documentation Formats#

Markdown Documentation (.md)#

Status: ✅ Implemented

Basic Markdown documentation with schema overview:

dbsurveyor generate schema.json --format markdown

Output Example:

# Database Schema: production_db

Generated by DBSurveyor v1.0.0
Collection Date: 2024-01-15 10:30:00 UTC

## Summary

- **Tables**: 25
- **Views**: 3
- **Indexes**: 47

## Tables

### users
- **Schema**: public
- **Columns**: 5
- **Primary Key**: id

JSON Analysis Report (.json)#

Status: ✅ Implemented

Structured analysis data for programmatic consumption:

dbsurveyor analyze schema.json --detailed

Output Example:

{
  "database_name": "production_db",
  "table_count": 25,
  "view_count": 3,
  "index_count": 47,
  "constraint_count": 32,
  "collection_date": "2024-01-15T10:30:00Z",
  "analysis": {
    "largest_table": "audit_log",
    "most_indexes": "users",
    "foreign_key_relationships": 18
  }
}

HTML Report (.html)#

Status: 🚧 Placeholder Implementation

Interactive HTML reports with search and navigation:

dbsurveyor generate schema.json --format html

Planned Features:

Interactive table browser
Search functionality
Relationship visualization
Responsive design for mobile/desktop
Offline-compatible (no external dependencies)

Mermaid ERD (.mmd)#

Status: 🚧 Placeholder Implementation

Entity Relationship Diagrams using Mermaid syntax:

dbsurveyor generate schema.json --format mermaid

Planned Output:

Loading diagram...

SQL DDL (.sql)#

Status: 🚧 Placeholder Implementation

Reconstructed SQL DDL for schema recreation:

dbsurveyor sql schema.json --dialect postgresql --output recreate.sql

Planned Features:

Multiple SQL dialect support (PostgreSQL, MySQL, SQLite, SQL Server)
Complete schema recreation scripts
Data type mapping between databases
Constraint and index recreation
Migration script generation

Unified Data Type System#

DBSurveyor uses a unified type system to represent data types across different databases:

Basic Types#

{
  "String": {
    "max_length": 255
  },
  "Integer": {
    "bits": 32,
    "signed": true
  },
  "Float": {
    "precision": 53
  },
  "Boolean": null,
  "Date": null,
  "DateTime": {
    "with_timezone": true
  },
  "Time": {
    "with_timezone": false
  }
}

Advanced Types#

{
  "Binary": {
    "max_length": 1024
  },
  "Json": null,
  "Uuid": null,
  "Array": {
    "element_type": {
      "String": {
        "max_length": 50
      }
    }
  },
  "Custom": {
    "type_name": "user_status_enum"
  }
}

Database-Specific Mapping#

DBSurveyor Type	PostgreSQL	MySQL	SQLite	MongoDB
`String`	`VARCHAR`, `TEXT`	`VARCHAR`, `TEXT`	`TEXT`	`string`
`Integer`	`INTEGER`, `BIGINT`	`INT`, `BIGINT`	`INTEGER`	`int`, `long`
`Float`	`REAL`, `DOUBLE`	`FLOAT`, `DOUBLE`	`REAL`	`double`
`Boolean`	`BOOLEAN`	`BOOLEAN`	`INTEGER`	`bool`
`Json`	`JSON`, `JSONB`	`JSON`	`TEXT`	`object`
`Array`	`ARRAY[]`	`JSON`	`TEXT`	`array`
`Custom`	`ENUM`, `DOMAIN`	`ENUM`	N/A	N/A

Schema Validation#

All output files are validated against the DBSurveyor JSON Schema v1.0:

Validation Features#

Format Version Checking: Ensures compatibility
Required Field Validation: All mandatory fields present
Data Type Validation: Correct type structure
Security Validation: No credential fields allowed
Relationship Validation: Foreign key consistency

Validation Errors#

# Validate a schema file
dbsurveyor validate schema.json

# Example validation error
Error: Schema validation failed
- Missing required field: collection_metadata.collected_at
- Invalid data type: expected Integer, found String
- Security violation: credential field detected in column default_value

File Size Considerations#

Compression Recommendations#

Schema Size	Recommendation	Expected Compression
< 1 MB	Standard JSON	N/A
1-10 MB	Zstandard compression	60-70% reduction
> 10 MB	Compression + chunking	70-80% reduction

Large Schema Handling#

For very large databases (1000+ tables):

# Use compression for large schemas
dbsurveyor-collect --compress postgres://localhost/large_db

# Consider sampling for development
dbsurveyor-collect --sample 10 postgres://localhost/large_db

Security Considerations#

Credential Protection#

All output formats guarantee:

No database credentials in any output file
Connection strings sanitized in metadata
Error messages sanitized
Validation prevents credential leakage

Encryption Best Practices#

# Use strong passwords for encryption
dbsurveyor-collect --encrypt postgres://localhost/sensitive_db

# Verify encrypted file integrity
dbsurveyor validate schema.enc

Airgap Compatibility#

All formats work completely offline:

No external dependencies in generated files
Self-contained documentation
Local-only processing
No network calls during generation

Format Evolution#

Version Compatibility#

v1.0: Current format with full backward compatibility
Future versions: Additive changes only
Migration tools: Automatic format upgrades
Deprecation policy: 2-version support window

Schema Extensions#

The format supports extensions for:

Database-specific features
Custom metadata
Plugin-generated data
Performance metrics

Integration Examples#

CI/CD Pipeline#

# Generate schema documentation in CI
  - name: Collect Database Schema
    run: |
      dbsurveyor-collect --compress $DATABASE_URL
      dbsurveyor generate schema.dbsurveyor.json.zst --format markdown

  - name: Upload Documentation
    uses: actions/upload-artifact@v3
    with:
      name: database-docs
      path: schema.md

Programmatic Access#

import json

# Load schema data
with open("schema.dbsurveyor.json") as f:
    schema = json.load(f)

# Analyze tables
for table in schema["tables"]:
    print(f"Table: {table['name']}")
    print(f"Columns: {len(table['columns'])}")

Documentation Automation#

#!/bin/bash
# Automated documentation generation

# Collect from multiple environments
dbsurveyor-collect --output prod.json $PROD_DB_URL
dbsurveyor-collect --output staging.json $STAGING_DB_URL

# Generate documentation
dbsurveyor generate prod.json --format html --output prod-schema.html
dbsurveyor generate staging.json --format html --output staging-schema.html

# Compare schemas (future feature)
# dbsurveyor compare prod.json staging.json --output diff.html

This comprehensive output format system ensures that DBSurveyor can adapt to various workflows while maintaining security and compatibility guarantees.