← nyxcore-systems
6 min read

Building Smarter AI: Database Introspection & The Memory System Overhaul Begins

This week, we wired our AI workflow engine directly into the database schema, giving it crucial context, and laid the groundwork for a massive overhaul of our system's memory and insight capabilities.

AIworkflow-enginedatabase-introspectionmemory-systemdeveloper-experiencetypescriptnextjspostgreszsh-gotcha

Building intelligent systems isn't just about crafting clever prompts; it's about giving the AI the right context and ensuring it can learn effectively from its experiences. That's been the driving force behind our recent sprint. We've just pushed a significant update that enables our AI to "see" our database schema, and we're now diving deep into a comprehensive overhaul of how our system captures and utilizes insights.

Let's break down what we shipped and what's next on the horizon.

Giving Our AI X-Ray Vision: Database Introspection

One of the biggest limitations for an AI trying to help with development tasks is its lack of real-time, context-specific information. How can it suggest a SQL query or a data model if it doesn't know what tables, columns, or relationships exist in your database?

Our solution: a dedicated Database Introspector.

We've introduced src/server/services/database-introspector.ts, a powerful new service designed to query pg_catalog and information_schema directly. It runs 9 parallel queries to gather comprehensive schema information – tables, columns, types, relationships, comments – and caches the results for 5 minutes to keep things snappy. The output? Clean, markdown-formatted summaries, perfect for AI consumption.

typescript
// src/server/services/database-introspector.ts (simplified example)
// Fetches and formats database schema for AI context.
async function introspectDatabaseSchema(): Promise<string> {
  // ... parallel queries against pg_catalog, information_schema ...
  // ... format results into markdown string ...
  return `
### Database Schema Overview

**Tables:**
- \`users\` (id, name, email, created_at)
- \`projects\` (id, name, user_id, status)
- \`tasks\` (id, project_id, description, due_date, completed)

... (more detailed schema info) ...
  `;
}

This introspector is now wired into our workflow-engine.ts. When a workflow runs, the {{database}} template variable is automatically populated with the latest schema context. This means our AI models can now receive prompts like:

"Given the following database schema: {{database}}, write a SQL query to find all incomplete tasks for projects owned by 'Alice'."

This is a game-changer for providing highly relevant and accurate AI assistance, moving us closer to truly intelligent developer workflows.

UI Polish: Sticky Progress Header

While the AI was getting smarter, we also gave our UI a little love. We moved the <WorkflowRunProgress> component inside a sticky step navigator div in workflows/[id]/page.tsx. This might seem minor, but having the workflow's progress always visible as you scroll through a long output or complex steps significantly improves the user experience. No more scrolling up and down to see what's happening!

A Relatable Developer's Pain: The Unquoted Path

Not all progress is glamorous. Sometimes, the biggest lessons come from the smallest, most frustrating errors.

This week, I stumbled into a classic zsh trap while trying to git add some files located in our src/app/(dashboard)/ directory.

bash
git add src/app/(dashboard)/path/to/file.tsx

The result? A cryptic zsh: no matches found: src/app/(dashboard)/path/to/file.tsx. My brain immediately went to "permissions? file doesn't exist?" But no, the file was right there.

The culprit, as it often is with zsh, was the unquoted parentheses () in the path. zsh interprets these as special characters for pattern matching, and when it couldn't find a pattern to match, it threw the error.

Lesson Learned (the hard way, again): Always, always quote paths that contain special characters (like parentheses, spaces, asterisks) when using them in shell commands, especially git commands.

bash
git add "src/app/(dashboard)/path/to/file.tsx" # The correct way

A quick double-quote fixed it, but it's a reminder that even seasoned developers can fall victim to shell esoterica. Consider this your friendly reminder!

The Next Frontier: Overhauling Our Memory System

With the database context flowing, our focus shifts to making our system truly learn from every interaction. The current "review key points → memory pipeline" has served us well, but as we scale, we've identified 7 critical gaps that need addressing. This isn't just about fixing bugs; it's about transforming our system's ability to capture, store, and retrieve actionable insights.

Here's a sneak peek at the memory system overhaul:

  1. Beyond Pain Points: Creating Solution Insights: Currently, we pair pain_point insights with strength insights. But what about suggestions? When a pain point comes with a suggested solution, we need to generate a companion "solution" WorkflowInsight, explicitly linked via pairedInsightId. This will allow our system to learn not just what went wrong, but how to fix it.

  2. Making "Avoid" Learnings Visible in MemoryPicker: Our MemoryPicker currently filters out pain_point insights. This is a mistake! Users need to see "avoid X" learnings just as much as "do Y." We'll be adding a toggle or dedicated section to ensure users can easily access these critical "anti-patterns" and lessons learned.

  3. Deduplicating Insight Persistence: We've noticed duplicate persistence paths for key points in SaveInsightsDialog and workflows.resume(). We'll be implementing robust upsert logic using reviewKeyPointId from metadata to ensure data integrity and efficiency.

  4. Handling "Recreate" Actions Properly: When a user selects "Recreate" for a key point, it's currently treated like "keep." This action implies a need for re-extraction or re-review. We'll adjust the logic to accurately reflect this, potentially flagging the insight for re-processing or closer human review.

  5. Making Action Fields Queryable: The action field (e.g., "keep," "discard," "recreate") is currently buried in JSON metadata. To enable better analytics and filtering, we'll either promote it to a dedicated column or, at minimum, add an indexed JSON path.

  6. Alerting on Embedding Failures: Our current embedding process for insights fails silently. This is a blind spot. We'll be surfacing these failures in the UI or an audit log to ensure we catch and address issues with our semantic search capabilities.

  7. Returning Cross-Project Scan Results: The triggerCrossProjectScan() function is currently a fire-and-forget operation with no visible output. To make this powerful feature useful, we need to provide a mechanism to view or act upon its results.

This overhaul touches several core files, including src/server/services/insight-persistence.ts, src/server/services/workflow-insights.ts, and key UI components like src/components/workflow/save-insights-dialog.tsx and src/components/workflow/memory-picker.tsx. It's a significant undertaking, but one that will profoundly impact our system's intelligence and utility.

Wrapping Up

This session saw us make great strides in connecting our AI to live database context and refining the user experience. We also learned a valuable, albeit minor, lesson about shell quoting. Now, with the stage set, we're eagerly diving into the memory system overhaul, excited to build a system that not only helps you achieve your goals but truly learns and grows with you.

Stay tuned for more updates as we transform these identified gaps into powerful new capabilities!

json
{
  "thingsDone": [
    "Implemented a database introspector service (`src/server/services/database-introspector.ts`) using parallel pg_catalog/information_schema queries, 5-min caching, and markdown output.",
    "Integrated `{{database}}` template variable into `workflow-engine.ts` for AI context.",
    "Moved `<WorkflowRunProgress>` into a sticky UI element for improved UX in `workflows/[id]/page.tsx`.",
    "Updated CLAUDE.md documentation for new `{{database}}` variable."
  ],
  "pains": [
    "Encountered `zsh: no matches found` error when using `git add` with unquoted paths containing parentheses (e.g., `src/app/(dashboard)/...`).",
    "Realized the necessity of consistently quoting paths with special characters in shell commands."
  ],
  "successes": [
    "Successfully delivered database introspection, providing crucial context for AI workflows.",
    "Improved workflow progress visibility with a sticky header.",
    "Identified and prioritized 7 key gaps in the memory/insight system for future overhaul.",
    "Maintained clean typecheck and dev server stability throughout the changes."
  ],
  "techStack": [
    "TypeScript",
    "Next.js",
    "PostgreSQL",
    "Redis",
    "Docker",
    "zsh"
  ]
}