duckdb-ies

Layer 4: IES Interactome Analytics with GF(3) Momentum Tracking

Version: 2.0.0 Trit: +1 (Generative - produces analysis artifacts) Bundle: analytics Extends: duckdb-timetravel

Overview

DuckDB-IES provides unified interactome analytics across Claude history, GitHub activity, workspace files, and skill manifests. It implements GF(3) momentum tracking, topic clustering, and cross-source fingerprint correlation.

Database Location

/Users/bob/ies/ducklake_data/ies_interactome.duckdb

Core Tables

Core Views

unified_interactions

Merges all sources into single stream:

SELECT timestamp, source, content, category, fingerprint, color_hex, trit
FROM unified_interactions
WHERE source = 'claude' AND timestamp > '2025-12-20';

gf3flowanalysis

Daily GF(3) balance tracking:

SELECT day, total_interactions, daily_gf3_sum, gf3_status, breakdown
FROM gf3_flow_analysis
WHERE gf3_status = '✓ balanced';

gf3momentumdetector

Hourly drift detection with velocity:

SELECT hour, cumulative_gf3, gf3_velocity_6h, momentum_status
FROM gf3_momentum_detector
WHERE momentum_status LIKE '%DRIFT%';

fingerprint_correlations

Cross-source co-occurrence within 1-hour windows:

SELECT edge_type, correlation_count, avg_time_delta
FROM fingerprint_correlations
ORDER BY correlation_count DESC;

interaction_velocity

Hourly momentum with cumulative GF(3):

SELECT hour, interactions, velocity, cumulative_gf3
FROM interaction_velocity
WHERE velocity > 20;  -- High activity spikes

simultaneity_surfaces

High-density interaction periods:

SELECT hour_bucket, density, gf3_sum, gf3_status, palette
FROM simultaneity_surfaces;

Capabilities

1. ingest-claude-history

CREATE OR REPLACE TABLE claude_history AS 
SELECT 
    display, timestamp,
    to_timestamp(timestamp/1000) as ts,
    project, sessionId,
    CASE 
        WHEN LOWER(display) LIKE '%duckdb%' THEN 'duckdb'
        WHEN LOWER(display) LIKE '%skill%' THEN 'skill'
        ELSE 'other'
    END as interaction_type
FROM read_json('~/.claude/history.jsonl', 
    format='newline_delimited',
    ignore_errors=true
);

2. apply-gay-coloring

-- Add Gay.jl deterministic coloring
CREATE OR REPLACE TABLE claude_history_colored AS
SELECT 
    *,
    hash(display || COALESCE(project,'') || CAST(timestamp AS VARCHAR)) as fingerprint,
    '#' || printf('%06x', ABS(hash(display)) % 16777216) as color_hex,
    CAST(ABS(hash(display)) % 3 AS INTEGER) - 1 as trit
FROM claude_history;

3. topic-extraction

-- Content-based topic clustering via regex
CREATE OR REPLACE TABLE topic_clusters AS
WITH topics AS (
    SELECT 
        content, source,
        CASE
            WHEN LOWER(content) LIKE '%duckdb%' THEN 'duckdb'
            WHEN LOWER(content) LIKE '%gay%' OR LOWER(content) LIKE '%color%' THEN 'gay-coloring'
            WHEN LOWER(content) LIKE '%acset%' THEN 'acsets'
            WHEN LOWER(content) LIKE '%skill%' THEN 'skills'
            WHEN LOWER(content) LIKE '%mcp%' THEN 'mcp'
            ELSE 'general'
        END as topic,
        trit, color_hex, timestamp
    FROM unified_interactions
)
SELECT 
    topic, COUNT(*) as mentions,
    SUM(trit) as gf3_sum,
    CASE WHEN SUM(trit) % 3 = 0 THEN '✓' ELSE '⚠' END as balanced,
    MIN(timestamp) as first_seen,
    MAX(timestamp) as last_seen
FROM topics
GROUP BY topic
ORDER BY mentions DESC;

4. momentum-detection

-- GF(3) momentum with 6h/24h velocity windows
CREATE OR REPLACE VIEW gf3_momentum_detector AS
WITH cumulative AS (
    SELECT 
        DATE_TRUNC('hour', timestamp) as hour,
        SUM(trit) as hourly_trit,
        SUM(SUM(trit)) OVER (ORDER BY DATE_TRUNC('hour', timestamp)) as cumulative_gf3
    FROM unified_interactions
    WHERE timestamp IS NOT NULL
    GROUP BY 1
),
with_velocity AS (
    SELECT 
        *,
        cumulative_gf3 - LAG(cumulative_gf3, 6) OVER (ORDER BY hour) as gf3_velocity_6h,
        cumulative_gf3 - LAG(cumulative_gf3, 24) OVER (ORDER BY hour) as gf3_velocity_24h
    FROM cumulative
)
SELECT 
    hour, hourly_trit, cumulative_gf3,
    gf3_velocity_6h, gf3_velocity_24h,
    CASE 
        WHEN ABS(gf3_velocity_6h) > 15 THEN '🔴 HIGH DRIFT'
        WHEN ABS(gf3_velocity_6h) > 8 THEN '🟡 MODERATE DRIFT'  
        WHEN cumulative_gf3 % 3 = 0 THEN '🟢 BALANCED'
        ELSE '⚪ STABLE'
    END as momentum_status
FROM with_velocity
ORDER BY hour DESC;

5. parquet-export

-- Export to Parquet for external analysis
COPY (SELECT * FROM unified_interactions WHERE timestamp IS NOT NULL)
TO 'ducklake_data/parquet/unified_interactions.parquet' (FORMAT PARQUET);

COPY (SELECT * FROM gf3_flow_analysis)
TO 'ducklake_data/parquet/gf3_flow.parquet' (FORMAT PARQUET);

COPY (SELECT * FROM simultaneity_surfaces)
TO 'ducklake_data/parquet/simultaneity_surfaces.parquet' (FORMAT PARQUET);

GF(3) Triad Integration

Conservation: (-1) + (0) + (+1) = 0 ✓

Current Interactome Stats

Total Interactions: 1733
Sources: 4 (claude, github_repo, github_contrib, skill)
Global GF(3): 2 (⚠ drift)
Balanced Topics: duckdb, gay-coloring, acsets, crdt, mcp, world-modeling

Topic Distribution

Parquet Outputs

ducklake_data/parquet/
├── unified_interactions.parquet
├── gf3_flow.parquet
└── simultaneity_surfaces.parquet

CLI Recipes

# Quick interactome status
duckdb /Users/bob/ies/ducklake_data/ies_interactome.duckdb -c "
SELECT source, COUNT(*), SUM(trit) as gf3 FROM unified_interactions GROUP BY source;"

# Check momentum drift
duckdb /Users/bob/ies/ducklake_data/ies_interactome.duckdb -c "
SELECT * FROM gf3_momentum_detector WHERE momentum_status LIKE '%DRIFT%' LIMIT 10;"

# Topic balance check
duckdb /Users/bob/ies/ducklake_data/ies_interactome.duckdb -c "
SELECT topic, mentions, gf3_sum, balanced FROM topic_clusters ORDER BY mentions DESC;"

# Recent high-density hours
duckdb /Users/bob/ies/ducklake_data/ies_interactome.duckdb -c "
SELECT * FROM simultaneity_surfaces ORDER BY density DESC LIMIT 5;"

Related Skills

• duckdb-timetravel - Temporal versioning layer
• gay-mcp - Deterministic color generation
• acsets - Category-theoretic schema
• entropy-sequencer - Temporal arrangement
• bisimulation-game - Cross-agent skill dispersal

REPL atlas

Part of: repl-commons. Family canonical: duckdb-guard.