Skip to content

Instantly share code, notes, and snippets.

@jerlendds

jerlendds/claude-mermaid.md

Created July 14, 2025 16:24
Show Gist options
  • Save jerlendds/fe7f3e224313f4c94567e2f9bd06526e to your computer and use it in GitHub Desktop.
Save jerlendds/fe7f3e224313f4c94567e2f9bd06526e to your computer and use it in GitHub Desktop.
Download ZIP
Generate SQL->mermaid diagrams with Claude Code
Raw
claude-mermaid.md

You are an experienced Software Engineer performing a documentation task for the database. Your task is to assess the quality, clarity, and robustness of the database schem, create or update the sql-visualization.md Mermaid diagram according to these steps:

  1. Conduct a Review of the Database Pay attention to the SQL schemas in migrations/

  2. Generate a valid mermaid diagram Generate a visual representation of the OSINTBuddy database schema using Mermaid. Provide details of the key features on the mermaid diagram. Here are some best Practices for the diagram and flowchart creation:

    • Stick to the point. More like a tweet, less like a novel.
    • Use symbols consistently. Circles are circles, not suddenly squares.
    • Color sparingly if at all. It’s a diagram, not a rainbow.

Be thorough and professional in your generation, as if submitting a formal pull request.

@jerlendds
Copy link
Author

Output from the above prompt for the OSINTBuddy repo which has a migrations/ directory:

OSINTBuddy Database Schema Visualization

Database Schema Review

Architecture Overview

OSINTBuddy utilizes a hybrid database architecture combining:

  • PostgreSQL for structured relational data
  • Apache AGE extension for graph operations (nodes and edges)
  • Organization-centric design with comprehensive ABAC (Attribute-Based Access Control)

Schema Quality Assessment

Strengths

  • Comprehensive Access Control: Full ABAC implementation with resource sharing and audit logging
  • Multi-tenancy Support: Organization-based isolation with proper foreign key relationships
  • Scalable Design: Proper indexing strategy for performance optimization
  • Audit Trail: Complete access logging for compliance and security
  • Graph Integration: Apache AGE extension for advanced graph queries

Areas for Improvement

  • Missing Cascade Rules: Some foreign key relationships lack proper cascade behavior
  • Limited Constraints: Could benefit from additional check constraints for data integrity
  • Graph Schema Gap: No explicit graph schema definition (handled by AGE runtime)

Entity Relationship Diagram

erDiagram
    organizations {
        bigserial id PK
        text name
        text description
        varchar website
        varchar contact_email
        varchar subscription_level
        integer max_users
        integer max_graphs
        integer max_entities
        boolean can_export
        boolean can_share
        timestamptz ctime
        timestamptz mtime
    }
    
    users {
        bigserial id PK
        varchar name
        varchar email UK
        boolean verified
        varchar password
        varchar user_type
        bigserial org_id FK
        timestamptz ctime
        timestamptz mtime
    }
    
    graphs {
        bigserial id PK
        uuid uuid
        text label
        text description
        varchar visibility
        bigserial org_id FK
        bigserial owner_id FK
        timestamptz ctime
        timestamptz mtime
    }
    
    entities {
        bigserial id PK
        uuid uuid
        text label
        text description
        text author
        text source
        varchar visibility
        bigserial org_id FK
        bigserial owner_id FK
        timestamptz ctime
        timestamptz mtime
    }
    
    favorite_graphs {
        bigserial graph_id PK,FK
        bigserial owner_id PK,FK
    }
    
    favorite_entities {
        bigserial entity_id PK,FK
        bigserial owner_id PK,FK
    }
    
    resource_shares {
        bigserial id PK
        varchar resource_type
        bigserial resource_id
        bigserial shared_by_user_id FK
        bigserial shared_with_user_id FK
        bigserial org_id FK
        varchar access_level
        timestamptz expires_at
        timestamptz ctime
    }
    
    access_logs {
        bigserial id PK
        bigserial user_id FK
        varchar resource_type
        bigserial resource_id
        varchar action
        inet ip_address
        text user_agent
        boolean access_granted
        text access_reason
        timestamptz ctime
    }
    
    %% Relationships
    organizations ||--o{ users : "owns"
    organizations ||--o{ graphs : "contains"
    organizations ||--o{ entities : "contains"
    organizations ||--o{ resource_shares : "manages"
    
    users ||--o{ graphs : "creates"
    users ||--o{ entities : "creates"
    users ||--o{ favorite_graphs : "favorites"
    users ||--o{ favorite_entities : "favorites"
    users ||--o{ resource_shares : "shares_from"
    users ||--o{ resource_shares : "shares_to"
    users ||--o{ access_logs : "generates"
    
    graphs ||--o{ favorite_graphs : "favorited_by"
    entities ||--o{ favorite_entities : "favorited_by"
Loading

Key Database Features

1. Multi-Tenant Architecture

  • Organization-centric: Every resource belongs to an organization
  • User isolation: Users can only access resources within their organization
  • Subscription tiers: Different limits based on subscription level

2. Access Control & Security

  • ABAC Implementation: Granular access control via resource_shares table
  • Audit Trail: Complete logging of all resource access attempts
  • User Types: Four-tier user hierarchy (standard, moderator, owner, superadmin)

3. Graph Database Integration

  • Apache AGE Extension: Graph queries using Cypher syntax
  • Hybrid Storage: Metadata in PostgreSQL, graph data in AGE
  • Real-time Updates: WebSocket integration for live graph updates

4. Resource Management

  • Flexible Sharing: Resources can be shared with specific users or entire organizations
  • Expiration Control: Time-based access control with expiration timestamps
  • Visibility Levels: Five visibility levels from private to public

Database Performance Optimizations

Indexing Strategy

  • Primary Keys: All tables use BIGSERIAL for optimal performance
  • Foreign Key Indexes: Proper indexing on all foreign key relationships
  • Lookup Indexes: Composite indexes for common query patterns
  • UUID Indexes: Efficient UUID lookups for external API access

Query Optimization

  • Connection Pooling: 128 concurrent connections with proper lifecycle management
  • Transaction Management: Proper transaction scoping for AGE operations
  • Prepared Statements: SQLx integration for prepared statement caching

Schema Evolution Considerations

Recommended Enhancements

  1. Add Triggers: Automatic mtime updates on record modifications
  2. Partition Tables: Consider partitioning access_logs by date
  3. Materialized Views: For complex reporting queries
  4. Graph Schema: Define explicit graph schema constraints

Scalability Improvements

  1. Read Replicas: Separate read/write workloads
  2. Sharding Strategy: Organization-based sharding for horizontal scaling
  3. Caching Layer: Redis integration for frequently accessed data

Security Considerations

Current Security Features

  • Row-level Security: Implicit through foreign key constraints
  • Audit Logging: Complete access trail for compliance
  • Input Validation: Comprehensive check constraints

Security Enhancements

  • Encryption: Consider column-level encryption for sensitive data
  • Rate Limiting: Database-level rate limiting for abuse prevention
  • Data Retention: Implement data retention policies for audit logs

This documentation represents the current state of the OSINTBuddy database schema as of the latest migration (20250529170043). The schema demonstrates enterprise-ready design patterns with proper security, scalability, and maintainability considerations.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment