SQLite-to-Oracle Planner

Role

This is a planning-only skill. Search, read minimally, and produce the manifest. Do NOT implement changes. Use Glob and Grep — never read entire files to scan them.

Scan Strategy

Work in five passes. Log every hit with file path and line number.

Pass 1 — Dependency Imports

Pattern: better-sqlite3 | sqlite3 | drizzle-orm/sqlite-core | typeorm.*sqlite | @agent-state
Files:   **/*.ts, **/*.js, **/*.mjs, **/*.cjs

Import Found	Oracle Equivalent
better-sqlite3	oracledb or project Oracle connection pool
sqlite3	oracledb
drizzle-orm/sqlite-core	Raw OCI SQL or drizzle-orm with Oracle adapter
typeorm + sqlite driver	typeorm with Oracle driver
@agent-state (SQLite-backed)	Oracle-backed state store

Pass 2 — SQLite Data Files

Glob: **/*.db, **/*.sqlite, **/*.sqlite3

These are data files — flag each one. They require data migration, not just code changes.

Pass 3 — Connection Strings and File-Based DB Paths

Pattern: sqlite: | :memory: | \.db['")] | new Database( | createConnection(
Files:   **/*.ts, **/*.js, **/*.env, **/*.env.*

SQLite Pattern	Oracle Equivalent
sqlite:./path/to/db.sqlite	OCI connection string / TNS alias
:memory:	Oracle SGA (in-memory buffering is automatic)
new Database('file.db')	oracledb.getConnection(config)
createConnection({ type: 'sqlite', database: '...' })	createConnection({ type: 'oracle', ... })

Pass 4 — Schema Syntax

Pattern: AUTOINCREMENT | INTEGER PRIMARY KEY | TEXT DEFAULT | BLOB | REAL | NUMERIC
Files:   **/*.sql, **/*.ts, **/*.js

SQLite Type	Oracle Equivalent
INTEGER PRIMARY KEY AUTOINCREMENT	NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
TEXT (< 4000 chars)	VARCHAR2(4000)
TEXT (unbounded / large)	CLOB
BLOB	BLOB
REAL	BINARY_FLOAT or NUMBER
NUMERIC	NUMBER
BOOLEAN (stored 0/1)	NUMBER(1) with CHECK (col IN (0,1))
DATETIME (stored as text)	TIMESTAMP WITH TIME ZONE
AUTOINCREMENT sequence in DDL	CREATE SEQUENCE + trigger, or GENERATED AS IDENTITY

Pass 5 — Fallback Logic (Most Commonly Missed)

Pattern: sqlite | SQLite | in.memory | fallback.*db | db.*fallback (case-insensitive)
Files:   **/*.ts, **/*.js

Conditional branches that fall back to SQLite when Oracle is unavailable must be removed entirely — not just replaced.

// Examples to flag:
if (process.env.DATABASE_URL?.includes('sqlite')) { ... }    // [SQLITE_REPLACE]
const db = hasOracle ? oracleDB : sqliteDB;                  // [SQLITE_REPLACE]
const adapter = isProd ? 'oracle' : 'sqlite';                // [SQLITE_REPLACE]

Migration Manifest Format

Output this after all five passes. This is the deliverable for implementation agents.

## SQLite Migration Manifest

| File                       | Line | Pattern Found                           | Marker           | Oracle Equivalent                                        |
|----------------------------|------|-----------------------------------------|------------------|----------------------------------------------------------|
| apps/api/src/db/client.ts  | 3    | `import Database from 'better-sqlite3'` | [SQLITE_REPLACE] | `import oracledb from 'oracledb'`                        |
| apps/api/src/db/schema.ts  | 12   | `id INTEGER PRIMARY KEY AUTOINCREMENT`  | [SQLITE_REPLACE] | `id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY` |
| apps/api/data/state.sqlite | —    | SQLite data file                        | [SQLITE_REPLACE] | Migrate data to Oracle table via INSERT SELECT           |
| apps/api/src/config.ts     | 45   | `sqlite:./data/portal.db`               | [SQLITE_REPLACE] | OCI connection string                                    |
| apps/api/src/app.ts        | 88   | `const db = isProd ? oracle : sqlite`   | [SQLITE_REPLACE] | Remove fallback; Oracle only                             |

**Total touch points: N**
**Estimated implementation tasks: N**
**Data files requiring migration: N**

Each [SQLITE_REPLACE] marker = one atomic implementation unit for Agent 2+.

Common Mistakes

Mistake	Fix
Reading entire files to search	Use Grep with -n. Never cat a file to scan it.
Skipping Pass 5 (fallback logic)	Fallback branches are the trickiest to remove — always run it.
Assuming TEXT → VARCHAR2 universally	Check actual usage. Content > 4000 chars needs CLOB.
Ignoring .db data files	Flag them — they require data migration planning, not just code changes.
Missing drizzle-orm/sqlite-core schema files	Drizzle's Oracle support differs; all schema files need flagging.
Flagging Oracle-side code as SQLite	Filter false positives — sqlite may appear in migration docs or comments.