# n8n GitHub / Oracle State Machine Design

## Goal
Build a scheduled n8n workflow that reconciles live GitHub PR / Issue / Comment data with Oracle-backed workflow tables, then routes work to Ethan, Luther, or implementers based on deterministic state.

This design is intentionally **schedule-first**:
- the cron job is the source of truth
- GitHub webhooks are recommended later, but not required for the first version
- webhook intake can accelerate updates, while the scheduled run remains the reconciliation safety net

## Operating Model
The workflow acts like a state machine with three responsibilities:
1. **Discover** new or changed GitHub objects
2. **Reconcile** GitHub reality against Oracle state
3. **Act** on safe transitions and notify Ethan for anything that needs judgment

The workflow should not depend on semantic memory for its state. Use Oracle tables for deterministic tracking.

## Recommended Execution Cadence
- Run every **20 minutes** or **30 minutes** via n8n Cron Trigger
- If you want a tighter review loop, use 20 minutes
- If you want lower API load, use 30 minutes

## Suggested State Model
Use a small finite state machine.

### Core states
- `NEW`
- `NEEDS_TRIAGE`
- `WAITING_ON_LUTHER`
- `WAITING_ON_IMPLEMENTER`
- `WAITING_ON_CODEx_REVIEW`
- `CHANGES_REQUESTED`
- `READY_TO_MERGE`
- `MERGED`
- `CLOSED`

### Waiting-on markers
- `ethan`
- `luther`
- `implementer`
- `codex`
- `user_approval`

## Reconciliation Rules
The reconciler should compare GitHub timestamps and statuses against Oracle state.

### 1) New issue / spec sheet discovered
If a new GitHub issue appears, or an issue body looks like a spec sheet / large project brief:
- notify Ethan immediately
- store / update Oracle state as `NEEDS_TRIAGE`
- set `waiting_on = ethan`
- if the item is complex, ambiguous, or cross-cutting, Ethan routes it to Luther first
- otherwise Ethan can assign directly to the right implementer

### 2) PR has a new commit, but no newer review
If the latest PR event is a code commit and the latest review timestamp is older than the commit timestamp:
- set `waiting_on = codex`
- if `now - latest_commit_at > 5 minutes`, comment `@codex review`
- notify Ethan that the PR is waiting on review

### 3) PR has a review after the latest commit
If the latest review is newer than the latest commit:
- if the review is `CHANGES_REQUESTED`, set `state = CHANGES_REQUESTED` and `waiting_on = implementer`
- if the review is `APPROVED`, set `state = READY_TO_MERGE`
- notify Ethan with the review result

### 4) Merge decision
Do **not** let n8n blindly auto-merge every approved PR.

Recommended rule:
- n8n should always inform Ethan about the review result
- Ethan decides whether the merge is safe to perform automatically or whether your approval is needed
- in most cases, Ethan can merge and push the project forward
- if a repo / PR policy says the change is milestone-sensitive or user-facing, Ethan escalates for your review

### 5) After merge
If Ethan confirms merge is allowed:
- merge the PR
- delete the source branch if allowed
- close the PR record in Oracle
- mark the workflow item as `MERGED`

## Node-by-Node n8n Design

### Workflow A — Scheduled Reconciler
1. **Cron Trigger**
   - every 20–30 minutes

2. **Oracle: Read Active Repos**
   - query repo registry table
   - filter `active = 'Y'`

3. **Split In Batches**
   - loop through one repo at a time

4. **GitHub: List Open Issues / PRs**
   - get current live GitHub data
   - include commits, reviews, comments, labels, draft flag, mergeable state

5. **Oracle: Load Existing Work Item State**
   - fetch row by repo + type + number

6. **Function / Code Node: Reconcile State**
   - compare GitHub timestamps
   - determine latest event type
   - compute waiting state
   - detect stale PRs waiting on review
   - detect new issue/spec items needing Ethan
   - detect items that should go to Luther first

7. **Switch Node: Route by Decision**
   - new issue/spec
   - waiting on codex
   - changes requested
   - ready to merge
   - closed / merged

8. **Action Node(s)**
   - comment `@codex review`
   - notify Ethan
   - mark item for Luther triage
   - prepare merge candidate
   - upsert repo rows from GitHub discovery
   - toggle repo active/inactive when the dashboard or operator changes it
   - update Oracle state row

9. **Oracle: Write Event / State Update**
   - persist the new state and timestamps

### Repo sync behavior
- `last_seen_at` is written by the n8n sync node when a repo successfully returns from GitHub.
- `updated_at` is written by the same n8n node whenever the repo row changes.
- The dashboard should call a backend/n8n action to refresh repos instead of writing directly to Oracle from the browser.
- The refresh workflow should upsert discovered repos and leave inactive repos in place rather than deleting them.

### Workflow B — GitHub Webhook Intake (Optional but Recommended)
This workflow is not required for the first version, but it is useful later.

Events to subscribe to:
- `issues`
- `issue_comment`
- `pull_request`
- `pull_request_review`
- `push`

Webhook intake should:
- validate GitHub signature
- upsert the related Oracle state row
- enqueue or trigger the same reconciliation logic
- notify Ethan on meaningful transitions

### Workflow C — Notification / Action Executor
Keep GitHub-side actions in a narrow workflow when possible:
- post review request comment
- post summary comment to Ethan
- merge PR
- delete branch
- close PR / issue

This keeps the reconciler simpler and reduces accidental side effects.

## Triage Logic for Ethan and Luther

### Ethan first
Ethan should be informed when:
- a new issue or spec sheet is discovered
- a PR changes state to waiting on review
- a review result arrives
- a PR is ready to merge
- a change looks large enough to need user approval

### Luther first
Route to Luther when the issue/spec is:
- broad or ambiguous
- architecture-heavy
- likely to need decomposition into smaller tasks
- too large for a single implementer task

### Implementer direct assignment
Route directly to an implementer when the task is:
- scoped
- unambiguous
- low-risk
- easy to verify

## Suggested Oracle Tables
Use these as workflow tables, separate from the semantic memory tables.

### 1) GitHub repositories registry
Tracks active repos that the reconciler should scan.

### 2) GitHub work item state
Tracks one row per issue / PR and the machine state.

### 3) GitHub event log
Optional but recommended for auditability and debugging.

## Oracle SQL DDL

```sql
-- 1) Repository registry
CREATE TABLE github_repos (
  repo_id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repo_full_name     VARCHAR2(255) NOT NULL,
  provider           VARCHAR2(30) DEFAULT 'github' NOT NULL,
  active             CHAR(1) DEFAULT 'Y' NOT NULL,
  default_branch     VARCHAR2(128),
  webhook_enabled    CHAR(1) DEFAULT 'N' NOT NULL,
  last_seen_at       TIMESTAMP WITH TIME ZONE,
  notes              CLOB,
  created_at         TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  updated_at         TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT github_repos_active_chk CHECK (active IN ('Y', 'N')),
  CONSTRAINT github_repos_webhook_chk CHECK (webhook_enabled IN ('Y', 'N')),
  CONSTRAINT github_repos_provider_chk CHECK (provider IN ('github')),
  CONSTRAINT github_repos_uk UNIQUE (repo_full_name)
);

CREATE INDEX github_repos_active_idx ON github_repos (active);

-- 2) Work item state table
CREATE TABLE github_work_items (
  work_item_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repo_id               NUMBER NOT NULL,
  github_type           VARCHAR2(10) NOT NULL,
  github_number         NUMBER NOT NULL,
  github_node_id        VARCHAR2(128),
  title                 VARCHAR2(500) NOT NULL,
  html_url              VARCHAR2(1000),
  state                 VARCHAR2(40) NOT NULL,
  waiting_on            VARCHAR2(30),
  latest_commit_sha     VARCHAR2(64),
  latest_commit_at      TIMESTAMP WITH TIME ZONE,
  latest_review_sha     VARCHAR2(64),
  latest_review_at      TIMESTAMP WITH TIME ZONE,
  latest_comment_at     TIMESTAMP WITH TIME ZONE,
  latest_actor_login    VARCHAR2(255),
  is_draft              CHAR(1) DEFAULT 'N' NOT NULL,
  needs_human_approval  CHAR(1) DEFAULT 'N' NOT NULL,
  needs_luther_breakdown CHAR(1) DEFAULT 'N' NOT NULL,
  mergeable_state       VARCHAR2(40),
  closed_at             TIMESTAMP WITH TIME ZONE,
  merged_at             TIMESTAMP WITH TIME ZONE,
  last_action           VARCHAR2(80),
  last_action_at        TIMESTAMP WITH TIME ZONE,
  last_synced_at        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  summary               CLOB,
  created_at            TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  updated_at            TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT github_work_items_repo_fk
    FOREIGN KEY (repo_id) REFERENCES github_repos (repo_id),
  CONSTRAINT github_work_items_type_chk
    CHECK (github_type IN ('PR', 'ISSUE')),
  CONSTRAINT github_work_items_draft_chk
    CHECK (is_draft IN ('Y', 'N')),
  CONSTRAINT github_work_items_human_chk
    CHECK (needs_human_approval IN ('Y', 'N')),
  CONSTRAINT github_work_items_luther_chk
    CHECK (needs_luther_breakdown IN ('Y', 'N')),
  CONSTRAINT github_work_items_uk UNIQUE (repo_id, github_type, github_number)
);

CREATE INDEX github_work_items_state_idx ON github_work_items (state);
CREATE INDEX github_work_items_waiting_idx ON github_work_items (waiting_on);
CREATE INDEX github_work_items_synced_idx ON github_work_items (last_synced_at);
CREATE INDEX github_work_items_repo_idx ON github_work_items (repo_id);

-- 3) Event log for audit/debugging
CREATE TABLE github_work_item_events (
  event_id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  work_item_id        NUMBER NOT NULL,
  event_type          VARCHAR2(50) NOT NULL,
  source              VARCHAR2(20) DEFAULT 'github' NOT NULL,
  github_event_id     VARCHAR2(128),
  actor_login         VARCHAR2(255),
  commit_sha          VARCHAR2(64),
  review_state        VARCHAR2(40),
  payload_json        CLOB,
  created_at          TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT github_work_item_events_fk
    FOREIGN KEY (work_item_id) REFERENCES github_work_items (work_item_id),
  CONSTRAINT github_work_item_events_source_chk
    CHECK (source IN ('github', 'n8n', 'manual'))
);

CREATE INDEX github_work_item_events_item_idx ON github_work_item_events (work_item_id);
CREATE INDEX github_work_item_events_type_idx ON github_work_item_events (event_type);
CREATE INDEX github_work_item_events_created_idx ON github_work_item_events (created_at);
```

## GitHub Webhook Setup Instructions
If you want GitHub push-style notifications later, set up a GitHub webhook that points to n8n.

### GitHub repository settings
In each repository:
1. Go to **Settings → Webhooks → Add webhook**
2. Payload URL: the n8n webhook URL
3. Content type: `application/json`
4. Secret: a shared HMAC secret
5. Choose events:
   - Issues
   - Issue comments
   - Pull requests
   - Pull request reviews
   - Pushes

### n8n webhook node
Use an HTTP Webhook Trigger node that:
- validates the signature
- routes by event type
- upserts state in Oracle
- optionally triggers the reconciler logic

### Important note
Even with webhooks enabled, keep the scheduled cron run. The cron job is the backstop that catches missed events and keeps the state machine consistent.

## Merge Policy Recommendation
A good default policy is:
- n8n may detect readiness and prepare a merge candidate
- Ethan receives every review result
- Ethan decides whether the PR needs your approval
- Ethan may merge directly in most cases
- user approval is reserved for milestone boundaries, risky changes, or work that affects project direction

This matches your staged milestone workflow and preserves autonomy without removing human control where it matters.

## Why This Works
- The cron job sees everything eventually
- Webhooks improve timeliness but are not required
- Oracle tables provide deterministic state
- Ethan stays the human-facing coordinator
- Luther is used when a spec needs decomposition
- Implementers get scoped work instead of large ambiguous tasks

## Next Step
If you want, I can turn this into:
1. an n8n node map with exact node names and conditions, or
2. a fuller Oracle schema with sample seed rows and example reconciliation SQL.