import { useState, useMemo, useRef, useEffect, useCallback } from "react"; import { useSQLQuery } from "@motherduck/react-sql-query"; import * as d3 from "d3"; import { Database as DbIcon, Table2, Eye, ZoomIn, ZoomOut, Maximize2, Link2, Key, Loader2, GripVertical, X, ChevronRight, Search, FolderTree, } from "lucide-react"; import { BarChart, Bar, AreaChart, Area, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer, } from "recharts"; const N = (v: unknown): number => (v != null ? Number(v) : 0); const TABLE_WIDTH = 240; const COL_HEIGHT = 22; const TABLE_HEADER_H = 32; const COL_GUTTER = 36; const ROW_GUTTER = 32; const SCHEMA_GUTTER = 80; const SCHEMA_TOP_PAD = 32; const SCHEMA_LABEL_PAD = 22; const PANEL_WIDTH = 340; const EXPLORER_DEFAULT_WIDTH = 260; const EXPLORER_MIN_WIDTH = 200; const EXPLORER_MAX_WIDTH = 520; const NUM_BUCKETS = 20; type ColRow = { schema_name: string; table_name: string; column_name: string; data_type: string; column_index: number; is_nullable: boolean; kind: string; }; type Col = { name: string; type: string; nullable: boolean }; type TableInfo = { schema: string; name: string; kind: "TABLE" | "VIEW"; columns: Col[]; pk: string | null; fkMap: Map; }; type LinkKind = "strong" | "weak"; type FKLink = { fromKey: string; fromCol: string; toKey: string; toCol: string; kind: LinkKind }; type Pos = { x: number; y: number }; type Viz = "numeric" | "timestamp" | "categorical" | null; const norm = (s: string) => s.toLowerCase().replace(/_/g, ""); function singular(s: string): string { if (s.length > 3 && s.endsWith("ies")) return s.slice(0, -3) + "y"; if (s.length > 3 && s.endsWith("ses")) return s.slice(0, -2); if (s.length > 1 && s.endsWith("s")) return s.slice(0, -1); return s; } function plural(s: string): string { if (s.endsWith("y")) return s.slice(0, -1) + "ies"; if (s.endsWith("s")) return s + "es"; return s + "s"; } function stripIdSuffix(name: string): string { const m = name.match(/^(.+?)[_]?(?:[iI][dD])$/); if (!m) return ""; return m[1].replace(/_+$/, "").toLowerCase(); } function shortType(t: string): string { const u = (t || "").toUpperCase(); if (u.startsWith("VARCHAR") || u === "TEXT" || u === "STRING") return "str"; if (u === "BIGINT" || u === "INTEGER" || u === "INT" || u === "SMALLINT" || u === "HUGEINT" || u === "TINYINT" || u.startsWith("UINT")) return "int"; if (u === "DOUBLE" || u === "FLOAT" || u === "REAL" || u.startsWith("DECIMAL") || u.startsWith("NUMERIC")) return "num"; if (u === "BOOLEAN" || u === "BOOL") return "bool"; if (u.startsWith("TIMESTAMP")) return "ts"; if (u === "DATE") return "date"; if (u === "TIME") return "time"; if (u === "JSON") return "json"; if (u === "UUID") return "uuid"; if (u === "BLOB" || u === "BYTEA") return "blob"; if (u.includes("[]") || u.startsWith("LIST")) return "list"; if (u.startsWith("STRUCT")) return "struct"; if (u.startsWith("MAP")) return "map"; return u.toLowerCase().slice(0, 8); } function isNumericType(t: string): boolean { const u = (t || "").toUpperCase(); return ( u === "BIGINT" || u === "INTEGER" || u === "INT" || u === "SMALLINT" || u === "HUGEINT" || u === "TINYINT" || u.startsWith("UINT") || u === "DOUBLE" || u === "FLOAT" || u === "REAL" || u.startsWith("DECIMAL") || u.startsWith("NUMERIC") ); } function isTemporalType(t: string): boolean { const u = (t || "").toUpperCase(); return u.startsWith("TIMESTAMP") || u === "DATE"; } function isCategoricalType(t: string): boolean { const u = (t || "").toUpperCase(); return ( u.startsWith("VARCHAR") || u === "TEXT" || u === "STRING" || u === "BOOLEAN" || u === "BOOL" || u === "UUID" ); } function vizForType(t: string): Viz { if (isNumericType(t)) return "numeric"; if (isTemporalType(t)) return "timestamp"; if (isCategoricalType(t)) return "categorical"; return null; } const GENERIC_COL_NAMES = new Set([ "id", "name", "title", "label", "description", "comment", "comments", "notes", "type", "status", "kind", "category", "categories", "code", "value", "key", "createdat", "updatedat", "deletedat", "modifiedat", "insertedat", "createddate", "updateddate", "deleteddate", "timestamp", "ts", "datetime", "date", "time", "count", "total", "sum", "avg", "amount", "price", "cost", "quantity", "url", "uri", "email", "phone", "address", "data", "datasource", "source", "metadata", "version", "ord", "rank", ]); function isGenericName(normalized: string): boolean { return GENERIC_COL_NAMES.has(normalized); } function pickPKFor(table: { name: string; columns: Col[] }, refColName: string): string | null { const refLower = norm(refColName); for (const c of table.columns) if (norm(c.name) === refLower) return c.name; for (const c of table.columns) if (c.name.toLowerCase() === "id") return c.name; const tNorm = norm(table.name); const tSing = singular(tNorm); for (const c of table.columns) { const cNorm = norm(c.name); if (cNorm === tNorm + "id" || cNorm === tSing + "id") return c.name; } return null; } function detectPK(table: { name: string; columns: Col[] }): string | null { const tNorm = norm(table.name); const tSing = singular(tNorm); for (const c of table.columns) { const cNorm = norm(c.name); if (cNorm === tNorm + "id" || cNorm === tSing + "id") return c.name; } for (const c of table.columns) if (c.name.toLowerCase() === "id") return c.name; return null; } const tableHeight = (t: TableInfo) => TABLE_HEADER_H + t.columns.length * COL_HEIGHT; const tableKey = (t: TableInfo | { schema: string; name: string }) => `${t.schema}.${t.name}`; const qid = (s: string) => `"${s.replace(/"/g, '""')}"`; function fmtPct(v: unknown): string { if (v == null) return "—"; const n = Number(v); if (!isFinite(n)) return "—"; if (n === 0) return "0%"; if (n < 0.1) return "<0.1%"; return `${n.toFixed(n < 1 ? 1 : 0)}%`; } function fmtNum(v: unknown): string { if (v == null) return "—"; const n = Number(v); if (!isFinite(n)) return String(v); if (Math.abs(n) >= 1_000_000) return (n / 1_000_000).toFixed(1) + "M"; if (Math.abs(n) >= 1_000) return (n / 1_000).toFixed(1) + "K"; return n.toLocaleString(); } function fmtVal(v: unknown, maxLen = 18): string { if (v == null) return "—"; let s = String(v); if (s.length > maxLen) s = s.slice(0, maxLen - 1) + "…"; return s; } function fmtNumeric(v: unknown): string { if (v == null) return "—"; const n = Number(v); if (!isFinite(n)) return String(v).slice(0, 14); if (Math.abs(n) >= 1_000_000) return (n / 1_000_000).toFixed(2) + "M"; if (Math.abs(n) >= 10_000) return (n / 1_000).toFixed(1) + "K"; if (Math.abs(n) >= 1) return n.toLocaleString(undefined, { maximumFractionDigits: 2 }); return n.toPrecision(3); } function fmtBucketLabel(b: string): string { if (typeof b === "string" && b.length > 10 && b.charAt(4) === "-" && b.charAt(7) === "-") { return b.slice(0, 10); } return String(b); } function fmtCell(v: unknown): string { if (v == null) return "NULL"; if (typeof v === "bigint") return v.toString(); if (typeof v === "boolean") return v ? "true" : "false"; if (typeof v === "number") return String(v); if (typeof v === "string") return v; if (typeof v === "object") { const obj = v as any; try { if (typeof obj.toJson === "function") return JSON.stringify(obj.toJson()); } catch {} try { if (Array.isArray(obj.values)) return JSON.stringify(obj.values); } catch {} try { const s = obj.toString(); if (s !== "[object Object]") return s; } catch {} try { return JSON.stringify(v); } catch { return "[object]"; } } return String(v); } function truncStr(s: string, max: number): string { return s.length > max ? s.slice(0, max - 1) + "…" : s; } export default function DatabaseModeler() { const [selectedDb, setSelectedDb] = useState(""); const [selectedKey, setSelectedKey] = useState(null); const [expandedCol, setExpandedCol] = useState(null); const [selectedSchemas, setSelectedSchemas] = useState>(new Set()); const [dbFilter, setDbFilter] = useState(""); const [explorerWidth, setExplorerWidth] = useState(EXPLORER_DEFAULT_WIDTH); const [isResizingExplorer, setIsResizingExplorer] = useState(false); const startResizeExplorer = useCallback((e: React.PointerEvent) => { if (e.button !== 0) return; e.preventDefault(); e.stopPropagation(); const target = e.currentTarget as HTMLElement; const startX = e.clientX; const startW = explorerWidth; target.setPointerCapture(e.pointerId); setIsResizingExplorer(true); const onMove = (ev: PointerEvent) => { const next = Math.max(EXPLORER_MIN_WIDTH, Math.min(EXPLORER_MAX_WIDTH, startW + (ev.clientX - startX))); setExplorerWidth(next); }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} setIsResizingExplorer(false); }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, [explorerWidth]); const dbsQ = useSQLQuery(` SELECT database_name FROM duckdb_databases() WHERE NOT internal AND database_name NOT IN ('system', 'temp', 'memory', 'md_information_schema') ORDER BY database_name `); const dbs: string[] = useMemo(() => { const rows = Array.isArray(dbsQ.data) ? dbsQ.data : []; return rows.map((r: any) => String(r.database_name)); }, [dbsQ.data]); useEffect(() => { if (!selectedDb && dbs.length > 0) setSelectedDb(dbs[0]); }, [dbs, selectedDb]); useEffect(() => { setSelectedKey(null); setExpandedCol(null); }, [selectedDb]); useEffect(() => { setExpandedCol(null); }, [selectedKey]); const safeDb = selectedDb.replace(/'/g, "''"); const colsQ = useSQLQuery( ` SELECT c.schema_name, c.table_name, c.column_name, c.data_type, c.column_index, c.is_nullable, COALESCE(t.kind, 'TABLE') AS kind FROM duckdb_columns() c LEFT JOIN ( SELECT database_name, schema_name, table_name, 'TABLE' AS kind FROM duckdb_tables() WHERE NOT internal UNION ALL SELECT database_name, schema_name, view_name AS table_name, 'VIEW' AS kind FROM duckdb_views() WHERE NOT internal ) t USING (database_name, schema_name, table_name) WHERE c.database_name = '${safeDb}' AND NOT c.internal AND c.schema_name NOT IN ('information_schema', 'pg_catalog') ORDER BY c.schema_name, c.table_name, c.column_index `, { enabled: !!selectedDb } ); const structure = useMemo(() => { const rows = (Array.isArray(colsQ.data) ? colsQ.data : []) as ColRow[]; const schemaMap = new Map>(); for (const r of rows) { const sch = String(r.schema_name); const tbl = String(r.table_name); const kind: "TABLE" | "VIEW" = String(r.kind) === "VIEW" ? "VIEW" : "TABLE"; let bySchema = schemaMap.get(sch); if (!bySchema) { bySchema = new Map(); schemaMap.set(sch, bySchema); } let t = bySchema.get(tbl); if (!t) { t = { schema: sch, name: tbl, kind, columns: [], pk: null, fkMap: new Map() }; bySchema.set(tbl, t); } t.columns.push({ name: String(r.column_name), type: String(r.data_type), nullable: Boolean(r.is_nullable), }); } const allTables: TableInfo[] = []; schemaMap.forEach(bs => bs.forEach(t => allTables.push(t))); for (const t of allTables) t.pk = detectPK(t); const tableByNorm = new Map(); for (const t of allTables) { const key = norm(t.name); const list = tableByNorm.get(key) || []; list.push(t); tableByNorm.set(key, list); } const linksOut: FKLink[] = []; const linkedPairs = new Set(); const pairKey = (k1: string, k2: string) => k1 < k2 ? `${k1}||${k2}` : `${k2}||${k1}`; for (const t of allTables) { for (const c of t.columns) { if (c.name === t.pk) continue; let target: TableInfo | null = null; const base = stripIdSuffix(c.name); if (base) { const cands = [base, singular(base), plural(base)]; for (const cand of cands) { const matches = tableByNorm.get(cand); if (matches && matches.length > 0) { const found = matches.find(m => m !== t); if (found) { target = found; break; } } } } if (!target) { for (const t2 of allTables) { if (t2 === t) continue; if (t2.pk && norm(t2.pk) === norm(c.name)) { target = t2; break; } } } if (target) { const pkCol = pickPKFor(target, c.name); if (pkCol) { t.fkMap.set(c.name, { toTable: tableKey(target), toCol: pkCol }); const fromK = tableKey(t); const toK = tableKey(target); linksOut.push({ fromKey: fromK, fromCol: c.name, toKey: toK, toCol: pkCol, kind: "strong" }); linkedPairs.add(pairKey(fromK, toK)); } } } } const colByNorm = new Map(); for (const t of allTables) { for (const c of t.columns) { const k = norm(c.name); const list = colByNorm.get(k) || []; list.push({ table: t, col: c }); colByNorm.set(k, list); } } for (const [colKey, occ] of colByNorm) { if (occ.length < 2) continue; if (isGenericName(colKey)) continue; if (occ.length > 6) continue; for (let i = 0; i < occ.length; i++) { for (let j = i + 1; j < occ.length; j++) { const a = occ[i], b = occ[j]; if (a.table === b.table) continue; const ak = tableKey(a.table); const bk = tableKey(b.table); const pk = pairKey(ak, bk); if (linkedPairs.has(pk)) continue; linksOut.push({ fromKey: ak, fromCol: a.col.name, toKey: bk, toCol: b.col.name, kind: "weak" }); linkedPairs.add(pk); } } } const schemaNames = Array.from(schemaMap.keys()).sort((a, b) => { if (a === "main") return -1; if (b === "main") return 1; return a.localeCompare(b); }); const sortedTablesBySchema = new Map(); for (const sch of schemaNames) { const list = Array.from(schemaMap.get(sch)!.values()).sort((a, b) => a.name.localeCompare(b.name)); sortedTablesBySchema.set(sch, list); } const initialPositions = new Map(); let cumX = 0; for (const sch of schemaNames) { const tables = sortedTablesBySchema.get(sch)!; const cols = Math.max(1, Math.min(5, Math.ceil(Math.sqrt(tables.length * 1.6)))); const rows_ = Math.ceil(tables.length / cols); const rowHeights: number[] = []; for (let r = 0; r < rows_; r++) { let maxH = 0; for (let c = 0; c < cols; c++) { const i = r * cols + c; if (i < tables.length) { const h = tableHeight(tables[i]); if (h > maxH) maxH = h; } } rowHeights.push(maxH); } let yCursor = SCHEMA_TOP_PAD; for (let r = 0; r < rows_; r++) { for (let c = 0; c < cols; c++) { const i = r * cols + c; if (i < tables.length) { const t = tables[i]; const x = cumX + c * (TABLE_WIDTH + COL_GUTTER); const y = yCursor; initialPositions.set(tableKey(t), { x, y }); } } yCursor += rowHeights[r] + ROW_GUTTER; } cumX += cols * (TABLE_WIDTH + COL_GUTTER) - COL_GUTTER + SCHEMA_GUTTER; } const tablesByKey = new Map(); for (const t of allTables) tablesByKey.set(tableKey(t), t); return { tablesByKey, tables: allTables, links: linksOut, schemaNames, sortedTablesBySchema, initialPositions, }; }, [colsQ.data]); // Reset selected schemas to all visible whenever the structure changes useEffect(() => { setSelectedSchemas(new Set(structure.schemaNames)); }, [structure]); // Visible subset based on schema selection const visible = useMemo(() => { const tables = structure.tables.filter(t => selectedSchemas.has(t.schema)); const keys = new Set(tables.map(t => tableKey(t))); const links = structure.links.filter(l => keys.has(l.fromKey) && keys.has(l.toKey)); const schemaNames = structure.schemaNames.filter(s => selectedSchemas.has(s)); return { tables, keys, links, schemaNames }; }, [structure, selectedSchemas]); const [positions, setPositions] = useState>(new Map()); const positionsRef = useRef(positions); useEffect(() => { positionsRef.current = positions; }, [positions]); useEffect(() => { setPositions(structure.initialPositions); }, [structure]); const [draggingKey, setDraggingKey] = useState(null); const getPos = useCallback((key: string): Pos => { return positions.get(key) || structure.initialPositions.get(key) || { x: 0, y: 0 }; }, [positions, structure.initialPositions]); const schemaBoxes = useMemo(() => { const out: { name: string; x: number; y: number; width: number; height: number }[] = []; for (const sch of visible.schemaNames) { const tables = (structure.sortedTablesBySchema.get(sch) || []).filter(t => visible.keys.has(tableKey(t))); let minX = Infinity, minY = Infinity, maxX = -Infinity, maxY = -Infinity; for (const t of tables) { const p = getPos(tableKey(t)); const h = tableHeight(t); if (p.x < minX) minX = p.x; if (p.y < minY) minY = p.y; if (p.x + TABLE_WIDTH > maxX) maxX = p.x + TABLE_WIDTH; if (p.y + h > maxY) maxY = p.y + h; } if (!isFinite(minX)) continue; const padX = 18; const padY = 18; const labelPad = SCHEMA_LABEL_PAD; out.push({ name: sch, x: minX - padX, y: minY - padY - labelPad, width: (maxX - minX) + padX * 2, height: (maxY - minY) + padY * 2 + labelPad, }); } return out; }, [visible, structure, getPos]); const canvasBounds = useMemo(() => { let minX = Infinity, minY = Infinity, maxX = 0, maxY = 0; for (const t of visible.tables) { const p = getPos(tableKey(t)); const h = tableHeight(t); if (p.x < minX) minX = p.x; if (p.y < minY) minY = p.y; if (p.x + TABLE_WIDTH > maxX) maxX = p.x + TABLE_WIDTH; if (p.y + h > maxY) maxY = p.y + h; } if (!isFinite(minX)) { minX = 0; minY = 0; } return { minX, minY, maxX, maxY, width: maxX, height: maxY }; }, [visible, getPos]); const wrapperRef = useRef(null); const [transform, setTransform] = useState<{ x: number; y: number; k: number }>({ x: 0, y: 0, k: 1 }); const transformRef = useRef(transform); useEffect(() => { transformRef.current = transform; }, [transform]); const zoomBehaviorRef = useRef | null>(null); useEffect(() => { if (!wrapperRef.current) return; const sel = d3.select(wrapperRef.current); const z = d3.zoom() .scaleExtent([0.15, 3]) .filter((event: any) => { if (event.target && (event.target as HTMLElement).closest && (event.target as HTMLElement).closest("[data-table-card]")) { return false; } if (event.type === "wheel") return true; return event.button === 0; }) .on("zoom", (event: any) => { const t = event.transform; setTransform({ x: t.x, y: t.y, k: t.k }); }); zoomBehaviorRef.current = z; sel.call(z as any); return () => { sel.on(".zoom", null); }; }, []); const fitToView = useCallback(() => { if (!wrapperRef.current || !zoomBehaviorRef.current) return; const w = wrapperRef.current.clientWidth; const h = wrapperRef.current.clientHeight; const cw = canvasBounds.maxX - canvasBounds.minX; const ch = canvasBounds.maxY - canvasBounds.minY; if (cw <= 0 || ch <= 0) return; const k = Math.min(w / (cw + 60), h / (ch + 60), 1); const x = (w - cw * k) / 2 - canvasBounds.minX * k; const y = Math.max(20, (h - ch * k) / 2) - canvasBounds.minY * k; const t = d3.zoomIdentity.translate(x, y).scale(k); d3.select(wrapperRef.current).call(zoomBehaviorRef.current.transform as any, t); }, [canvasBounds]); // Auto-fit on data load, schema change, or explorer resize end useEffect(() => { if (isResizingExplorer) return; if (!colsQ.isLoading && visible.tables.length > 0) { const id = setTimeout(fitToView, 60); return () => clearTimeout(id); } }, [colsQ.isLoading, selectedDb, selectedSchemas, visible.tables.length, isResizingExplorer, explorerWidth]); // Close detail panel if its schema gets hidden useEffect(() => { if (!selectedKey) return; const t = structure.tablesByKey.get(selectedKey); if (t && !selectedSchemas.has(t.schema)) setSelectedKey(null); }, [selectedSchemas, selectedKey, structure]); function zoomBy(factor: number) { if (!wrapperRef.current || !zoomBehaviorRef.current) return; d3.select(wrapperRef.current) .transition() .duration(150) .call(zoomBehaviorRef.current.scaleBy as any, factor); } const startDragOrClick = useCallback((e: React.PointerEvent, key: string) => { if (e.button !== 0) return; e.stopPropagation(); e.preventDefault(); const target = e.currentTarget as HTMLElement; const startClientX = e.clientX; const startClientY = e.clientY; const startPos = positionsRef.current.get(key) || structure.initialPositions.get(key) || { x: 0, y: 0 }; const startK = transformRef.current.k; let isDrag = false; target.setPointerCapture(e.pointerId); const onMove = (ev: PointerEvent) => { const dx = ev.clientX - startClientX; const dy = ev.clientY - startClientY; if (!isDrag && Math.hypot(dx, dy) >= 4) { isDrag = true; setDraggingKey(key); } if (isDrag) { const mdx = (ev.clientX - startClientX) / startK; const mdy = (ev.clientY - startClientY) / startK; setPositions(prev => { const next = new Map(prev); next.set(key, { x: startPos.x + mdx, y: startPos.y + mdy }); return next; }); } }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} if (!isDrag) { setSelectedKey(prev => prev === key ? null : key); } setDraggingKey(null); }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, [structure.initialPositions]); const resetLayout = useCallback(() => { setPositions(new Map(structure.initialPositions)); setTimeout(fitToView, 30); }, [structure.initialPositions, fitToView]); const focusKey = draggingKey ?? selectedKey; const edgePaths = useMemo(() => { const out: { id: string; d: string; dim: boolean; weak: boolean }[] = []; for (let i = 0; i < visible.links.length; i++) { const l = visible.links[i]; const ft = structure.tablesByKey.get(l.fromKey); const tt = structure.tablesByKey.get(l.toKey); if (!ft || !tt) continue; const fp = getPos(l.fromKey); const tp = getPos(l.toKey); const fci = ft.columns.findIndex(c => c.name === l.fromCol); const tci = tt.columns.findIndex(c => c.name === l.toCol); if (fci < 0 || tci < 0) continue; const fy = fp.y + TABLE_HEADER_H + fci * COL_HEIGHT + COL_HEIGHT / 2; const ty = tp.y + TABLE_HEADER_H + tci * COL_HEIGHT + COL_HEIGHT / 2; const fLeftX = fp.x; const fRightX = fp.x + TABLE_WIDTH; const tLeftX = tp.x; const tRightX = tp.x + TABLE_WIDTH; let sx: number, tx: number; if (tRightX < fLeftX) { sx = fLeftX; tx = tRightX; } else if (tLeftX > fRightX) { sx = fRightX; tx = tLeftX; } else { sx = fRightX; tx = tRightX; } const dx = Math.abs(tx - sx); const off = Math.max(40, Math.min(180, dx / 2)); const sxOff = sx + (tx >= sx ? off : -off); const txOff = tx + (sx >= tx ? off : -off); const d = `M ${sx} ${fy} C ${sxOff} ${fy}, ${txOff} ${ty}, ${tx} ${ty}`; const dim = focusKey != null && l.fromKey !== focusKey && l.toKey !== focusKey; out.push({ id: `${i}`, d, dim, weak: l.kind === "weak" }); } return out; }, [visible.links, structure, getPos, focusKey]); const tableCount = visible.tables.length; const fkCount = visible.links.length; const schemaCount = visible.schemaNames.length; const filteredDbs = useMemo(() => { if (!dbFilter) return dbs; const f = dbFilter.toLowerCase(); return dbs.filter(db => db.toLowerCase().includes(f)); }, [dbs, dbFilter]); const selectedTable = selectedKey ? structure.tablesByKey.get(selectedKey) || null : null; const tableRef = selectedTable ? `${qid(selectedDb)}.${qid(selectedTable.schema)}.${qid(selectedTable.name)}` : ""; const rowCountQ = useSQLQuery( selectedTable ? `SELECT COUNT(*) AS n FROM ${tableRef}` : "SELECT 0 AS n", { enabled: !!selectedTable } ); const summarizeQ = useSQLQuery( selectedTable ? `SUMMARIZE ${tableRef}` : "SELECT 0 AS n", { enabled: !!selectedTable } ); const totalRows = useMemo(() => { if (!selectedTable) return null; const rows = Array.isArray(rowCountQ.data) ? rowCountQ.data : []; if (rows.length === 0) return null; return N((rows[0] as any).n); }, [rowCountQ.data, selectedTable]); const summaryByCol = useMemo(() => { const map = new Map(); if (!selectedTable) return map; const rows = Array.isArray(summarizeQ.data) ? summarizeQ.data : []; for (const r of rows as any[]) map.set(String(r.column_name), r); return map; }, [summarizeQ.data, selectedTable]); const relsForSelected = useMemo(() => { if (!selectedTable) return { outgoing: [] as FKLink[], incoming: [] as FKLink[], shared: [] as FKLink[] }; const k = tableKey(selectedTable); const outgoing: FKLink[] = []; const incoming: FKLink[] = []; const shared: FKLink[] = []; for (const l of structure.links) { if (l.kind === "weak") { if (l.fromKey === k || l.toKey === k) shared.push(l); } else { if (l.fromKey === k) outgoing.push(l); else if (l.toKey === k) incoming.push(l); } } return { outgoing, incoming, shared }; }, [selectedTable, structure.links]); return (

Database Explorer

Schemas, tables and inferred relationships

{selectedDb || "—"} · {colsQ.isLoading ? "—" : `${N(schemaCount)} schema${schemaCount === 1 ? "" : "s"}`} · {colsQ.isLoading ? "—" : `${N(tableCount)} object${tableCount === 1 ? "" : "s"}`} · {colsQ.isLoading ? "—" : `${N(fkCount)} relationship${fkCount === 1 ? "" : "s"}`} {colsQ.isError && · Error: {String(colsQ.error?.message)}}

{/* ── Left: Database / schema explorer ─────────────────────────── */}

Databases {!dbsQ.isLoading && `(${dbs.length})`}

setDbFilter(e.target.value)} placeholder="Filter…" style={{ width: "100%", fontSize: 11, padding: "4px 6px 4px 22px", border: "1px solid #d4d4d4", borderRadius: 4, outline: "none", background: "#fff", color: "#231f20", }} />

{dbsQ.isLoading && (

Loading…

)} {!dbsQ.isLoading && filteredDbs.length === 0 && (

No matches.

)} {filteredDbs.map(db => { const isSel = db === selectedDb; return ( ); })}

{structure.schemaNames.length > 0 && (

Schemas ({structure.schemaNames.length})

{structure.schemaNames.map(s => { const sel = selectedSchemas.has(s); const cnt = structure.sortedTablesBySchema.get(s)?.length || 0; return ( ); })}

)}

{/* ── Resize handle ──────────────────────────────────────────── */}

{ if (!isResizingExplorer) (e.currentTarget as HTMLElement).style.background = "#bcd0e0"; }} onMouseLeave={(e) => { if (!isResizingExplorer) (e.currentTarget as HTMLElement).style.background = "#e5e5e5"; }} /> {/* ── Center: Diagram canvas ──────────────────────────────────── */}

{colsQ.isLoading && (

Loading schema…

)} {!colsQ.isLoading && structure.tables.length === 0 && selectedDb && (

No tables or views found in {selectedDb}.

)} {!colsQ.isLoading && structure.tables.length > 0 && visible.tables.length === 0 && (

No schemas selected — pick one or more from the left panel.

)}

{schemaBoxes.map(s => (

{s.name}

))} {visible.tables.map(t => { const key = tableKey(t); const p = getPos(key); const isDragging = draggingKey === key; const isSelected = selectedKey === key; const isFocused = isDragging || isSelected; return (

startDragOrClick(e, key)} style={{ position: "absolute", left: p.x, top: p.y, width: TABLE_WIDTH, background: "#ffffff", border: isFocused ? "1px solid #0777b3" : "1px solid #d4d4d4", borderRadius: 6, boxShadow: isFocused ? "0 6px 16px rgba(7,119,179,0.18)" : "0 1px 2px rgba(0,0,0,0.04)", overflow: "hidden", zIndex: isFocused ? 10 : 1, userSelect: "none", cursor: isDragging ? "grabbing" : "grab", touchAction: "none", }} >

{t.kind === "VIEW" ? : } {t.name}

{t.columns.map((c, i) => { const isPK = c.name === t.pk; const isFK = t.fkMap.has(c.name); return (

{isPK ? : isFK ? : } {c.name}

); })}

{Math.round(transform.k * 100)}% · click table to inspect · drag to move · scroll to zoom

table view PK FK – – shared col

{/* ── Right: Detail panel ─────────────────────────────────────── */} {selectedTable && (

{selectedTable.kind === "VIEW" ? : } {selectedTable.name}

{selectedTable.schema}.{selectedTable.name} · {selectedTable.kind.toLowerCase()}

Rows

{rowCountQ.isLoading ? "…" : rowCountQ.isError ? "—" : fmtNum(totalRows)}

Cols

{selectedTable.columns.length}

{selectedTable.pk && (

{selectedTable.pk}

)}

{(relsForSelected.outgoing.length > 0 || relsForSelected.incoming.length > 0 || relsForSelected.shared.length > 0) && (

Relationships

{relsForSelected.outgoing.map((l, i) => (

→ {l.fromCol} to {l.toKey}.{l.toCol}

))} {relsForSelected.incoming.map((l, i) => (

← {l.toCol} from {l.fromKey}.{l.fromCol}

))} {relsForSelected.shared.map((l, i) => { const otherKey = l.fromKey === tableKey(selectedTable) ? l.toKey : l.fromKey; const otherCol = l.fromKey === tableKey(selectedTable) ? l.toCol : l.fromCol; const myCol = l.fromKey === tableKey(selectedTable) ? l.fromCol : l.toCol; return (

~ {myCol} · shared with {otherKey}.{otherCol}

); })}

)}

{summarizeQ.isError && (

Stats unavailable: {String(summarizeQ.error?.message || "")}

)} {selectedTable.columns.map((c) => ( setExpandedCol(prev => prev === c.name ? null : c.name)} selectedDb={selectedDb} schemaName={selectedTable.schema} tableName={selectedTable.name} totalRows={totalRows} isSummaryLoading={summarizeQ.isLoading} /> ))}

)}

{selectedTable && ( )}

); } function SampleDataTable({ selectedDb, selectedTable }: { selectedDb: string; selectedTable: TableInfo }) { const tableRef = `${qid(selectedDb)}.${qid(selectedTable.schema)}.${qid(selectedTable.name)}`; const sampleQ = useSQLQuery(`SELECT * FROM ${tableRef} LIMIT 10`); const rows = useMemo(() => Array.isArray(sampleQ.data) ? sampleQ.data : [], [sampleQ.data]); const cols = selectedTable.columns; return (

{selectedTable.schema}.{selectedTable.name} · first 10 rows {sampleQ.isLoading && } SELECT * FROM {selectedTable.schema}.{selectedTable.name} LIMIT 10

{sampleQ.isError && (

{String(sampleQ.error?.message || "Could not load sample rows")}

)} {!sampleQ.isLoading && !sampleQ.isError && rows.length === 0 && (

No rows in this {selectedTable.kind.toLowerCase()}.

)} {!sampleQ.isError && rows.length > 0 && ( {cols.map(c => { const numeric = isNumericType(c.type); return ( ); })} {rows.map((row, i) => ( {cols.map(c => { const v = (row as any)[c.name]; const isNull = v == null; const formatted = isNull ? "NULL" : fmtCell(v); const numeric = isNumericType(c.type); return ( ); })} ))}

{c.name} {shortType(c.type)}
{truncStr(formatted, 80)}

)}

); } function ColumnDetail({ col, summary, isPK, isFK, isExpanded, onToggle, selectedDb, schemaName, tableName, totalRows, isSummaryLoading, }: { col: Col; summary: any; isPK: boolean; isFK: boolean; isExpanded: boolean; onToggle: () => void; selectedDb: string; schemaName: string; tableName: string; totalRows: number | null; isSummaryLoading: boolean; }) { const viz = vizForType(col.type); const ref = `${qid(selectedDb)}.${qid(schemaName)}.${qid(tableName)}`; const colId = qid(col.name); const sql = useMemo(() => { if (!isExpanded || !viz) return ""; if (viz === "numeric") { return ` WITH src AS (SELECT CAST(${colId} AS DOUBLE) AS v FROM ${ref} WHERE ${colId} IS NOT NULL), stats AS (SELECT MIN(v) AS mn, MAX(v) AS mx FROM src), bk AS ( SELECT CASE WHEN s.mx = s.mn THEN 0 ELSE LEAST(${NUM_BUCKETS - 1}, GREATEST(0, FLOOR((src.v - s.mn) * ${NUM_BUCKETS}.0 / NULLIF(s.mx - s.mn, 0))::INT)) END AS b FROM src, stats s ) SELECT b AS bucket, COUNT(*) AS cnt, (SELECT mn FROM stats) AS mn, (SELECT mx FROM stats) AS mx FROM bk GROUP BY b ORDER BY b `; } if (viz === "timestamp") { return ` WITH src AS (SELECT CAST(${colId} AS TIMESTAMP) AS v FROM ${ref} WHERE ${colId} IS NOT NULL), stats AS (SELECT MIN(v) AS mn, MAX(v) AS mx FROM src), unit AS ( SELECT CASE WHEN EXTRACT(EPOCH FROM mx - mn) < 86400 * 60 THEN 'day' WHEN EXTRACT(EPOCH FROM mx - mn) < 86400 * 730 THEN 'week' ELSE 'month' END AS u FROM stats ), buck AS ( SELECT CASE u WHEN 'day' THEN CAST(date_trunc('day', v) AS VARCHAR) WHEN 'week' THEN CAST(date_trunc('week', v) AS VARCHAR) ELSE CAST(date_trunc('month', v) AS VARCHAR) END AS b FROM src, unit ) SELECT b AS bucket, COUNT(*) AS cnt, (SELECT u FROM unit) AS unit FROM buck GROUP BY b ORDER BY b `; } return ` SELECT CAST(${colId} AS VARCHAR) AS bucket, COUNT(*) AS cnt FROM ${ref} WHERE ${colId} IS NOT NULL GROUP BY ${colId} ORDER BY cnt DESC LIMIT 12 `; }, [isExpanded, viz, ref, colId]); const histoQ = useSQLQuery(sql, { enabled: !!sql }); const rawRows = useMemo(() => Array.isArray(histoQ.data) ? histoQ.data : [], [histoQ.data]); const chart = useMemo(() => { if (!viz || rawRows.length === 0) return { type: "none" as const }; if (viz === "numeric") { const mn = N((rawRows[0] as any).mn); const mx = N((rawRows[0] as any).mx); const span = mx - mn; const counts = new Array(NUM_BUCKETS).fill(0); for (const r of rawRows as any[]) { const b = Math.max(0, Math.min(NUM_BUCKETS - 1, Number(r.bucket))); counts[b] = N(r.cnt); } const data = counts.map((cnt, i) => ({ bucket: i, cnt, rangeStart: mn + (span * i) / NUM_BUCKETS, rangeEnd: mn + (span * (i + 1)) / NUM_BUCKETS, })); return { type: "numeric" as const, data, mn, mx }; } if (viz === "timestamp") { const data = (rawRows as any[]).map(r => ({ bucket: fmtBucketLabel(String(r.bucket)), cnt: N(r.cnt), })); const unit = (rawRows[0] as any).unit || "day"; return { type: "timestamp" as const, data, unit }; } const data = (rawRows as any[]).map(r => ({ bucket: fmtVal(r.bucket, 22), bucketRaw: r.bucket, cnt: N(r.cnt), })); return { type: "categorical" as const, data }; }, [viz, rawRows]); const numericMin = summary?.min; const numericMax = summary?.max; const numericAvg = summary?.avg; return (

{isPK ? : isFK ? : } {col.name} {shortType(col.type)}

{col.nullable ? "nullable" : "not null"} {summary && nulls: {fmtPct(summary.null_percentage)}} {summary && distinct: {fmtNum(summary.approx_unique)}} {!summary && isSummaryLoading && loading…}

{isExpanded && (

e.stopPropagation()} style={{ marginTop: 10 }}> {!viz && (

No chart available for type {col.type}.

)} {viz && histoQ.isLoading && (

Loading distribution…

)} {viz && histoQ.isError && (

{String(histoQ.error?.message || "Could not compute distribution")}

)} {viz && !histoQ.isLoading && !histoQ.isError && chart.type === "numeric" && ( )} {viz && !histoQ.isLoading && !histoQ.isError && chart.type === "timestamp" && ( )} {viz && !histoQ.isLoading && !histoQ.isError && chart.type === "categorical" && ( )} {summary && viz === "numeric" && (numericMin != null || numericMax != null) && (

min {fmtNumeric(numericMin)} max {fmtNumeric(numericMax)} {numericAvg != null && avg {fmtNumeric(numericAvg)}}

q25 {fmtNumeric(summary.q25)} q50 {fmtNumeric(summary.q50)} q75 {fmtNumeric(summary.q75)}

)} {summary && viz === "timestamp" && (numericMin != null || numericMax != null) && (

earliest {fmtVal(numericMin, 22)} latest {fmtVal(numericMax, 22)}

)}

); } function NumericHistogram({ data, mn, mx }: { data: { bucket: number; cnt: number; rangeStart: number; rangeEnd: number }[]; mn: number; mx: number }) { const span = mx - mn; return (

fmtNum(v)} /> { const r = p?.[0]?.payload; if (!r) return ""; return `${fmtNumeric(r.rangeStart)} – ${fmtNumeric(r.rangeEnd)}`; }} formatter={(v: any) => [fmtNum(v), "count"]} />

{fmtNumeric(mn)} {span > 0 ? `${NUM_BUCKETS} buckets` : "single value"} {fmtNumeric(mx)}

); } function TimestampSeries({ data, unit }: { data: { bucket: string; cnt: number }[]; unit: string }) { const showTicks = data.length <= 8 ? "preserveEnd" : "preserveStartEnd"; return (

fmtBucketLabel(v).slice(0, 7)} /> fmtNum(v)} /> [fmtNum(v), "count"]} />

bucketed by {unit}

); } function CategoricalTop({ data, totalRows }: { data: { bucket: string; cnt: number }[]; totalRows: number | null }) { const max = data.reduce((m, d) => Math.max(m, d.cnt), 0); const tr = totalRows ?? max; return (

top {data.length}

{data.map((d, i) => { const pct = max > 0 ? (d.cnt / max) * 100 : 0; const sharePct = tr > 0 ? (d.cnt / tr) * 100 : 0; return (

{d.bucket}

{fmtNum(d.cnt)} · {sharePct < 0.1 ? "<0.1" : sharePct.toFixed(sharePct < 10 ? 1 : 0)}%

); })}

); }

Database Explorer

Comments