# n8n GitHub / Oracle Node Map

This document converts the scheduled GitHub / Oracle state-machine design into an implementation-ready n8n workflow map.

## Purpose
- Reconcile GitHub repos, issues, pull requests, reviews, and comments into Oracle tables
- Keep Oracle as the source of truth for workflow state
- Route triage/review outcomes to Ethan, and involve Luther when an issue/spec needs breakdown
- Support dashboard actions for repo refresh, repo activation, and workflow toggles

## Core rules
- **Schedule-first**: the cron workflow is the source of truth for v1; webhooks are optional later
- **Oracle owns state**: `github_repos`, `github_work_items`, `github_work_item_events`
- **Timestamp ownership**:
  - `last_seen_at` = set when n8n successfully sees the repo from GitHub
  - `updated_at` = set whenever the Oracle row changes
  - `last_synced_at` = set when a reconciliation pass completes
- **Routing**:
  - new issues/spec-like work → Ethan
  - complex spec/architecture work → Luther first
  - PRs with stale review after new commits → request `@codex review` and notify Ethan
  - approved PRs → ready for Ethan review/merge decision

## Recommended build order
1. **Oracle connectivity smoke test**: create one Oracle DB node that runs a trivial query and confirms the wallet, client libraries, and credentials work inside n8n.
2. **Repo table read**: query `github_repos` and display rows in the preview page / workflow output.
3. **Repo toggle write**: wire the `active` update only after the read path succeeds.
4. **Full reconciliation workflow**: add PR/issue sync and event logging only after the Oracle path is verified end to end.

### Smoke test query
Use a single Oracle node with one of these queries:
```sql
SELECT 1 AS ok FROM dual
```
Or, if you want to validate the target table immediately:
```sql
SELECT repo_id, repo_full_name, active, last_seen_at, updated_at
FROM github_repos
FETCH FIRST 5 ROWS ONLY
```

If the smoke test fails, stop there and fix the n8n Oracle node / container image before building the larger workflow JSON.

---

# Workflow A — Scheduled reconciler

## 1. `Cron Trigger - Reconcile Every 20 Minutes`
**Type:** Cron
**Schedule:** `*/20 * * * *`

Purpose: start the scheduled reconciliation pass.

## 2. `Oracle - Read Active Repos`
**Type:** Oracle query

```sql
SELECT repo_id, repo_full_name, default_branch, webhook_enabled
FROM github_repos
WHERE active = 'Y'
ORDER BY updated_at DESC
```

## 3. `IF - Active Repo List Not Empty`
**Condition:**
```text
{{ $json.length > 0 }}
```

## 4. `Split In Batches - Repo Loop`
**Batch size:** `1`

## 5. `Set - Repo Context`
**Expressions:**
```text
owner = {{ $json.repo_full_name.split('/')[0] }}
repo = {{ $json.repo_full_name.split('/')[1] }}
```

## 6. `HTTP Request - GitHub Get Repo`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}
```

**Headers:**
```text
Authorization: token {{$env.GITHUB_TOKEN}}
Accept: application/vnd.github+json
X-GitHub-Api-Version: 2022-11-28
```

## 7. `Oracle - Upsert Repo`
**Type:** MERGE

```sql
MERGE INTO github_repos r
USING (
  SELECT
    :repo_full_name AS repo_full_name,
    :default_branch AS default_branch,
    :webhook_enabled AS webhook_enabled
  FROM dual
) s
ON (r.repo_full_name = s.repo_full_name)
WHEN MATCHED THEN UPDATE SET
  r.default_branch = s.default_branch,
  r.webhook_enabled = s.webhook_enabled,
  r.last_seen_at = SYSTIMESTAMP,
  r.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT (
  repo_full_name, provider, active, default_branch, webhook_enabled,
  last_seen_at, created_at, updated_at
) VALUES (
  :repo_full_name, 'github', 'Y', :default_branch, :webhook_enabled,
  SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP
)
```

## 8. `HTTP Request - GitHub List Open PRs`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/pulls?state=open&per_page=100
```

## 9. `HTTP Request - GitHub List Open Issues`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/issues?state=open&per_page=100
```

Ignore items that contain `pull_request` when treating issue-only rows.

## 10. `Code - Normalize Work Items`
Output one canonical item per PR or issue with fields like:
- `github_type`
- `github_number`
- `title`
- `body`
- `html_url`
- `state`
- `is_draft`
- `labels`
- `latest_commit_at`
- `latest_commit_sha`
- `latest_review_at`
- `latest_review_sha`
- `latest_review_state`
- `latest_comment_at`
- `latest_actor_login`
- `mergeable_state`
- `repo_id`
- `repo_full_name`

## 11. `Split In Batches - Work Item Loop`
**Batch size:** `1`

## 12. `Oracle - Load Existing Work Item`
**SQL:**
```sql
SELECT
  work_item_id, state, waiting_on,
  latest_commit_at, latest_review_at, latest_comment_at,
  latest_commit_sha, latest_review_sha, mergeable_state,
  is_draft, needs_human_approval, needs_luther_breakdown,
  last_action, last_action_at
FROM github_work_items
WHERE repo_id = :repo_id
  AND github_type = :github_type
  AND github_number = :github_number
```

## 13. `IF - Is PR`
**Condition:**
```text
{{ $json.github_type === 'PR' }}
```

---

# PR branch

## 14. `HTTP Request - GitHub List PR Commits`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/pulls/{{$json.github_number}}/commits?per_page=100
```

## 15. `HTTP Request - GitHub List PR Reviews`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/pulls/{{$json.github_number}}/reviews?per_page=100
```

## 16. `HTTP Request - GitHub List PR Comments`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/issues/{{$json.github_number}}/comments?per_page=100
```

## 17. `Code - Compute PR State`
Compute:
- latest commit timestamp
- latest review timestamp
- latest decisive review state
- whether the review is stale after a new commit
- whether the PR is overdue for review

Suggested rules:
- stale review after new commit → `WAITING_ON_CODEX_REVIEW`, `waiting_on = 'codex'`
- `CHANGES_REQUESTED` → `CHANGES_REQUESTED`, `waiting_on = 'implementer'`
- `APPROVED` → `READY_TO_MERGE`, `waiting_on = 'ethan'`
- closed/merged → terminal state

## 18. `Switch - PR Decision`
Cases:
1. overdue review
2. changes requested
3. approved
4. closed / merged
5. default = persist only

## 19. `HTTP Request - Post @codex review`
**Method:** `POST`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/issues/{{$json.github_number}}/comments
```

**Body:**
```json
{"body":"@codex review"}
```

## 20. `HTTP Request - Notify Ethan`
Send an internal notification payload such as:

```json
{
  "repo": "{{$json.repo_full_name}}",
  "type": "PR",
  "number": "{{$json.github_number}}",
  "state": "{{$json.state}}",
  "waiting_on": "{{$json.waiting_on}}",
  "url": "{{$json.html_url}}",
  "summary": "{{$json.title}}"
}
```

## 21. `IF - Auto Merge Allowed`
**Condition:**
```text
{{ $json.state === 'READY_TO_MERGE' && $env.ALLOW_AUTO_MERGE === 'true' }}
```

## 22. `HTTP Request - Merge PR`
**Method:** `PUT`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/pulls/{{$json.github_number}}/merge
```

**Body:**
```json
{"merge_method":"squash"}
```

## 23. `HTTP Request - Delete Source Branch`
**Method:** `DELETE`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}/git/refs/heads/{{$json.head_branch}}
```

Only run if branch deletion is allowed.

## 24. `Oracle - Upsert Work Item`
Use a `MERGE` into `github_work_items` to store:
- title
- URL
- state
- waiting_on
- latest commit/review/comment timestamps and SHAs
- mergeable state
- draft/approval flags
- `last_action`
- `last_synced_at`
- `updated_at`

## 25. `Oracle - Insert Event`
**SQL:**
```sql
INSERT INTO github_work_item_events (
  work_item_id, event_type, source, github_event_id,
  actor_login, commit_sha, review_state, payload_json
) VALUES (
  :work_item_id, :event_type, 'n8n', :github_event_id,
  :actor_login, :commit_sha, :review_state, :payload_json
)
```

Useful event types:
- `repo_refreshed`
- `issue_triaged`
- `pr_waiting_on_codex`
- `review_changes_requested`
- `review_approved`
- `pr_ready_to_merge`
- `pr_merged`
- `pr_closed`

---

# Issue branch

## 14b. `Code - Classify Issue`
Classify spec-like or complex issues using heuristics such as:
- title/body contains `spec`, `spec sheet`, `proposal`, `architecture`, `design`, `RFC`, `roadmap`, `epic`, `brief`
- body length > 2000
- labels include `architecture`, `design`, `spec`, or `epic`

Set:
- `state = NEEDS_TRIAGE`
- `waiting_on = ethan`
- `needs_luther_breakdown = Y` if complex, else `N`

## 15b. `Switch - Issue Decision`
Cases:
1. complex/spec-like issue
2. normal issue

## 16b. `HTTP Request - Notify Ethan`
Use the same notification endpoint as PRs, but mark the payload as `type: ISSUE` and include whether Luther breakdown is needed.

## 17b. `Oracle - Upsert Work Item`
Same `MERGE` pattern as the PR path.

## 18b. `Oracle - Insert Event`
Append the triage event into `github_work_item_events`.

---

# Workflow B — Optional GitHub webhook intake

This workflow is optional for v1 and can be added later.

## 1. `Webhook Trigger - GitHub Events`
Supported events:
- `issues`
- `issue_comment`
- `pull_request`
- `pull_request_review`
- `push`

## 2. `Function - Verify Signature`
Reject the webhook if the HMAC signature does not match the shared secret.

## 3. `Switch - Event Type`
Route to issue, PR, comment, or push normalization.

## 4. `Code - Normalize Webhook Payload`
Convert the webhook body into the same canonical work-item format used by the scheduled workflow.

## 5. `Oracle - Upsert Work Item`
Use the same `MERGE` logic as the scheduled reconciler.

## 6. `Oracle - Insert Event`
Persist the received event and state transition.

## 7. `Execute Workflow - Reconcile Item`
Reuse Workflow A state logic for the affected work item.

---

# Workflow C — Repo refresh and toggle actions

These controls are intended for the dashboard’s repo management section and its action buttons.

## Repo management row controls
Each repo row should expose:
- **Active toggle** — turns a repo on/off for the scheduled reconciler
- **Workflow toggle** — enables or disables webhook intake for that repo
- **Refresh button** — forces an immediate GitHub sync for that repo
- **Last seen / updated timestamps** — show when n8n last observed and last changed the row

### Active toggle behavior
The active toggle is the primary switch the user asked for.

- **Active = on** means the repo remains in the scheduled reconciliation loop
- **Active = off** means the repo is excluded from Workflow A until re-enabled
- The control should display the current state as **Active** or **Inactive** rather than a generic checkbox
- The browser should only submit the user intent; n8n should own the actual `active` update in Oracle
- `last_seen_at` and `updated_at` continue to be written by n8n, not by the browser

Suggested payload for the control:
```json
{
  "action": "toggle_active",
  "repo_id": 123,
  "next_active": "N"
}
```

## 1. `Webhook Trigger - Repo Action`
Example payload:
```json
{
  "action": "refresh_repo|refresh_all|toggle_active|toggle_workflow",
  "repo_id": 123
}
```

## 2. `Switch - Repo Action`
Cases:
- `refresh_repo`
- `refresh_all`
- `toggle_active`
- `toggle_workflow`

## 3. `HTTP Request - GitHub Refresh Repo`
**Method:** `GET`

**URL:**
```text
https://api.github.com/repos/{{$json.owner}}/{{$json.repo}}
```

## 4. `HTTP Request - GitHub Refresh All`
**Method:** `GET`

**URL:**
```text
https://api.github.com/user/repos?per_page=100&sort=updated`
```

Upsert any newly discovered repos into `github_repos`.

## 5. `Oracle - Update Repo Active`
```sql
UPDATE github_repos
SET active = :active,
    updated_at = SYSTIMESTAMP
WHERE repo_id = :repo_id
```

The toggle workflow should map the UI state to Oracle values consistently:
- Active/on → `active = 'Y'`
- Inactive/off → `active = 'N'`

## 6. `Oracle - Update Repo Workflow Flag`
```sql
UPDATE github_repos
SET webhook_enabled = :webhook_enabled,
    updated_at = SYSTIMESTAMP
WHERE repo_id = :repo_id
```

## 7. `Oracle - Insert Event`
Log:
- `repo_refreshed`
- `repo_toggled`
- `repo_activated`
- `repo_deactivated`

---

# Dashboard preview guidance

The dashboard redesign should expose these sections in a wired preview page first:
- live metrics strip
- snapshot banner
- activity feed
- agent activity table
- repo management section with active/inactive toggles
- refresh/toggle actions
- stale-data indicators
- optional detail drawer/modal

Keep the preview page live for review. If it looks good, replace the index page with the preview page later.

## Summary
This node map is the concrete implementation form of the schedule-first Oracle-backed state machine. It keeps repo state, work item state, and event history in Oracle while leaving token accounting on the existing path.