The Topical Authority Map: Plan Site Architecture Before Writing

┌─────────────────────────────────────────────────────────────┐
│                 Topical Authority Knowledge Graph           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│                    ┌──────────────────────┐                 │
│                    │    Primary Entity    │                 │
│                    │ (Parent Pillar Node) │                 │
│                    └──────────┬───────────┘                 │
│                               │                             │
│         ┌─────────────────────┼─────────────────────┐       │
│         │                     │                     │       │
│         ▼                     ▼                     ▼       │
│  ┌──────────────┐      ┌──────────────┐      ┌────────────┐ │
│  │ Sub-Entity A │      │ Sub-Entity B │      │Sub-Entity C│ │
│  │ (Architecture│      │  (Security   │      │(Storage &  │ │
│  │  & Networking│      │ & Compliance)│      │ Persistence│ │
│  └──────┬───────┘      └──────┬───────┘      └─────┬──────┘ │
│         │                     │                    │        │
│    ┌────┴────┐           ┌────┴────┐          ┌────┴────┐   │
│    ▼         ▼           ▼         ▼          ▼         ▼   │
│ [Spoke 1] [Spoke 2]  [Spoke 3] [Spoke 4]  [Spoke 5] [Spoke 6]│
│                                                             │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│           5-Step Topical Authority Decomposition Protocol   │
├────────┬─────────────────────────┬──────────────────────────┤
│ Step   │ Operational Phase       │ Primary Deliverable      │
├────────┼─────────────────────────┼──────────────────────────┤
│ Step 1 │ Core Entity Definition  │ Target Knowledge Entity  │
│ Step 2 │ Semantic Sub-Branching  │ 4–6 Cluster Sub-Themes   │
│ Step 3 │ Intent & Keyword Map    │ Tiered Keyword Inventory │
│ Step 4 │ Link Topology Graph     │ Bidirectional Link Rules │
│ Step 5 │ Content Gap Triage      │ Phased Publishing Queue  │
└────────┴─────────────────────────┴──────────────────────────┘

┌────────────────────────────────────────────────────────────────────────────────────────┐
│                        Topical Authority Architecture Worksheet                        │
├──────────────┬──────────────────┬──────────────┬─────────────┬─────────────┬───────────┤
│ Node ID      │ URL Slug         │ Entity Topic │ Search Type │ Click Depth │ Inbound PR│
├──────────────┼──────────────────┼──────────────┼─────────────┼─────────────┼───────────┤
│ HUB-001      │ /db-tuning       │ DB Perf Hub  │ Commercial  │ Depth 1     │ Primary   │
│ CLUST-01     │ /db-indexing     │ Index Types  │ Pillar      │ Depth 2     │ High      │
│ SPOKE-01A    │ /b-tree-indexes  │ B-Tree Deep  │ Informational│ Depth 3    │ Reciprocal│
│ SPOKE-01B    │ /gist-gin-index  │ GIN/GiST     │ Informational│ Depth 3    │ Reciprocal│
│ CLUST-02     │ /vacuum-tuning   │ Autovacuum   │ Pillar      │ Depth 2     │ High      │
│ SPOKE-02A    │ /table-bloat-fix │ Bloat Audit  │ Diagnostic  │ Depth 3     │ Reciprocal│
│ SPOKE-02B    │ /freeze-maps-io  │ Freeze Maps  │ Technical   │ Depth 3     │ Reciprocal│
└──────────────┴──────────────────┴──────────────┴─────────────┴─────────────┴───────────┘

node_id,parent_hub_id,url_path,page_title,target_keyword,intent_type,target_click_depth,planned_outbound_links,planned_inbound_donors
HUB-001,ROOT,/database/postgresql-performance-tuning,"PostgreSQL Performance Tuning: Complete Architecture Guide",postgresql performance tuning,commercial,1,"CLUST-01,CLUST-02,CLUST-03",ROOT
CLUST-01,HUB-001,/database/postgresql-indexing-strategies,"PostgreSQL Indexing Strategies: B-Tree, GiST, GIN, and BRIN",postgresql indexing strategies,informational,2,"HUB-001,SPOKE-01A,SPOKE-01B","HUB-001,SPOKE-01A,SPOKE-01B"
SPOKE-01A,CLUST-01,/database/postgresql-btree-indexes,"B-Tree Indexing in PostgreSQL: Mechanics, Bloat, and Maintenance",postgresql btree index,technical,3,"CLUST-01,SPOKE-01B","CLUST-01,HUB-001"
SPOKE-01B,CLUST-01,/database/postgresql-gin-gist-indexes,"GIN vs GiST Indexes in PostgreSQL: When to Use Which",gin vs gist postgresql,technical,3,"CLUST-01,SPOKE-01A","CLUST-01"
CLUST-02,HUB-001,/database/postgresql-vacuum-optimization,"PostgreSQL VACUUM Optimization: Cost Limits and Bloat Prevention",postgresql vacuum optimization,informational,2,"HUB-001,SPOKE-02A,SPOKE-02B","HUB-001,SPOKE-02A"
SPOKE-02A,CLUST-02,/database/postgresql-table-bloat-repack,"How to Detect and Remove PostgreSQL Table Bloat with pg_repack",postgresql table bloat repack,diagnostic,3,"CLUST-02","CLUST-02,HUB-001"

#!/usr/bin/env python3
"""
validate_authority_map.py
Validates a planned site architecture CSV to ensure zero orphan nodes,
flat click depth hierarchy, and balanced link reciprocity.
"""

import sys
import csv
import networkx as nx

def validate_architecture(csv_file_path: str):
    print(f"\n=======================================================")
    print(f"VALIDATING TOPICAL AUTHORITY MAP: {csv_file_path}")
    print(f"=======================================================\n")

    G = nx.DiGraph()
    nodes = {}

    with open(csv_file_path, mode='r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            node_id = row['node_id'].strip()
            nodes[node_id] = row
            G.add_node(node_id, **row)

    # Add edges based on planned outbound links
    for node_id, data in nodes.items():
        outbound = [x.strip() for x in data['planned_outbound_links'].split(',') if x.strip()]
        for target in outbound:
            if target in nodes:
                G.add_edge(node_id, target)
            else:
                print(f"[!] WARNING: Node {node_id} targets non-existent node: '{target}'")

    print(f"[*] Total Planned Graph Nodes: {G.number_of_nodes()}")
    print(f"[*] Total Planned Graph Edges: {G.number_of_edges()}")

    # 1. Check for Orphan Nodes (Zero in-degree)
    orphans = [n for n, deg in G.in_degree() if deg == 0 and n != 'HUB-001']
    if orphans:
        print(f"\n[FAIL] Found {len(orphans)} orphan nodes with ZERO inbound links:")
        for o in orphans:
            print(f"   ❌ {o} ({nodes[o]['url_path']})")
    else:
        print("\n[✓] PASS: Zero orphan nodes found in planned architecture.")

    # 2. Check for Dead-End Leaves (Zero out-degree)
    dead_ends = [n for n, deg in G.out_degree() if deg == 0]
    if dead_ends:
        print(f"\n[!] ADVICE: Found {len(dead_ends)} dead-end nodes with ZERO outbound links:")
        for d in dead_ends:
            print(f"   ⚠️ {d} ({nodes[d]['url_path']})")
    else:
        print("\n[✓] PASS: All nodes provide onward crawl paths.")

    # 3. Verify Shortest Path / Click Depth from Root Hub
    print("\n--- Modeled Click Depth from Root Hub (HUB-001) ---")
    depth_violations = []
    for n in G.nodes():
        if n == 'HUB-001':
            continue
        try:
            depth = nx.shortest_path_length(G, source='HUB-001', target=n)
            planned_depth = int(nodes[n]['target_click_depth'])
            status = "OK" if depth <= planned_depth else "EXCEEDED"
            print(f" {n:<10} | Planned: {planned_depth} | Actual Hop: {depth} [{status}]")
            if depth > 3:
                depth_violations.append((n, depth))
        except nx.NetworkXNoPath:
            print(f" {n:<10} | [NO PATH FROM ROOT HUB!]")
            depth_violations.append((n, "Unreachable"))

    if depth_violations:
        print(f"\n[!] WARNING: {len(depth_violations)} nodes exceed recommended click depth (<= 3 hops):")
        for v in depth_violations:
            print(f"   ⚠️ {v[0]}: {v[1]} hops from root")
    else:
        print("\n[✓] PASS: Entire cluster resides within 3 hops of root hub.")

    print("\n=======================================================")
    print("VALIDATION COMPLETE.")
    print("=======================================================\n")

if __name__ == '__main__':
    if len(sys.argv) < 2:
        print("Usage: python3 validate_authority_map.py <path_to_csv>")
        sys.exit(1)
    validate_architecture(sys.argv[1])

python3 scripts/validate_authority_map.py site_architecture_map.csv

#!/usr/bin/env python3
"""
entity_cooccurrence_analyzer.py
Extracts named entities from seed documents and computes an entity
co-occurrence matrix to uncover semantic gaps in planned topic clusters.
"""

import spacy
from collections import Counter, defaultdict
import itertools

# Load small English model (or en_core_web_trf for production accuracy)
nlp = spacy.load("en_core_web_sm")

def extract_entities(text_corpus: list) -> list:
    """Extracts domain-relevant entities (ORG, PRODUCT, GPE, LAW, TECH)."""
    document_entities = []
    for doc_text in text_corpus:
        doc = nlp(doc_text)
        # Extract unique named entities and technical noun chunks
        entities = set()
        for ent in doc.ents:
            if ent.label_ in {"ORG", "PRODUCT", "WORK_OF_ART", "LAW"}:
                entities.add(ent.text.strip().lower())
        for chunk in doc.noun_chunks:
            if len(chunk.text.split()) in {2, 3} and not chunk.root.is_stop:
                entities.add(chunk.text.strip().lower())
        document_entities.append(list(entities))
    return document_entities

def compute_cooccurrence(document_entities: list, min_frequency: int = 2):
    cooccurrences = defaultdict(int)
    entity_counts = Counter()

    for entities in document_entities:
        for ent in entities:
            entity_counts[ent] += 1
        # Generate co-occurring pairs within same document
        for ent1, ent2 in itertools.combinations(sorted(entities), 2):
            cooccurrences[(ent1, ent2)] += 1

    print("\n=======================================================")
    print("TOP CO-OCCURRING ENTITIES (SEMANTIC MAP REQUIREMENTS)")
    print("=======================================================\n")
    
    sorted_pairs = sorted(cooccurrences.items(), key=lambda x: x[1], reverse=True)
    for (e1, e2), freq in sorted_pairs[:15]:
        if freq >= min_frequency:
            print(f" • [Strength: {freq:2d}] {e1:<28} <---> {e2}")

# Example competitive seed corpus for PostgreSQL Performance
sample_corpus = [
    "PostgreSQL vacuum optimization prevents table bloat using autovacuum cost limit and freeze maps.",
    "B-Tree index bloat in PostgreSQL requires pg_repack to reclaim disk storage without locking read queries.",
    "PostgreSQL query execution plans rely on pg_stat_statements, buffer cache hits, and shared_buffers tuning.",
    "PostgreSQL autovacuum workers clean dead tuples and update freeze maps to prevent transaction ID wraparound."
]

if __name__ == "__main__":
    extracted = extract_entities(sample_corpus)
    compute_cooccurrence(extracted)

#!/usr/bin/env python3
"""
generate_dot_graph.py
Converts site_architecture_map.csv into a publication-ready Graphviz DOT diagram.
"""

import csv
import sys

def csv_to_dot(csv_path: str, dot_path: str):
    nodes = []
    edges = []

    with open(csv_path, mode='r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            node_id = row['node_id'].strip()
            title = row['page_title'].replace('"', '\\"')
            intent = row.get('intent_type', 'informational').lower()
            depth = row.get('target_click_depth', '1')

            # Color styling by hierarchy tier
            if "HUB" in node_id:
                style = 'shape=box, style="filled,rounded", fillcolor="#1e3a8a", fontcolor="#ffffff", penwidth=2'
            elif "CLUST" in node_id:
                style = 'shape=ellipse, style="filled", fillcolor="#0284c7", fontcolor="#ffffff"'
            else:
                style = 'shape=note, style="filled", fillcolor="#f0fdf4", fontcolor="#166534"'

            label = f"{node_id}\\n{title}\\n(Depth: {depth})"
            nodes.append(f'    "{node_id}" [label="{label}", {style}];')

            # Build edges
            outbound = [x.strip() for x in row['planned_outbound_links'].split(',') if x.strip()]
            for dest in outbound:
                edges.append(f'    "{node_id}" -> "{dest}" [color="#94a3b8", arrowsize=0.8];')

    dot_content = "digraph TopicalAuthorityMap {\n"
    dot_content += "    rankdir=TB;\n"
    dot_content += "    node [fontname=\"Helvetica,Arial,sans-serif\", fontsize=10];\n"
    dot_content += "    edge [fontname=\"Helvetica,Arial,sans-serif\", fontsize=8];\n\n"
    dot_content += "\n".join(nodes) + "\n\n"
    dot_content += "\n".join(edges) + "\n"
    dot_content += "}\n"

    with open(dot_path, 'w', encoding='utf-8') as f:
        f.write(dot_content)

    print(f"[✓] Graphviz DOT file written successfully to: {dot_path}")

if __name__ == '__main__':
    csv_input = sys.argv[1] if len(sys.argv) > 1 else 'site_architecture_map.csv'
    dot_output = sys.argv[2] if len(sys.argv) > 2 else 'site_architecture_map.dot'
    csv_to_dot(csv_input, dot_output)

dot -Tsvg site_architecture_map.dot -o site_architecture_map.svg

-- DDL Schema for Content Cluster Architecture Management
CREATE TABLE content_clusters (
    cluster_id VARCHAR(32) PRIMARY KEY,
    cluster_name VARCHAR(128) NOT NULL,
    pillar_url_slug VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE topic_nodes (
    node_id VARCHAR(32) PRIMARY KEY,
    cluster_id VARCHAR(32) REFERENCES content_clusters(cluster_id) ON DELETE CASCADE,
    url_slug VARCHAR(255) NOT NULL UNIQUE,
    target_keyword VARCHAR(128) NOT NULL,
    intent_type VARCHAR(32) CHECK (intent_type IN ('pillar', 'commercial', 'informational', 'diagnostic')),
    target_click_depth INT NOT NULL CHECK (target_click_depth BETWEEN 1 AND 4),
    is_published BOOLEAN DEFAULT FALSE
);

CREATE TABLE planned_link_edges (
    source_node_id VARCHAR(32) REFERENCES topic_nodes(node_id) ON DELETE CASCADE,
    target_node_id VARCHAR(32) REFERENCES topic_nodes(node_id) ON DELETE CASCADE,
    planned_anchor_text VARCHAR(128) NOT NULL,
    is_reciprocal BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (source_node_id, target_node_id)
);

-- Query 1: Detect Asymmetric Spokes (Spokes missing reciprocal links to their parent Hub)
SELECT 
    tn.node_id AS spoke_node,
    tn.url_slug AS spoke_url,
    cc.cluster_name,
    cc.pillar_url_slug
FROM topic_nodes tn
JOIN content_clusters cc ON tn.cluster_id = cc.cluster_id
WHERE tn.intent_type != 'pillar'
  AND NOT EXISTS (
      SELECT 1 
      FROM planned_link_edges ple
      JOIN topic_nodes hub_node ON ple.target_node_id = hub_node.node_id
      WHERE ple.source_node_id = tn.node_id
        AND hub_node.url_slug = cc.pillar_url_slug
  );

-- Query 2: Find Anemic Topic Clusters with fewer than 4 supporting spokes
SELECT 
    cc.cluster_id,
    cc.cluster_name,
    COUNT(tn.node_id) AS total_spokes
FROM content_clusters cc
LEFT JOIN topic_nodes tn ON cc.cluster_id = tn.cluster_id AND tn.intent_type != 'pillar'
GROUP BY cc.cluster_id, cc.cluster_name
HAVING COUNT(tn.node_id) < 4;