06 · Text → Edges → Centrality → Spine

View on GitHub · Download .ipynb

The Path-2 move: text induces a graph, and the graph becomes point-in-time-correct features. Six authors post short Spanish messages; three of them paste the same campaign text at staggered dates — the copy-paste signature of coordination. We run the full two-stage φ-bridge pipeline (ADR-0001/ADR-0014):

posts ─ SentimentBridge ────────→ bridge_sentiment  (Path 1)
  └─── NearDuplicateEdgeBridge ─→ text_edges        (Path 2, stage 1)
          └─ CentralityBridge ──→ post_centrality   (stage 2, snapshots)
                └─ SQL spine ───→ feature matrix    (as-of bounded)

This tutorial executes against PostgreSQL (just db-up first; the committed outputs were produced against the throwaway database).

1. Setup and data

import sys
from pathlib import Path

# This tutorial EXECUTES against PostgreSQL (see the note above).
sys.path.insert(0, str(Path.cwd().parent.parent))  # repo root, for `featurizer`
sys.path.insert(0, str(Path.cwd().parent))  # examples/, for `_db`

import create_data

create_data.main()  # (re)seed the example_06 schema

✓ Seeded schema example_06: 6 authors, 6 posts, 2 as-of dates

The seeded posts: a1–a3 share the campaign text (staggered dates), a4–a6 are organic. Two as-of dates bound the backtest: at 2024-03-31 only the first copy exists; by 2024-06-30 the cluster has fully formed.

import psycopg
import _db

conn = psycopg.connect(_db.require_conninfo())
cur = conn.cursor()
cur.execute("set search_path to example_06")
cur.execute(
    "select post_id, author_id, posted_at, left(body, 44) from posts order by post_id"
)
for row in cur.fetchall():
    print(row)

(1, 'a1', datetime.date(2024, 1, 15), 'Terrible el nuevo reglamento del corredor: o')
(2, 'a2', datetime.date(2024, 2, 20), 'Terrible el nuevo reglamento del corredor: o')
(3, 'a3', datetime.date(2024, 5, 10), 'Terrible el nuevo reglamento del corredor: o')
(4, 'a4', datetime.date(2024, 1, 20), 'Excelente jornada en el puerto, servicio ráp')
(5, 'a5', datetime.date(2024, 3, 5), 'La asamblea revisó el calendario de obras si')
(6, 'a6', datetime.date(2024, 4, 12), 'Buena respuesta del operador aunque persiste')

2. Path 1 — reduce each post to a sentiment scalar

SentimentBridge is dependency-free with a Spanish-register default lexicon (never a silent English default). persist=True writes a real table — the orchestrated-asset flow you would wire into Dagster/Snakemake upstream of the SQL run.

from featurizer.bridge import SentimentBridge

for table in ("bridge_sentiment", "text_edges", "post_centrality"):
    cur.execute(f"drop table if exists {table}")  # idempotent re-runs

sentiment = SentimentBridge(pk_col="post_id", text_col="body")
sentiment.materialize(
    conn,
    source_table="posts",
    pk="post_id",
    carry_cols=["author_id", "posted_at"],
    content_cols=["body"],
    output_table="bridge_sentiment",
    persist=True,
)
cur.execute(
    "select post_id, author_id, sentiment from bridge_sentiment order by post_id"
)
for row in cur.fetchall():
    print(row)

(1, 'a1', -0.72)
(2, 'a2', -0.72)
(3, 'a3', -0.72)
(4, 'a4', 0.7000000000000001)
(5, 'a5', None)
(6, 'a6', 0.0)

The pasted campaign scores −0.72 wherever it appears; a5’s post has no lexicon evidence → NULL (no evidence ≠ neutral).

3. Path 2, stage 1 — near-duplicate text induces edges

MinHash/LSH over word shingles. An edge connects the authors of two near-duplicate posts and is knowable at the later post of the pair — causality lives on the edge timestamp. Self-copies are excluded (pasting your own text is not coordination).

from featurizer.bridge import NearDuplicateEdgeBridge

edges = NearDuplicateEdgeBridge(
    pk_col="post_id",
    entity_col="author_id",
    text_col="body",
    ts_col="posted_at",
)
edges.materialize_edges(
    conn,
    source_table="posts",
    output_table="text_edges",
    content_cols=["post_id", "author_id", "posted_at", "body"],
    persist=True,
)
cur.execute("select * from text_edges order by ts, src")
for row in cur.fetchall():
    print(row)

('a1', 'a2', datetime.date(2024, 2, 20))
('a1', 'a3', datetime.date(2024, 5, 10))
('a2', 'a3', datetime.date(2024, 5, 10))

Three edges — the (a1, a2) pair on 2024-02-20, then a3 joins both on 2024-05-10. Organic authors never appear.

4. Path 2, stage 2 — centrality snapshots per as-of window

Centrality is non-local: one future edge changes every node’s score, so the graph is rebuilt per window from strictly pre-t₀ edges — never computed once and sliced. The output is keyed (node_id, as_of_date): an ordinary event stream the spine trends. The cheap metric tier is the default; betweenness/eigenvector/ closeness are opt-in (include_heavy=True) so configs never get silently slower.

from featurizer.bridge import CentralityBridge

cur.execute("select as_of_date from as_of_dates order by as_of_date")
as_of_dates = [row[0] for row in cur.fetchall()]

centrality = CentralityBridge(source_col="src", target_col="dst", directed=False)
centrality.materialize_snapshots(
    conn,
    source_table="text_edges",
    output_table="post_centrality",
    as_of_dates=as_of_dates,
    causal_col="ts",
    content_cols=["src", "dst"],
    entity_col="node_id",
    as_of_col="as_of_date",
    persist=True,
)
cur.execute(
    "select node_id, as_of_date, degree, clustering from post_centrality order by as_of_date, node_id"
)
for row in cur.fetchall():
    print(row)
conn.commit()

('a1', datetime.date(2024, 3, 31), 1.0, 0.0)
('a2', datetime.date(2024, 3, 31), 1.0, 0.0)
('a1', datetime.date(2024, 6, 30), 2.0, 1.0)
('a2', datetime.date(2024, 6, 30), 2.0, 1.0)
('a3', datetime.date(2024, 6, 30), 2.0, 1.0)

At the March cut only the first pair exists (degree 1, clustering 0); by June the triangle has closed (degree 2, clustering 1.0) — and the March rows stay what was knowable in March.

5. The config — bridge outputs are ordinary entities

emit_yaml() produces the entity + relationship fragments; this example commits them in config.yaml and asserts equality, so the declared config cannot drift from what the bridges emit.

import yaml

fragment = centrality.emit_yaml(
    output_table="post_centrality",
    pk="node_id",
    parent_alias="authors",
    parent_key="author_id",
    fk="node_id",
    temporal_ix="as_of_date",
)
declared = yaml.safe_load(Path("config.yaml").read_text())
assert fragment["entity"] == {e["alias"]: e for e in declared["entities"]}["centrality"]
print(yaml.safe_dump(fragment["entity"], sort_keys=False))

alias: centrality
table: post_centrality
id: node_id
variables:
  degree:
    type: numeric
  in_degree:
    type: numeric
  out_degree:
    type: numeric
  weighted_degree:
    type: numeric
  coreness:
    type: numeric
  clustering:
    type: numeric
temporal_ix: as_of_date

6. The spine — the feature matrix

From here everything is standard featurizer: the snapshot stream and the sentiment column aggregate under the normal <= as_of_date causal bound.

import os
from featurizer import Featurizer

os.environ["DATABASE_URL"] = _db.records_url("example_06")
featurizer = Featurizer("config.yaml")
df = featurizer.to_dataframe()
df[
    [
        c
        for c in df.columns
        if "MAX(centrality.degree)" in c
        or "MAX(centrality.clustering)" in c
        or "MEAN(sentiment.sentiment)" in c
    ]
]

2026-07-18 22:53:31.050 | DEBUG    | featurizer.planner:plan:252 - Starting feature build for target authors


2026-07-18 22:53:31.050 | DEBUG    | featurizer.planner:_build_features:278 - build_features(authors) depth=0


2026-07-18 22:53:31.050 | DEBUG    | featurizer.planner:_build_features:278 - build_features(centrality) depth=1


2026-07-18 22:53:31.050 | INFO     | featurizer.planner:_build_features:298 - Maximum recursion depth reached at depth 2; materializing centrality without traversing further.


2026-07-18 22:53:31.051 | DEBUG    | featurizer.planner:_build_aggregations:1270 - Processing backward relationship Entity(authors).author_id -> Entity(centrality).node_id


2026-07-18 22:53:31.051 | DEBUG    | featurizer.planner:_build_features:278 - build_features(sentiment) depth=1


2026-07-18 22:53:31.051 | INFO     | featurizer.planner:_build_features:298 - Maximum recursion depth reached at depth 2; materializing sentiment without traversing further.


2026-07-18 22:53:31.051 | DEBUG    | featurizer.planner:_build_aggregations:1270 - Processing backward relationship Entity(authors).author_id -> Entity(sentiment).author_id


2026-07-18 22:53:31.051 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'authors': 8 CTEs, 4932 chars


2026-07-18 22:53:31.052 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'authors': 8 CTEs, 4932 chars

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

		MAX(centrality.clustering)	MAX(centrality.degree)	MEAN(sentiment.sentiment)
as_of_date	author_id
2024-03-31	a1	0.0	1.0	-0.72
	a2	0.0	1.0	-0.72
	a3	NaN	NaN	NaN
	a4	NaN	NaN	0.70
	a5	NaN	NaN	NaN
	a6	NaN	NaN	NaN
2024-06-30	a1	1.0	2.0	-0.72
	a2	1.0	2.0	-0.72
	a3	1.0	2.0	-0.72
	a4	NaN	NaN	0.70
	a5	NaN	NaN	NaN
	a6	NaN	NaN	0.00

7. Reading the signal

• The coordinated trio (a1–a3) carries centrality that grows between the as-of dates — degree 1 → 2, clustering 0 → 1 as the copy-paste triangle closes — while every organic author stays NULL (never in the induced graph; never fabricated as 0).
• The campaign sentiment (−0.72) travels with the pasted text.
• Both signals are point-in-time correct: the March row knows nothing about May’s paste.

The heavier variants of everything here — multilingual NER counts, Louvain communities over the induced graph, embedding-trajectory novelty, change-point scores — follow the same compute → materialize → emit_yaml lifecycle; see the bridge cookbook. For cheap graph features without any Python at all, see the native graph_relationships block in the configuration reference.

conn.close()