SOCMINT AI Database Schema Design

Overview

This document defines the complete database schema for SOCMINT AI, covering both MongoDB (operational data) and Neo4j (graph intelligence).

MongoDB Collections

1. Identity Profile Collection

Collection: identity_profiles

{
  _id: ObjectId,
  caseId: ObjectId,
  
  // Core Identity
  displayName: String,
  aliases: [String],
  primaryIdentifier: String,
  
  // Scoring
  confidenceScore: Number,      // 0-100
  riskScore: Number,             // 0-100
  
  // Status
  status: String,                // "active" | "archived" | "merged"
  verificationStatus: String,    // "unverified" | "pending" | "verified" | "rejected"
  
  // Discovered Data
  discoveredProfiles: [{
    platform: String,
    username: String,
    profileId: ObjectId,        // Reference to social_profiles
    confidence: Number
  }],
  
  emails: [{
    email: String,
    verified: Boolean,
    source: String,
    confidence: Number
  }],
  
  phones: [{
    number: String,
    verified: Boolean,
    source: String,
    confidence: Number
  }],
  
  images: [{
    url: String,
    hash: String,
    source: String,
    similarity: Number
  }],
  
  links: [{
    url: String,
    platform: String,
    type: String
  }],
  
  // Location Data
  locations: [{
    city: String,
    country: String,
    source: String,
    confidence: Number
  }],
  
  // Metadata
  tags: [String],
  notes: String,
  
  // Audit
  createdBy: ObjectId,
  createdAt: Date,
  updatedAt: Date,
  lastAnalyzedAt: Date
}

2. Social Profile Collection

Collection: social_profiles

{
  _id: ObjectId,
  identityId: ObjectId,          // Reference to identity_profiles
  caseId: ObjectId,
  
  // Platform Info
  platform: String,              // "instagram" | "github" | "reddit" | "x" | "linkedin"
  username: String,
  displayName: String,
  
  // Profile Data
  bio: String,
  profileUrl: String,
  profileImage: String,
  profileImageHash: String,
  
  // Metrics
  followers: Number,
  following: Number,
  postsCount: Number,
  
  // Account Info
  accountCreated: Date,
  verified: Boolean,
  
  // Correlation
  confidence: Number,            // 0-100
  correlationMethod: String,     // "username" | "image" | "bio" | "behavior"
  
  // Raw Data
  metadata: {
    location: String,
    website: String,
    email: String,
    phone: String,
    customFields: Object
  },
  
  // Status
  status: String,                // "active" | "suspended" | "deleted" | "private"
  lastScraped: Date,
  
  // Audit
  discoveredBy: String,          // "discovery_agent" | "manual" | "correlation_agent"
  createdAt: Date,
  updatedAt: Date
}

3. Investigation Finding Collection

Collection: investigation_findings

{
  _id: ObjectId,
  caseId: ObjectId,
  investigationId: ObjectId,
  
  // Finding Type
  type: String,                  // "identity_match" | "image_match" | "relationship" | "behavior_pattern" | "content_pattern"
  category: String,              // "high_priority" | "medium_priority" | "low_priority"
  
  // Core Data
  title: String,
  description: String,
  
  // Confidence
  confidence: Number,            // 0-100
  confidenceLevel: String,       // "very_high" | "high" | "medium" | "low"
  
  // Explanation (Explainable AI)
  explanation: {
    summary: String,
    factors: [{
      factor: String,
      weight: Number,
      score: Number,
      reasoning: String
    }],
    methodology: String
  },
  
  // Related Entities
  relatedIdentities: [ObjectId],
  relatedProfiles: [ObjectId],
  evidenceIds: [ObjectId],
  
  // Source
  sourceAgent: String,           // "discovery_agent" | "correlation_agent" | "visual_agent"
  sourceData: Object,
  
  // Validation
  validationStatus: String,      // "pending" | "validated" | "rejected" | "needs_review"
  validatedBy: ObjectId,
  validatedAt: Date,
  validationNotes: String,
  
  // Audit
  createdAt: Date,
  updatedAt: Date
}

4. Relationship Collection

Collection: relationships

{
  _id: ObjectId,
  caseId: ObjectId,
  
  // Relationship
  sourceIdentityId: ObjectId,
  targetIdentityId: ObjectId,
  
  // Type
  relationshipType: String,      // "follows" | "mentions" | "connected_to" | "same_person" | "associated_with"
  platform: String,
  
  // Strength
  confidence: Number,            // 0-100
  strength: String,              // "strong" | "moderate" | "weak"
  
  // Evidence
  evidenceIds: [ObjectId],
  evidenceCount: Number,
  
  // Interaction Data
  interactions: [{
    type: String,                // "mention" | "reply" | "share" | "like"
    timestamp: Date,
    platform: String,
    url: String
  }],
  
  // Metadata
  firstSeen: Date,
  lastSeen: Date,
  frequency: Number,
  
  // Audit
  discoveredBy: String,
  createdAt: Date,
  updatedAt: Date
}

5. Content Analysis Collection

Collection: content_analysis

{
  _id: ObjectId,
  caseId: ObjectId,
  profileId: ObjectId,
  identityId: ObjectId,
  
  // Content
  contentType: String,           // "post" | "comment" | "bio" | "tweet"
  content: String,
  contentUrl: String,
  
  // Analysis
  topics: [String],
  keywords: [String],
  hashtags: [String],
  mentions: [String],
  
  // NLP Results
  sentiment: {
    score: Number,               // -1 to 1
    label: String,               // "positive" | "neutral" | "negative"
    confidence: Number
  },
  
  language: String,
  writingStyle: {
    formalityScore: Number,
    complexityScore: Number,
    emotionalTone: String
  },
  
  // Metadata
  timestamp: Date,
  platform: String,
  engagement: {
    likes: Number,
    shares: Number,
    comments: Number
  },
  
  // Audit
  analyzedAt: Date,
  createdAt: Date
}

6. Behavior Analysis Collection

Collection: behavior_analysis

{
  _id: ObjectId,
  caseId: ObjectId,
  identityId: ObjectId,
  profileId: ObjectId,
  
  // Time Patterns
  activeHours: [{
    hour: Number,              // 0-23
    frequency: Number,
    timezone: String
  }],
  
  activeDays: [{
    day: String,               // "monday" | "tuesday" etc
    frequency: Number
  }],
  
  // Posting Patterns
  postingFrequency: {
    daily: Number,
    weekly: Number,
    monthly: Number
  },
  
  peakActivityHour: Number,
  averagePostsPerDay: Number,
  
  // Consistency
  behaviorConsistency: Number,   // 0-100
  activityGaps: [{
    startDate: Date,
    endDate: Date,
    duration: Number
  }],
  
  // Anomalies
  anomalies: [{
    type: String,
    description: String,
    timestamp: Date,
    severity: String
  }],
  
  // Analysis Period
  analysisStartDate: Date,
  analysisEndDate: Date,
  dataPoints: Number,
  
  // Audit
  analyzedAt: Date,
  createdAt: Date
}

7. Investigation Timeline Collection

Collection: investigation_timeline

{
  _id: ObjectId,
  caseId: ObjectId,
  investigationId: ObjectId,
  
  // Event
  eventType: String,             // "discovery" | "correlation" | "analysis" | "validation" | "report"
  eventCategory: String,         // "agent_action" | "user_action" | "system_event"
  
  title: String,
  description: String,
  
  // Related Entities
  relatedIdentityId: ObjectId,
  relatedProfileId: ObjectId,
  relatedFindingId: ObjectId,
  relatedEvidenceId: ObjectId,
  
  // Agent Info
  agentName: String,
  agentType: String,
  
  // User Info
  userId: ObjectId,
  userName: String,
  
  // Metadata
  metadata: Object,
  
  // Timestamp
  timestamp: Date,
  createdAt: Date
}

8. Source Reliability Collection

Collection: source_reliability

{
  _id: ObjectId,
  
  // Source Info
  sourceName: String,            // "github" | "linkedin" | "reddit" | "forum_xyz"
  sourceType: String,            // "social_media" | "forum" | "public_record" | "osint_tool"
  sourceUrl: String,
  
  // Reliability Score
  reliabilityScore: Number,      // 0-100
  reliabilityLevel: String,      // "very_high" | "high" | "medium" | "low" | "unverified"
  
  // Factors
  factors: {
    verificationRequired: Boolean,
    officialSource: Boolean,
    dataQuality: Number,
    updateFrequency: String,
    historicalAccuracy: Number
  },
  
  // Usage Stats
  timesUsed: Number,
  successfulMatches: Number,
  falsePositives: Number,
  
  // Metadata
  notes: String,
  lastVerified: Date,
  createdAt: Date,
  updatedAt: Date
}

9. Investigation Timeline Events Collection

Purpose: Cross-platform chronological event timeline (populated by timeline_builder.py in Stage 10). Distinct from the investigation_timeline audit-log collection — this stores the user-facing timeline of account creation events, posts, and repo updates across platforms.

Collection: investigation_timeline_events

{
  _id: ObjectId,
  caseId: ObjectId,
  identityId: ObjectId,

  // Event
  timestamp: Date,              // ISO 8601
  platform: String,             // "github" | "reddit" | "pinterest" etc.
  eventType: String,            // "account_created" | "post" | "repo_update"
  label: String,                // Human-readable event description
  profileUrl: String,           // Link to the platform profile

  // Metadata
  source: String,               // "timeline_builder"
  createdAt: Date
}

10. Web Mentions Collection

Purpose: Store forum/blog/comment-section mentions found by web_mentions.py.

Collection: web_mentions

{
  _id: ObjectId,
  caseId: ObjectId,
  identityId: ObjectId,

  // Mention
  url: String,
  domain: String,
  title: String,
  snippet: String,

  // Metadata
  source: String,               // "web_mentions_agent"
  discoveredAt: Date,
  createdAt: Date
}

Behavior Analysis — Activity Gaps & Anomalies

The behavior_analysis collection (Section 6 above) already defines activityGaps and anomalies fields at lines 337-349. These fields are now populated by the anomaly_detection.py module (Stage 4.5), which runs after the behavior stage:

activityGaps[].startDate / endDate / duration — gaps > 14 days between consecutive events
anomalies[].type / description / timestamp / severity — spike events where a single day's post count exceeds mean + 2× standard deviation

The field names match exactly: activityGaps uses duration (number of days, float), not durationDays. No schema migration needed.

Limitations & Engagement Findings

investigation_findings collection stores limitations summaries and engagement scores as findings:

Limitations: { type: "limitations_summary", ... }
Engagement: { type: "engagement", ... }

No schema changes to investigation_findings — these are additive type values.

Neo4j Graph Schema

Node Types

1. Person Node

(:Person {
  id: String,
  displayName: String,
  confidenceScore: Float,
  riskScore: Float,
  status: String,
  createdAt: DateTime
})

2. Profile Node

(:Profile {
  id: String,
  platform: String,
  username: String,
  displayName: String,
  profileUrl: String,
  confidence: Float,
  status: String,
  createdAt: DateTime
})

3. Email Node

(:Email {
  address: String,
  verified: Boolean,
  source: String
})

4. Phone Node

(:Phone {
  number: String,
  verified: Boolean,
  source: String
})

5. Image Node

(:Image {
  hash: String,
  url: String,
  source: String
})

6. Post Node

(:Post {
  id: String,
  content: String,
  platform: String,
  timestamp: DateTime,
  sentiment: Float
})

7. Community Node

(:Community {
  name: String,
  platform: String,
  memberCount: Integer,
  category: String
})

8. Case Node

(:Case {
  id: String,
  caseNumber: String,
  title: String,
  status: String,
  createdAt: DateTime
})

Relationship Types

1. OWNS_PROFILE

(:Person)-[:OWNS_PROFILE {
  confidence: Float,
  discoveredAt: DateTime,
  method: String
}]->(:Profile)

2. USES_EMAIL

(:Person)-[:USES_EMAIL {
  verified: Boolean,
  source: String
}]->(:Email)

3. USES_PHONE

(:Person)-[:USES_PHONE {
  verified: Boolean,
  source: String
}]->(:Phone)

4. HAS_IMAGE

(:Person)-[:HAS_IMAGE {
  similarity: Float,
  source: String
}]->(:Image)

5. POSTED

(:Profile)-[:POSTED {
  timestamp: DateTime
}]->(:Post)

6. MENTIONS

(:Profile)-[:MENTIONS {
  timestamp: DateTime,
  context: String
}]->(:Profile)

7. FOLLOWS

(:Profile)-[:FOLLOWS {
  since: DateTime,
  platform: String
}]->(:Profile)

8. CONNECTED_TO

(:Person)-[:CONNECTED_TO {
  relationshipType: String,
  confidence: Float,
  strength: String
}]->(:Person)

9. PART_OF

(:Profile)-[:PART_OF {
  joinedAt: DateTime,
  role: String
}]->(:Community)

10. EVIDENCE_FOR

(:Profile)-[:EVIDENCE_FOR {
  evidenceType: String,
  confidence: Float
}]->(:Case)

11. SAME_AS

(:Person)-[:SAME_AS {
  confidence: Float,
  method: String,
  verifiedBy: String
}]->(:Person)

Graph Queries Examples

Find all profiles owned by a person

MATCH (p:Person {id: $personId})-[r:OWNS_PROFILE]->(profile:Profile)
RETURN p, r, profile
ORDER BY r.confidence DESC

Find relationship network

MATCH (p:Person {id: $personId})-[:OWNS_PROFILE]->(profile:Profile)
MATCH (profile)-[r:FOLLOWS|MENTIONS|CONNECTED_TO]-(other:Profile)
RETURN p, profile, r, other
LIMIT 50

Find community connections

MATCH (p:Person {id: $personId})-[:OWNS_PROFILE]->(profile:Profile)
MATCH (profile)-[:PART_OF]->(community:Community)
MATCH (community)<-[:PART_OF]-(otherProfile:Profile)
RETURN community, profile, otherProfile

Identity correlation path

MATCH path = (p1:Person)-[:OWNS_PROFILE]->(:Profile)-[*1..3]-(p2:Person)
WHERE p1.id = $person1Id AND p2.id = $person2Id
RETURN path
ORDER BY length(path)
LIMIT 5

Data Flow

Investigation Data Flow

 1. Discovery Agent
    ↓
    Creates: social_profiles
    ↓
 2. Enrichment Agent
    ↓
    Updates: social_profiles (enriched metadata)
    ↓
 3. Content Agent
    ↓
    Creates: content_analysis
    ↓
 4. Behavior Agent
    ↓
    Creates: behavior_analysis
    ↓
 5. Visual Agent
    ↓
    Creates: investigation_findings (visual matches)
    ↓
 6. Relationship Agent
    ↓
    Creates: relationships
    Updates: Neo4j graph
    ↓
 7. Correlation Agent
    ↓
    Creates: identity_profiles (identity clusters)
    Updates: social_profiles (links to identity)
    Consumes: content_analysis, behavior_analysis, visual_matches, relationships
    ↓
 8. Source Verification Agent
    ↓
    Creates/Updates: source_reliability
    ↓
 9. Report Agent
    ↓
    Reads: All collections
    Creates: reports

Indexes

MongoDB Indexes

// identity_profiles
db.identity_profiles.createIndex({ caseId: 1 })
db.identity_profiles.createIndex({ confidenceScore: -1 })
db.identity_profiles.createIndex({ "emails.email": 1 })
db.identity_profiles.createIndex({ "phones.number": 1 })

// social_profiles
db.social_profiles.createIndex({ identityId: 1 })
db.social_profiles.createIndex({ caseId: 1 })
db.social_profiles.createIndex({ platform: 1, username: 1 })
db.social_profiles.createIndex({ profileImageHash: 1 })

// investigation_findings
db.investigation_findings.createIndex({ caseId: 1 })
db.investigation_findings.createIndex({ type: 1, confidence: -1 })
db.investigation_findings.createIndex({ validationStatus: 1 })

// relationships
db.relationships.createIndex({ sourceIdentityId: 1 })
db.relationships.createIndex({ targetIdentityId: 1 })
db.relationships.createIndex({ caseId: 1 })

// investigation_timeline
db.investigation_timeline.createIndex({ caseId: 1, timestamp: -1 })
db.investigation_timeline.createIndex({ investigationId: 1, timestamp: -1 })

Neo4j Indexes

CREATE INDEX person_id FOR (p:Person) ON (p.id)
CREATE INDEX profile_username FOR (p:Profile) ON (p.platform, p.username)
CREATE INDEX email_address FOR (e:Email) ON (e.address)
CREATE INDEX phone_number FOR (p:Phone) ON (p.number)
CREATE INDEX image_hash FOR (i:Image) ON (i.hash)
CREATE INDEX case_id FOR (c:Case) ON (c.id)

Migration Strategy

Phase 1: Core Collections

identity_profiles
social_profiles
investigation_findings

Phase 2: Analysis Collections

content_analysis
behavior_analysis
relationships

Phase 3: Supporting Collections

investigation_timeline
source_reliability

Phase 4: Neo4j Integration

Create nodes from existing MongoDB data
Build relationships
Sync mechanism

This schema provides the complete data foundation for SOCMINT AI's investigation capabilities.

FilesExpand file tree

DATABASE_SCHEMA.md

Latest commit

History