Files
worldwideview/scripts/migrate-aviation-history.ts
Silvertakana c4207bd6e1 feat(core): migrate to Prisma 7 and unify PostgreSQL architecture (#56)
* feat(cloud): implement Postgres DB adapter, fix static build crashes, and containerize NextAuth/Stripe integrations

* test(marketplace): fix mocks and assertions for multi-tenant Postgres architecture

* feat: rsa jwt license key verification and state integration

* feat: add internal workspace validation route for Edge Functions

* refactor: clean up hub routes and finalize db proxy/adapter for multi-tenant cloud deploy

* feat(cloud): finalize multi-tenant Prisma extensions and workspace routing

* fix(db): correct Prisma 7 better-sqlite3 adapter initialization [Patch]

- update better-sqlite3 adapter instantiation to use configuration object ({ url }) required by Prisma 7 instead of raw Database instance [Patch]
- fix PrismaClientConstructorValidationError by generating client with adapter support [Patch]

* fix(auth): support dynamic origins for local proxies and multi-tenant subdomains [Patch]

- prioritize forwarded headers and request host over hardcoded envOrigin in getRequestOrigin to support subdomain routing [Patch]
- allow ALLOWED_DEV_ORIGIN in marketplace install redirect checks to fix local network development login flows [Patch]

* fix(marketplace): enable dynamic CORS reflection for marketplace API [Patch]

- remove explicit origin array, replacing with origin header reflection to allow marketplace hosted on any domain or local IP [Patch]

- remove global wildcard CORS headers from next.config.ts to harden security against broader app injection [Patch]

* feat(db): create initial postgresql migration

* refactor(db): update remaining files for postgresql

* feat(db): unify database adapter to PostgreSQL-only [Minor]

- replace SQLite adapter with PostgreSQL PrismaPg [Minor]
- remove better-sqlite3 dependencies [Patch]
- finalize docs and implementation plan [Patch]

* fix: resolve prisma client generation and script execution issues with tsx and type module

* feat(db): port tenant isolation logic to new PostgreSQL adapter

* docs: add rule for transparent and narrated agent actions

* BREAKING CHANGE(core): migrate to PostgreSQL and add auto-migration pipeline [Major]

- unified database adapter to exclusively use PostgreSQL [Major]
- implement auto-migration pipeline to pipe legacy SQLite data to Postgres [Major]
- add sqlite to runner stage to support extraction pipeline [Patch]

* feat(core): migrate to Prisma 7 and unify PostgreSQL architecture [Minor]

- removed legacy Prisma SQLite driver and all Better-Sqlite dependencies [Minor]
- updated schema to utilize purely postgresql providers [Patch]
- modified migration scripts to seamlessly convert local sqlite DBs to PostgreSQL containers using keinos/sqlite3 [Minor]
- updated deployment scripts, Coolify pipelines, and Nginx proxy support [Patch]
- injected Prisma Client Extensions for multi-tenant Row Level Security isolation [Minor]
- stabilized PrismaClient initialization utilizing the official adapter-pg runtime pattern [Patch]
- bumped version to 2.1.0

* fix(core): resolve TypeScript compilation errors for CI [Patch]

- fixed NextResponse.json() 3-arg calls to use 2-arg ResponseInit form [Patch]
- replaced invalid 'developer' TrustTier with 'unverified' in parseWwvManifest and load-unpacked route [Patch]
- removed non-existent pluginManager.hotReload() call in DevModeSubscriber, replaced with disable/reload cycle [Patch]
- updated Stripe API version from '2024-04-10' to '2026-04-22.dahlia' to match installed SDK [Patch]
- excluded local-scripts/ from tsconfig to prevent debug scripts from breaking CI [Patch]

* fix(ci): resolve pnpm version conflict in GitHub Actions [Patch]

- removed explicit version: 9 from pnpm/action-setup@v4 — v4 auto-reads packageManager from package.json [Patch]
- updated build job DATABASE_URL from SQLite to PostgreSQL format [Patch]

* fix(docker): add explicit Prisma generator output path for Docker builds [Patch]

- Prisma 7 changed default output location, causing COPY --from=builder to fail
- added output = '../src/generated/prisma' to generator block for consistency

* fix(prisma): update client import path and resolve CI type errors [Patch]

- updated PrismaClient import in db.ts to point to custom generated output path
- resolved implicit 'any' error in marketplace check-updates route
- verified clean local build with tsc --noEmit

* chore(deps): sync Prisma versions to 7.6.0 [Patch]
2026-05-09 22:44:30 +12:00

126 lines
3.7 KiB
TypeScript

/**
* @deprecated This script uses better-sqlite3 directly to migrate data
* from Supabase into the legacy data engine's SQLite database.
* It is NOT part of the main application's database layer.
* It will be removed once the data engine migration is complete.
*/
import { createClient } from "@supabase/supabase-js";
import Database from "better-sqlite3";
import path from "path";
import fs from "fs";
import dotenv from "dotenv";
// Load environment from .env.local
const envPath = path.resolve(process.cwd(), ".env.local");
if (fs.existsSync(envPath)) {
dotenv.config({ path: envPath });
} else {
dotenv.config();
}
const SUPABASE_URL = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const SUPABASE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY || process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;
if (!SUPABASE_URL || !SUPABASE_KEY) {
console.error("Missing Supabase credentials in .env.local");
process.exit(1);
}
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
// Connect to SQLite DB (respects DB_PATH, fallback to data engine location)
const defaultDbPath = path.resolve(process.cwd(), "packages/wwv-data-engine/data/engine.db");
const dbPath = process.env.DB_PATH || defaultDbPath;
console.log(`Connecting to SQLite at ${dbPath}...`);
// Ensure DB directory exists
const dbDir = path.dirname(dbPath);
if (!fs.existsSync(dbDir)) {
fs.mkdirSync(dbDir, { recursive: true });
}
const db = new Database(dbPath);
db.pragma('journal_mode = WAL');
// Ensure table exists (mirroring what we'll inject into Data Engine later)
db.exec(`
CREATE TABLE IF NOT EXISTS aviation_history (
icao24 TEXT NOT NULL,
ts INTEGER NOT NULL,
lat REAL,
lon REAL,
alt REAL,
hdg REAL,
spd REAL,
fetched_at INTEGER NOT NULL,
PRIMARY KEY (icao24, ts)
)
`);
const insertHistory = db.prepare(`
INSERT OR IGNORE INTO aviation_history (icao24, ts, lat, lon, alt, hdg, spd, fetched_at)
VALUES (@icao24, @ts, @lat, @lon, @alt, @hdg, @spd, @fetched_at)
`);
async function migrate() {
console.log("Fetching existing aviation_history records from Supabase...");
let offset = 0;
const limit = 10000; // Chunk size
let totalInserted = 0;
while (true) {
const { data, error } = await supabase
.from("aviation_history")
.select("*")
.range(offset, offset + limit - 1);
if (error) {
console.error("Fetch error:", error);
process.exit(1);
}
if (!data || data.length === 0) {
console.log("No more records to fetch.");
break;
}
console.log(`Processing ${data.length} records (Offset: ${offset})...`);
// Batch insert to SQLite
const insertMany = db.transaction((records) => {
for (const r of records) {
// Determine timestamp logic: Postgres is Timestamp so convert to epoch seconds
const tsMs = new Date(r.timestamp).getTime();
insertHistory.run({
icao24: r.icao24,
ts: Math.floor(tsMs / 1000),
lat: r.latitude,
lon: r.longitude,
alt: r.altitude,
hdg: r.heading,
spd: r.speed,
fetched_at: Math.floor(Date.now() / 1000)
});
}
});
try {
insertMany(data);
totalInserted += data.length;
} catch (err) {
console.error("SQLite Insert Error:", err);
}
if (data.length < limit) {
break;
}
offset += limit;
}
console.log(`Migration complete! Successfully injected ~${totalInserted} records into SQLite.`);
}
migrate();