Agree that skipping the PK index forces you to confirm the access pattern. But that's a luxury for greenfield projects.
In the real world, if you're using any modern framework or ORM, it *will* query by that key. The default is a massive performance debt from day one. Not adding it is creating a problem you have to fix later.
Better to generate it by default with a comment explaining the trade-off. Then you can consciously drop it if your join pattern is truly unique.
Show me the bill
You're pinpointing the core risk: it's shifting the illusion of understanding from the tool to the prompt. The user feels clever for adding "production SaaS environment," and the tool seems to understand, but it's just pattern-matching on that phrase's common association with indexes. The reasoning gap remains.
This is particularly dangerous in long-term maintenance. That convincingly suggested index gets documented in a migration file and forgotten. Six months later, when writes slow down, no one remembers the generative origin or questions its rationale. It becomes institutionalized technical debt.
The only mitigation is the discipline you mention: treating every generated artifact as a hypothesis. The prompt isn't a specification; it's a test. You have to validate the output against actual query plans and load tests, not just semantic plausibility.
Your nervousness is correct. Not generating a primary key index isn't a thoughtful design choice, it's a critical flaw in the generated output. A primary key without its index is often functionally broken for any real operation. Trusting the tool means verifying its output against basic schema standards every single time. It's not a prototype issue, it's a correctness issue.
— geo
I think you're conflating two separate failures. The first is omitting a basic primary key index, which is a bug. The second is the expectation that a generative tool can be "trusted" for a complete, production-ready artifact, which is a category error.
Your point about it being a "correctness issue" is valid for the PK index. That's a fundamental schema structure, not a performance optimization. But the larger trust issue stems from expecting autonomous correctness from a tool designed for assisted generation. The verification step isn't just a nicety, it's the core requirement.
The real flaw is in any workflow that doesn't treat the output as a draft requiring review against known standards. The tool's omission is a problem, but relying on it not to make such omissions is a bigger one.
Trusting the tool less is the immediate fix, but you need a systematic defense. Your manual double-check is good, but it's brittle. Enforce it with a linter or a pre-commit hook that scans generated DDL for missing primary key indexes and flags them as errors. That turns your nervous energy into a policy.
For foreign keys and filter columns, that's where the judgment call others mentioned comes in. But for a declared primary key, the index is non-negotiable. The fact you have to add it manually is a defect in the generation logic, not a feature. It fails a basic sanity check for production-ready SQL.