tructor(indexData: RouterIndex) { this.index = indexData; }

resolveCategory(target: string): SkillCategory | undefined { return this.index.categories.find(cat => cat.id.toLowerCase() === target.toLowerCase() || cat.path.includes(target.toLowerCase()) ); } }

**Rationale:** Decoupling the index from the execution layer prevents context bleed. The router only exposes metadata, not full documentation. This keeps the initial payload minimal and ensures version constraints are evaluated before any skill file is loaded.

### Step 2: Define Persona and Task Dispatchers

Routing decisions should align with either the operator’s role or the immediate technical objective. Dispatchers map high-level intents to specific skill categories and enforce the correct starting node.

```typescript
type DispatchIntent = 'APP_DEV' | 'AI_ENGINEER' | 'DBA' | 'MIGRATION' | 'PERFORMANCE_TRIAGE' | 'RAG_SETUP';

interface DispatchRule {
  intent: DispatchIntent;
  primaryCategory: string;
  fallbackCategory?: string;
  requiredSequence?: string[];
}

const DISPATCH_TABLE: DispatchRule[] = [
  { intent: 'APP_DEV', primaryCategory: 'db/frameworks', fallbackCategory: 'db/features' },
  { intent: 'AI_ENGINEER', primaryCategory: 'db/agent', fallbackCategory: 'db/features' },
  { intent: 'DBA', primaryCategory: 'db/performance', fallbackCategory: 'db/security' },
  { intent: 'MIGRATION', primaryCategory: 'db/migrations', fallbackCategory: 'db/devops' },
  { intent: 'PERFORMANCE_TRIAGE', requiredSequence: ['explain-plan', 'wait-events', 'optimizer-stats', 'awr-reports'] },
  { intent: 'RAG_SETUP', requiredSequence: ['ai-profiles', 'vector-search', 'dbms-vector'] }
];

function selectDispatch(intent: DispatchIntent): DispatchRule {
  const rule = DISPATCH_TABLE.find(r => r.intent === intent);
  if (!rule) throw new Error(`Unsupported dispatch intent: ${intent}`);
  return rule;
}

Rationale: Hardcoding dispatch rules eliminates guesswork. When an AI engineer initializes a session, the router immediately points to db/agent for schema discovery and destructive-op guards, rather than allowing the model to wander into generic AI documentation. Required sequences enforce Oracle’s diagnostic pipelines, ensuring that wait-event analysis never precedes plan inspection.

Step 3: Implement the Progressive Discovery Loop

The core routing mechanism loads exactly one skill file, summarizes its constraints, and evaluates whether the next node should be fetched. This loop replaces monolithic prompts with a stateful, token-efficient workflow.

interface RoutingState {
  currentSkill: string | null;
  loadedSkills: string[];
  sequenceIndex: number;
  decision: 'CONTINUE' | 'STOP' | 'EXECUTE';
}

class ProgressiveDiscoveryEngine {
  private router: OracleSkillRouter;
  private state: RoutingState;

  constructor(router: OracleSkillRouter) {
    this.router = router;
    this.state = { currentSkill: null, loadedSkills: [], sequenceIndex: 0, decision: 'STOP' };
  }

  async loadNextSkill(sequence: string[]): Promise<string> {
    if (this.state.sequenceIndex >= sequence.length) {
      this.state.decision = 'STOP';
      return 'Sequence complete. No further routing required.';
    }

    const nextSkill = sequence[this.state.sequenceIndex];
    this.state.currentSkill = nextSkill;
    this.state.loadedSkills.push(nextSkill);
    
    // Simulate fetching and parsing the skill file
    const summary = await this.fetchSkillSummary(nextSkill);
    this.state.sequenceIndex++;
    
    return summary;
  }

  private async fetchSkillSummary(skillId: string): Promise<string> {
    // In production, this resolves to a secure file reader or MCP resource fetch
    return `Loaded ${skillId}. Constraints applied. Awaiting next routing decision.`;
  }

  evaluateNextStep(): RoutingState {
    return { ...this.state };
  }
}

Rationale: Progressive discovery prevents context explosion. By loading one file at a time, the assistant maintains a tight token budget and avoids cross-contamination between unrelated skill domains. The evaluateNextStep method enforces a strict boundary: routing never transitions to execution until the sequence explicitly permits it. This aligns with Oracle’s operational philosophy, where diagnostics must complete before remediation begins.

Step 4: Enforce Execution Boundaries

Routing and execution must remain architecturally separate. The router outputs a structured decision payload. Execution engines (such as SQLcl MCP or custom toolchains) consume this payload only after explicit approval.

interface ExecutionBoundary {
  routingComplete: boolean;
  approvedSequence: string[];
  privilegeLevel: 'READ_ONLY' | 'DML' | 'DDL' | 'ADMIN';
  versionConstraint: string;
}

function validateExecutionBoundary(state: RoutingState, targetEnv: string): ExecutionBoundary {
  const is26ai = targetEnv.includes('26ai') || targetEnv.includes('autonomous');
  
  return {
    routingComplete: state.decision === 'STOP' || state.decision === 'EXECUTE',
    approvedSequence: state.loadedSkills,
    privilegeLevel: state.loadedSkills.includes('destructive-op-guards') ? 'DML' : 'READ_ONLY',
    versionConstraint: is26ai ? '26ai' : '19c'
  };
}

Rationale: Execution boundaries prevent premature tool invocation. By validating routing completion, privilege levels, and version constraints before allowing SQL execution, teams eliminate a major class of AI-induced incidents. The router acts as a gatekeeper, ensuring that agent-safe schema changes, RAG pipelines, and performance diagnostics follow Oracle’s prescribed sequence before any command reaches the database.

Pitfall Guide

1. Version Assumption Drift

Explanation: Assuming that AI-native features like DBMS_VECTOR, Select AI, or AI Profiles are available in 19c environments. These capabilities are documented for 26ai and Autonomous Database, and backporting them to older releases causes runtime failures. Fix: Implement explicit version gating in the router. Cross-reference each skill file’s versionGate metadata against the target environment before loading. Reject sequences that require 26ai features when connected to 19c or 21c instances.

2. Sequence Skipping

Explanation: Jumping directly to AWR reports or optimizer statistics without first inspecting the executed plan. Oracle’s diagnostic pipeline is intentionally ordered; skipping steps masks root causes like cardinality misestimates or adaptive parallelism decisions. Fix: Enforce sequence arrays in the dispatcher. The router must validate that explain-plan completes before allowing wait-events or optimizer-stats to load. Use state flags to block out-of-order skill resolution.

3. Context Bleed Across Domains

Explanation: Mixing framework documentation (Spring, Django, SQLAlchemy) with core database skills in the same prompt. This causes the model to apply generic connection pooling or type mapping strategies that conflict with Oracle’s JDBC/ODP.NET best practices. Fix: Isolate category resolution. The router should load db/frameworks first, establish connection patterns, and only then permit db/features or db/performance skills. Clear category boundaries prevent dialect leakage.

4. Execution Prematurely

Explanation: Attempting to run SQL or invoke MCP tools before the routing loop completes. This bypasses Oracle’s guardrails for destructive operations, idempotency checks, and privilege validation. Fix: Maintain a strict routingComplete flag. Execution engines must reject any command payload that lacks a validated routing sequence. Implement a two-phase commit: route → approve → execute.

5. Idempotency Neglect

Explanation: Retrying schema migrations or DML operations without idempotency patterns. AI assistants frequently generate identical statements on retry, causing duplicate constraint violations or data corruption. Fix: Load idempotency-patterns and destructive-op-guards before any migration sequence. Enforce IF NOT EXISTS, MERGE statements, and transactional checkpoints in generated SQL. The router should flag non-idempotent sequences for manual review.

6. Static Prompt Hardcoding

Explanation: Bypassing the dynamic router by embedding fixed skill paths or sequences directly into system prompts. This breaks version alignment and prevents progressive discovery from adapting to new repository updates. Fix: Treat db/SKILL.md as the single source of truth. All routing decisions must flow through the index parser. Update dispatch tables dynamically when the repository publishes new skill categories or sequence modifications.

7. Privilege Blindness

Explanation: Configuring AI agents or MCP servers without enforcing least-privilege access. Granting DBA or RESOURCE roles to AI workflows violates Oracle security baselines and exposes production schemas to unintended modifications. Fix: Route through db/security before initializing execution tools. Load privilege-management skills that define role hierarchies, audit policies, and encryption requirements. The router should downgrade execution privileges to READ_ONLY until explicit DML/DDL authorization is granted.

Production Bundle

Action Checklist

• Initialize router index: Parse db/SKILL.md and extract category metadata, entry points, and version constraints.
• Configure dispatch table: Map personas and tasks to primary/fallback categories and required sequences.
• Implement progressive loader: Enforce single-file resolution with state tracking and sequence indexing.
• Define execution boundary: Validate routing completion, privilege level, and version alignment before tool invocation.
• Enable version gating: Cross-reference skill metadata against target environment to prevent 19c/26ai drift.
• Enforce idempotency checks: Load destructive-op guards and idempotency patterns before migration or DML sequences.
• Audit routing decisions: Log sequence progression, loaded skills, and boundary validations for compliance review.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Rapid prototyping with Oracle AI features	Progressive routing via db/features	Ensures 26ai/Autonomous version alignment and prevents generic AI pattern leakage	Low (token-efficient, reduces debugging cycles)
Production performance triage	Sequence-enforced routing via db/performance	Guarantees plan inspection before wait-event or AWR analysis, preventing misdiagnosis	Medium (requires licensed AWR/ASH, but eliminates wasted investigation time)
Multi-team schema migration	Agent-safe routing via db/agent + db/migrations	Enforces discovery, idempotency, and privilege validation before DDL execution	High upfront (routing setup), but drastically reduces rollback and data corruption costs
Legacy 19c environment integration	Version-gated routing with fallback to db/frameworks	Prevents 26ai feature requests from failing at runtime, maintains stable JDBC/ODP.NET patterns	Low (avoids licensing upgrades and feature incompatibility incidents)

Configuration Template

# skill-router-config.yaml
router:
  index_source: "db/SKILL.md"
  progressive_mode: true
  max_concurrent_skills: 1
  version_gate: "auto_detect"

dispatch:
  personas:
    app_developer:
      primary: "db/frameworks"
      fallback: "db/features"
    ai_engineer:
      primary: "db/agent"
      fallback: "db/features"
    dba:
      primary: "db/performance"
      fallback: "db/security"
    migration_lead:
      primary: "db/migrations"
      fallback: "db/devops"

sequences:
  rag_pipeline:
    - "ai-profiles"
    - "vector-search"
    - "dbms-vector"
  performance_triage:
    - "explain-plan"
    - "wait-events"
    - "optimizer-stats"
    - "awr-reports"
  schema_change:
    - "schema-discovery"
    - "destructive-op-guards"
    - "idempotency-patterns"
    - "schema-migrations"

execution_boundary:
  require_routing_complete: true
  default_privilege: "READ_ONLY"
  version_validation: "strict"
  audit_logging: true

Quick Start Guide

1. Clone the repository: Access the Oracle Database Skills repository and locate db/SKILL.md. Parse the index to extract category paths, entry points, and version constraints.
2. Initialize the router: Load the dispatch table and sequence definitions into your routing controller. Configure progressive mode to enforce single-file resolution.
3. Define your intent: Select a persona or task dispatch rule. The router will resolve the primary category and return the first skill file in the sequence.
4. Run the discovery loop: Load the skill, evaluate constraints, and advance the sequence index. Repeat until the router signals STOP or EXECUTE.
5. Validate and execute: Confirm routing completion, privilege level, and version alignment. Pass the approved sequence to your execution engine (SQLcl MCP, custom toolchain, or CI/CD pipeline).