NetShield — Unified Pipeline Architecture Plan

Glossary — Technical Players & Their Roles

Before diving into the architecture, here is a plain-language explanation of every tool and service used in this plan — what it is, what problem it solves, and what role it plays specifically in the NetShield pipeline.

1. Current State — The Problem

The pipeline is split across three machines with no shared infrastructure. Every daily run requires manual coordination between three people.

2. Target State — What We Are Building

The goal is a single automated daily pipeline where:

• Data Collection team pushes their NDJSON files to a shared GitHub repo and that push automatically starts the full pipeline.
• The ELT pipeline (load + dbt + report) runs in the cloud with zero human involvement.
• The DS team reads directly from a cloud PostgreSQL database — always up to date.
• All logs, run results, and the HTML report are accessible from GitHub — no local server needed.

3. Architecture Diagram

BEFORE (current)

AFTER (target)

4. Component Breakdown & Free-Tier Resources

5. GitHub Actions as the Orchestrator

GitHub Actions runs a YAML workflow file on a schedule. Each job = one Airflow task. Jobs can depend on each other, pass data between steps, and send notifications on failure — everything Airflow does for this use case.

Workflow Design

# .github/workflows/daily_pipeline.yml
name: NetShield Daily Pipeline

on:
  schedule:
    - cron: '0 6 * * *'          # runs every day at 06:00 UTC
  workflow_dispatch:              # also allows manual trigger from GitHub UI
  repository_dispatch:            # triggered by data collection team's push
    types: [data-ready]

jobs:

  load-and-transform:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout ELT repo
        uses: actions/checkout@v4

      - name: Checkout data collection repo
        uses: actions/checkout@v4
        with:
          repository: Trendact/data-collection   # data team's repo
          path: data_intake

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.13'
          cache: pip

      - name: Install dependencies
        run: pip install -r requirements.txt

      - name: Run pipeline (load + dbt + report)
        env:
          DB_HOST:     ${{ secrets.NEON_HOST }}
          DB_USER:     ${{ secrets.NEON_USER }}
          DB_PASSWORD: ${{ secrets.NEON_PASSWORD }}
          DB_NAME:     ${{ secrets.NEON_DB }}
        run: python run_pipeline.py

      - name: Publish report to GitHub Pages
        uses: peaceiris/actions-gh-pages@v4
        with:
          github_token: ${{ secrets.GITHUB_TOKEN }}
          publish_dir: ./reports

Action Minutes Usage Estimate (at 100k rows/load)

6. Cloud PostgreSQL — Neon (Free)

Neon is a serverless PostgreSQL provider with a generous free tier — fully compatible with psycopg2, dbt-postgres, and SQLAlchemy. No credit card required to sign up.

Actual Current Data Size (measured March 14, 2026)

The local PostgreSQL database was measured before recommending a storage tier. Here are the real numbers:

Storage Growth Projection

Average storage per row across all tables: ~0.6 KB. Target load: 100,000 rows per load = ~60 MB per daily run.

Archival Strategy (keeps DB lean)

Rather than storing every raw NDJSON row forever, the pipeline should periodically archive old raw_data tables to compressed files in GitHub Releases and truncate the source tables. The social_data_alfa transformed tables are much smaller and can be kept live indefinitely.

Why Neon vs alternatives

Required code change — .env update

After creating the Neon project, replace your local connection string:

# .env  (local machine — never committed)
DB_HOST=ep-xxxxx.us-east-2.aws.neon.tech
DB_PORT=5432
DB_NAME=neondb
DB_USER=netshield_user
DB_PASSWORD=xxxxxxxxxxxxxxxx
DB_SSLMODE=require                          # required for Neon

# GitHub Actions secrets (set in repo Settings → Secrets)
# NEON_HOST, NEON_USER, NEON_PASSWORD, NEON_DB

The db_config.py and profiles.yml files already read from environment variables — only the values change, not the code logic.

7. Data Handoff Strategy Between Teams

The weakest link in the current flow is how NDJSON files get from the data collection machine to the ELT pipeline. There are two options:

Option A — GitHub Releases as Data Drop Zone (Recommended)

The data collection team uploads their NDJSON file as a GitHub Release asset to the Trendact/data-intake repo (up to 2 GB per file, free on public repos). Creating the release triggers a release event, which fires the ELT workflow automatically — no polling, no waiting for cron.

# In data-intake repo — .github/workflows/notify.yml
on:
  release:
    types: [published]   # fires when data team publishes a new release
jobs:
  notify-elt:
    runs-on: ubuntu-latest
    steps:
      - name: Trigger ELT pipeline with release URL
        run: |
          curl -X POST \
            -H "Authorization: token ${{ secrets.PAT_TOKEN }}" \
            -H "Accept: application/vnd.github.v3+json" \
            https://api.github.com/repos/Trendact/-elt/dispatches \
            -d '{"event_type":"data-ready","client_payload":{"asset_url":"${{ github.event.release.assets[0].browser_download_url }}"}}'

The ELT workflow receives the direct download URL of the NDJSON file as a payload, downloads it with curl inside the Actions runner, and processes it. No large files ever enter a git commit.

Option B — Single Monorepo

All teams commit to the same repo under separate folders (data_intake/, elt/). Simpler for a small team, but mixes concerns and makes the repo large over time as NDJSON files accumulate.

DS Team Access (Trendact/-ai-pipeline)

After migration, the DS team updates their connection string in the Trendact/-ai-pipeline repo to point to Neon. No other changes are required on their side:

# DS pipeline — Python
import psycopg2
conn = psycopg2.connect(
    host="ep-xxxxx.us-east-2.aws.neon.tech",
    dbname="neondb", user="netshield_user",
    password="xxxxxx", sslmode="require"
)

No VPN, no IP whitelisting, no file transfers needed. Neon supports connections from any IP by default.

8. Migration Roadmap — Step by Step

9. Cost Summary

Future upgrade triggers

10. Known Limitations & Future Upgrade Path