When AI Coding Agents Go Awry: The Architectural Redesign of PostgreSQL Container SBOMs and Provenance

Main Facts: The Quest for Secure PostgreSQL Extensions

In the rapidly evolving landscape of cloud-native databases, supply chain security has transitioned from a theoretical best practice to an urgent operational requirement. Software Bill of Materials (SBOMs), complete inventory tracking, cryptographic provenance, and attestation are now foundational elements for deploying production-grade infrastructure.

For developers and database administrators working with CloudNativePG (CNPG)—a popular Kubernetes operator for PostgreSQL—integrating secure extensions into containerized environments presents a distinct set of engineering hurdles. Recently, an ambitious engineering effort aimed at embedding comprehensive SBOMs and provenance tracking into CloudNativePG extension images ran into a classic software architecture trap.

Driven heavily by AI coding assistants, the project quickly spiraled into structural fragmentation. The root of the crisis lay in a divergence of user experience: building extensions via standard Debian packages versus compiling them using pgrx (a Rust framework for developing PostgreSQL extensions) resulted in two entirely different sets of validation commands and structural pipelines. Recognizing that end-users simply want a seamless, unified way to verify their database extensions without untangling the underlying build mechanics, the project leadership hit the reset button. By pivoting to Docker’s native custom SBOM generators and leveraging a modular plugin architecture, the initiative successfully unified its build pipelines while maintaining rigorous supply chain security.

Chronology: How AI-Driven Development Led to a Full Reset

Phase 1: The Initial AI-Assisted Sprint

The project began as an effort to pack PostgreSQL extensions into containers with full inventory, provenance, and attestation data. Relying extensively on AI agents to write boilerplate code, structure Dockerfiles, and configure CI/CD workflows, development moved at a blistering pace.

Agents Gone Awry on Postgres SBOMs: Start Over

As the author noted, "I didn’t have time to write a short letter"—a sentiment reflecting the sheer volume of code generated during the initial sprint. AI agents excelled at the micro-level, rapidly spinning up configuration files and parsing dependency trees. However, without a rigid macro-architecture dictated upfront, the agents optimized individual paths rather than the holistic user experience.

Phase 2: The Architectural Divergence

After roughly a week of intense development, a critical flaw emerged. The validation workflows for Debian-based extensions and pgrx-compiled extensions had drifted apart.

Debian-Based Extensions: Initially built using QEMU emulation for multi-architecture support, these packages installed cleanly via standard package managers, maintaining a specific metadata structure. However, QEMU emulation introduced severe performance bottlenecks, leading to excessive build timeouts.
pgrx Extensions: To resolve the build timeouts, the engineering workflow was refactored to utilize native GitHub Runners. While this solved the performance issue, it fundamentally altered the internal layout and artifact generation of the resulting container images.

Consequently, a user attempting to verify the security provenance of a Debian-based extension container had to execute a completely different set of commands than someone verifying a pgrx-based extension.

Phase 3: The Reality Check and Back to the Drawing Board

Realizing that "most users don’t know or care how I built the container; they just want to use a Postgres extension," the project hit pause. Presenting fragmented validation procedures to the open-source community would create unnecessary friction and adoption barriers.

The developer recognized a fundamental truth about human-AI collaboration: AI agents are exceptionally capable executors, but they cannot supply the foundational product vision or architectural constraints. Because the initial design lacked a unified validation model, the AI had simply raced ahead down divergent paths.

Phase 4: The Clean Slate and Custom Generators

Armed with a clear understanding of the design flaws, the project went back to the drawing board. Docker offers a powerful, yet frequently underutilized feature: the ability to integrate custom SBOM generators directly into the build process.

By re-engaging the AI agents with strict, explicitly defined architectural parameters, the codebase was refactored from the ground up:

Custom SBOM Generator Module: Most of the complex logic was encapsulated within a dedicated, self-contained sbom-generator module.
Plugin API: A flexible plugin API was introduced, specifically engineered to support future pgrx build integrations without breaking structural uniformity.
Minimal Pipeline Impact: Unlike the convoluted multi-step workflows of the previous iteration, the new design required surprisingly minimal modifications to the core CI/CD build pipelines.

Supporting Data: Structural Evolution of Container Images

The architectural shift can be understood through the changing anatomy of the container images throughout the project lifecycle.

The Original CloudNativePG Image Structure

In the initial mapping of CloudNativePG images, container layers were assembled linearly, focusing primarily on runtime availability rather than uniform metadata generation. While this approach successfully packaged the core database and its dependencies, it lacked a standardized mechanism for cross-platform artifact attestation.

The Fragmented State

Following the pgrx refactor to native GitHub Runners, the project suffered from a bifurcated design pattern:

Standard Debian Images maintained their legacy composition layers.
pgrx Images adopted a modified directory hierarchy and disparate metadata emission paths, summarized by internal tooling (such as Codex summaries) as structurally incompatible with their Debian counterparts.

The Unified Custom Generator Design

The final, optimized architecture centralizes metadata extraction. Below is a structural representation of how the new custom SBOM generator integrates into the build lifecycle:

+-------------------------------------------------------------                 +
|                        Docker Build Process                                  |
|                                                                              |
|  +--------------------+     +----------------------------------+             |
|  | Base PG Image Layer| --> | Extension Installation (Debian/  |             |
|  |                    |     | pgrx Native Compilation)         |             |
|  +--------------------+     +----------------------------------+             |
|                                              |                               |
|                                              v                               |
|                             +----------------------------------+             |
|                             | Custom SBOM Generator Module     |             |
|                             | - Self-Contained Logic           |             |
|                             | - Plugin API Integration         |             |
|                             +----------------------------------+             |
|                                              |                               |
|             +--------------------------------+----------------+              |
|             |                                                 |              |
|             v                                                 v              |
|  +---------------------+                           +---------------------+   |
|  | Unified SBOM Output |                           | Cryptographic       |   |
|  | (Consistent Schema) |                           | Provenance &        |   |
|  |                     |                           | Attestations        |   |
|  +---------------------+                           +---------------------+   |
+------------------------------------------------------------------------------+

For developers interested in inspecting the clean architecture, the work-in-progress code remains available on GitHub under the repository branch:
https://github.com/ardentperf/postgres-extensions-containers/tree/x-ai/ardentperf/cnpg-sbom-generator/sbom-generator

Official Perspectives and Human-AI Synergy

The pivot highlights a maturing perspective on artificial intelligence in software engineering. While tools powered by large language models—such as Codex and various coding agents—can drastically accelerate development velocity, they introduce unique governance challenges.

"One thing that AI could not do: correctly tell me what the right design was. Because it relies on me to know the right questions to ask, telling it the goals & priorities of the design."

This observation underscores the irreplaceable role of human architectural oversight. AI agents operate effectively within bounded contexts, but they lack strategic intent. When given ambiguous or incomplete constraints, they optimize for immediate compilation rather than long-term usability and ecosystem consistency.

By stepping back, re-evaluating user friction points, and enforcing a unified command interface for security validation, the project transformed an AI-generated labyrinth into an elegant, maintainable engineering artifact.

Implications for the Cloud-Native PostgreSQL Ecosystem

The lessons learned from this container SBOM overhaul extend far beyond a single developer’s workflow, offering significant implications for the broader CloudNativePG ecosystem.

1. Raising the Bar for Supply Chain Security

As regulatory frameworks and enterprise security standards demand greater transparency, container images containing database extensions can no longer be treated as black boxes. Generating verifiable SBOMs and cryptographic provenance ensures that enterprises can audit every compiled library running inside their PostgreSQL clusters.

2. The Rise of the CNPG-Extensions Project

While upstream integration of these features continues, the community-driven CNPG-Extensions Project (https://github.com/cnpg-extensions/postgres-extensions-containers) serves as a cutting-edge proving ground. Featuring advanced automation powered by Renovate, the project automatically tracks upstream extension releases, ensuring rapid deployment updates across image catalogs.

Supported extensions in the catalog include critical database utilities such as:

Performance & Monitoring: PG-Cron, PG-Stat-KCache, PGSentinel, PLProfiler
Data Management: PG-Partman, PLDebugger, PG-Hint-Plan
Federated Data: MySQL and Microsoft SQL Server Foreign Data Wrappers (FDWs)

3. A Blueprint for AI-Assisted Infrastructure Engineering

For platform engineers and DevOps teams looking to leverage AI coding agents for complex containerization tasks, this case study offers a vital blueprint. Success requires:

Upfront Architectural Guardrails: Defining strict user-facing interfaces (such as uniform validation commands) before letting AI generate internal logic.
Leveraging Native Tooling: Utilizing native platform features—like Docker’s custom SBOM generators—rather than reinventing wheels through brittle shell scripts or complex multi-step wrappers.
Continuous Refactoring: Recognizing when AI-generated code has created technical debt and having the discipline to reset and refactor for maintainability.

As the first major pull requests merge, bringing fully attested SBOMs and provenance tracking to the CNPG extensions ecosystem, database administrators can look forward to a future where high-performance extensions and uncompromising supply chain security go hand in hand.