{"id":110766,"date":"2026-06-29T12:00:00","date_gmt":"2026-06-29T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110766"},"modified":"2026-06-15T09:09:41","modified_gmt":"2026-06-15T09:09:41","slug":"i-let-copilot-build-my-database-heres-what-i-learned-and-everything-id-do-differently-next-time","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/i-let-copilot-build-my-database-heres-what-i-learned-and-everything-id-do-differently-next-time\/","title":{"rendered":"I let Copilot build my database. Here’s what I learned – and everything I’d do differently next time (part two)"},"content":{"rendered":"\n

I handed Microsoft Copilot the keys to design a PostgreSQL schema for Azure Database for PostgreSQL Flexible Server, with as little intervention from me as I could stand. What came back was a working four-table schema (dialects, terms, translations, artifacts) with foreign keys, JSONB metadata, seed data for three SQL dialects, and a deployment checklist that actually ran<\/em>. <\/strong> <\/p>\n\n\n\n

But that’s just a small part of the story. In this article, I’ll shout about what it got right – but also emphasize what it got wrong, why it happened, and what I’ve learned. Includes tips, advice, and the full code repo for if you want to give<\/strong> it a go yourself.<\/strong><\/p>\n\n\n\n

This is the second part of Grant Fritchey’s series on building an app and database almost entirely through an LLM.<\/a> The repo, including every prompt, lives on GitHub<\/a>.<\/em><\/p>\n\n\n\n

In part one, I established the rules of this experiment and got the AI to commit to a project plan. This time, we actually build something. The first thing on the list, per Copilot’s own recommendation in part one, is the database. So: a PostgreSQL<\/a> schema for dbRosetta, hosted on Azure Flexible Server<\/a>. And the most important bit: it’s all designed and generated by Copilot<\/a>, with as little intervention from me as I can stand.<\/p>\n\n\n\n

What follows is what happened, what I let slide, what I overrode, and a slightly uncomfortable question I haven’t fully answered for myself yet.<\/p>\n\n\n\n

Generating the prompt<\/h2>\n\n\n\n

I’d originally said I would write two prompts in parallel \u2014 one by hand, one from Copilot \u2014 and compare them. On reflection I dropped that. The whole point of this series is to see what happens when the AI runs the show, and writing my own version in parallel would have biased everything that came after it. <\/p>\n\n\n\n

So, I gave Copilot a one-liner asking for the Visual Studio Code (VSCode)<\/a> prompt and got back a properly structured one: schema name, table list, constraints, JSONB metadata, deterministic<\/a> style, “do not include destructive statements,”<\/em> sample seed inserts for three dialects, and a deployment checklist.<\/p>\n\n\n\n

Click here for the full prompt, and Copilot’s response<\/strong><\/em><\/summary>\n
Prompt<\/strong><\/td>Response<\/strong><\/td><\/tr>
OK. Let’s start with the initial prompt for vsCode to create the database. Would you like to create an example of that prompt? Do you need more information before you create the database that I’ve roughly described?<\/em><\/td>VS Code prompt for Copilot (create initial Azure Postgres schema)<\/strong> Use this prompt in VS Code Copilot (or your prompt-runner) to generate the initial SQL DDL and a tiny deployment checklist. It assumes an Azure Database for PostgreSQL instance and creates the minimal tables for dbRosetta v0: dialects, terms, translations, and a simple artifacts\/metadata table. It also records the assumptions used so the output is deterministic.Prompt SYSTEM: You are a careful SQL developer and infra assistant. Produce a single SQL file (UTF-8) that can be applied to an Azure PostgreSQL instance to create a schema named dbrosetta and four tables: dialects, terms, translations, and artifacts. Include sensible indexes, constraints, and comments that reflect the acceptance criteria for term translation (no users\/auth stored, cacheable translations, metadata JSON). Use standard PostgreSQL types and features compatible with Azure Database for PostgreSQL (single server\/postgres-compatible). Do not include any destructive statements (DROP TABLE). At the top of the file, include a brief assumptions block (postgres version, encoding, owner placeholder). Provide example INSERTs for a few dialects (postgres, sqlserver, mysql). USER: Generate the SQL file now, then provide a one-paragraph deployment checklist with the minimal Azure connection\/env vars and a sample psql command to apply the file. Keep SQL and checklist separate and clearly labeled. Use low creativity and deterministic style.
Example SQL DDL result (what Copilot should produce)<\/strong>— Assumptions:
— Postgres version: compatible with 12+ features
— Encoding: UTF8
— Schema: dbrosetta
— Owner: replace with your DB user CREATE SCHEMA IF NOT EXISTS dbrosetta AUTHORIZATION current_user;– dialects: supported DB dialects and a docs home URL CREATE TABLE dbrosetta.dialects ( id SERIAL PRIMARY KEY, slug TEXT NOT NULL UNIQUE, name TEXT NOT NULL, documentation_home_url TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); COMMENT ON TABLE dbrosetta.dialects IS ‘Registry of supported dialects and their canonical documentation home page’;– terms: canonical terms per dialect for potential seed data CREATE TABLE dbrosetta.terms ( id SERIAL PRIMARY KEY, dialect_id INT NOT NULL REFERENCES dbrosetta.dialects(id) ON DELETE RESTRICT, term TEXT NOT NULL, canonical_normalized_term TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (dialect_id, canonical_normalized_term) ); COMMENT ON TABLE dbrosetta.terms IS ‘Optional seeding of known terms per dialect; canonical_normalized_term is lowercased normalization’;– translations: cached translation results and metadata from runs CREATE TABLE dbrosetta.translations ( id BIGSERIAL PRIMARY KEY, from_dialect_id INT NOT NULL REFERENCES dbrosetta.dialects(id) ON DELETE RESTRICT, to_dialect_id INT NOT NULL REFERENCES dbrosetta.dialects(id) ON DELETE RESTRICT, input_term TEXT NOT NULL, preferred_term TEXT, alternates JSONB DEFAULT ‘[]’::jsonb, description TEXT, doc_url TEXT, mapping_report JSONB DEFAULT ‘[]’::jsonb, metadata JSONB NOT NULL, cached BOOLEAN NOT NULL DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (from_dialect_id, to_dialect_id, lower(input_term)) WHERE cached ); COMMENT ON TABLE dbrosetta.translations IS ‘Cached translations: metadata includes prompt_version, model_settings, run_id, timestamp’;– artifacts: optional generic artifacts produced by runs CREATE TABLE dbrosetta.artifacts ( id BIGSERIAL PRIMARY KEY, run_id UUID NOT NULL, artifact_type TEXT NOT NULL, — e.g., translation.json, metadata.json payload JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); COMMENT ON TABLE dbrosetta.artifacts IS ‘Store run artifacts for auditing and reproducibility’;– Example seeds INSERT INTO dbrosetta.dialects (slug, name, documentation_home_url) VALUES (‘postgres’, ‘PostgreSQL’, ‘https:\/\/www.postgresql.org\/docs\/’), (‘sqlserver’, ‘Microsoft SQL Server’, ‘https:\/\/learn.microsoft.com\/sql\/’), (‘mysql’, ‘MySQL’, ‘https:\/\/dev.mysql.com\/doc\/’);– Optional: index to speed lookup by normalized input term in translations CREATE INDEX IF NOT EXISTS idx_translations_from_to_input ON dbrosetta.translations(from_dialect_id, to_dialect_id, lower(input_term)); <\/strong>Minimal deployment checklist (one-paragraph)<\/strong>Assumptions: you have an Azure PostgreSQL server and a database created; export connection info as environment variables PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD. Apply the SQL file with: psql “host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD sslmode=require” -f dbrosetta-init.sql. . In CI, store secrets in GH Actions secrets or Azure Key Vault and run the same psql command in a job step; verify success by querying SELECT count(*) FROM dbrosetta.dialects;. <\/strong>Assumptions I used (so you can edit them into the prompt-runner)<\/strong>Azure Postgres supports standard PostgreSQL 12+ features and JSONB.No row-level security or advanced role setup is included; owner replacement is manual.Caching is enabled by default: translations are inserted with cached=true.Metadata JSON must include prompt_version and model_settings as strings\/objects during run-time (enforced by application logic, not DB constraints).If this meets your needs, I will produce a ready-to-save dbrosetta-init.sql file and a short GitHub Actions job snippet next.<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/details>\n\n\n\n

<\/p>\n\n\n\n

The interesting part was the deviation. Back in part one, Copilot and I agreed a canonical prompt template \u2014 frontmatter with version and model settings, explicit input and output contracts, acceptance criteria, telemetry keys. The prompt it produced for the database didn’t follow that template. <\/p>\n\n\n\n

In response, here’s what I then asked it directly:<\/h4>\n\n\n\n
\n

That’s great and I’ll use it as written within VSCode if it’s what you want. However, is that following the template that you defined for me earlier?<\/em><\/p>\n\n\n\n

Click here for the full prompt, and Copilot’s response<\/em><\/strong><\/summary>\n
Prompt<\/strong><\/td>Response<\/strong><\/td><\/tr>
That’s great and I’ll use it as written within vsCode if it’s what you want. However, is that following the template that you defined for me earlier?<\/em><\/td>Assessment<\/strong>Yes \u2014 the VS Code prompt and the example SQL DDL you accepted broadly follow the canonical prompt template you asked for, but they\u2019re not a 1:1 fulfillment of every template section. The prompt covers Intent, Context, Template Prompt text, and a deterministic ModelSettings requirement, and the DDL output aligns with the Acceptance Criteria and Guardrails you described.Where it already matches the canonical template<\/strong>Intent<\/strong>: Clearly stated (create initial Azure Postgres schema for dbRosetta).Context<\/strong>: Included assumptions about Azure Postgres, schema name, and non\u2011destructive output.Template Prompt (literal)<\/strong>: Provided a SYSTEM and USER instruction that can be pasted into Copilot or a prompt-runner.Guardrails<\/strong>: Explicit \u201cDo not include destructive statements\u201d and determinism guidance (low creativity).Post Processing expectations<\/strong>: You asked for a separate checklist and told Copilot to keep SQL and checklist separate.Small gaps to make it fully conformant (suggested, optional)<\/strong>Frontmatter \/ ModelSettings block<\/strong>: Add a short frontmatter header (Title, Version, ModelSettings) to the prompt file so it matches the template structure and can be parsed by your prompt-runner.Input \/ Output Contracts<\/strong>: Explicitly state the Input Contract (e.g., target schema name, owner placeholder, desired seed dialects) and Output Contract (single SQL file path; checklist text file) inside the prompt text so CI and schema validators can assert them.Acceptance Criteria<\/strong>: Convert the existing informal checks into 3\u20136 bullet acceptance sentences (e.g., “DDL creates schema and tables without DROP statements”; “translations table contains metadata JSON and cache uniqueness constraint”; “psql apply command succeeds in CI”).Telemetry keys<\/strong>: Ask the run to emit a run_id and include metadata keys (prompt_version, model_settings) in an artifacts JSON when the prompt-runner executes.Minimal actionable edit (one small snippet you can paste)<\/strong>Add this frontmatter and two short contract lines at the top of your prompt before the SYSTEM line:Title: “dbRosetta init schema” Version: “0.1.0” ModelSettings: { temperature: 0.0, max_tokens: 1024, top_p: 1.0 }Input Contract: { schema_name: “dbrosetta”, owner_placeholder: “<DB_USER>”, seed_dialects: [“postgres”,”sqlserver”,”mysql”] } Output Contract: { file: “dbrosetta-init.sql”, checklist: “dbrosetta-deploy-checklist.txt” }…<additional stuff snipped><\/td><\/tr><\/tbody><\/table><\/figure>\n<\/details>\n<\/blockquote>\n\n\n\n

<\/p>\n\n\n\n

Copilot acknowledged the gaps cheerfully, told me what was missing, and offered a small frontmatter snippet to bolt on. To paraphrase: yes, broadly, but here are five things you could add to make it fully conformant.<\/em> Which is fine. But it would never have flagged any of this had I not asked. <\/p>\n\n\n\n

An LLM will produce something that resembles what you asked for, much faster than it will produce something that conforms to a standard you defined three sessions ago.<\/strong> Bookmark that one.<\/p>\n\n\n\n

I let it run with the corrected prompt. Over to VSCode.<\/p>\n\n\n\n

Frankenstein, with relational integrity<\/h2>\n\n\n\n

I’m not going to lie. There is something deeply odd about watching an LLM create a file in your workspace, fill it with two hundred lines of DDL, and then write you a deployment checklist that actually works<\/em>. <\/p>\n\n\n\n

From the prompt, it generated init-dbrosetta-schema.sql<\/code>, four tables, indexes<\/a>, foreign keys<\/a>, comments on every column, sample seed data for PostgreSQL, SQL Server<\/a> and MySQL<\/a>, and a tidy little psql<\/code> command to apply it.<\/p>\n\n\n\n

I tested it. It worked. Schema present, tables present, seed data queryable. I feel just a little like Victor Frankenstein. Actually, I feel a lot like Victor Frankenstein.<\/p>\n\n\n\n

Here’s the interesting part of what it produced \u2014 the translations<\/code> table, which is where the domain actually lives:<\/p>\n\n\n\n

CREATE TABLE translations (\n    id SERIAL PRIMARY KEY,\n    term_id INTEGER NOT NULL REFERENCES terms(id) ON DELETE CASCADE,\n    dialect_id INTEGER NOT NULL REFERENCES dialects(id) ON DELETE CASCADE,\n    translated_term VARCHAR(500) NOT NULL,\n    syntax_pattern TEXT,\n    examples TEXT,\n    notes TEXT,\n    confidence_level INTEGER DEFAULT 100\n        CHECK (confidence_level >= 0 AND confidence_level <= 100),\n    is_active BOOLEAN NOT NULL DEFAULT true,\n    metadata JSONB,\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE(term_id, dialect_id)\n);<\/pre><\/div>\n\n\n\n
Click here to see the full code<\/em><\/strong><\/summary>\n
-- =============================================================================\n-- dbRosetta Schema Initialization\n-- =============================================================================\n-- ASSUMPTIONS:\n-- - PostgreSQL version 11+ (Azure Database for PostgreSQL compatible)\n-- - UTF-8 encoding\n-- - Database owner\/admin privileges required for schema creation\n-- - Target database already exists\n-- - Schema owner placeholder: replace 'dbrosetta_owner' with actual role\n-- =============================================================================\n-- Create schema\nCREATE SCHEMA IF NOT EXISTS dbrosetta;\n-- Set search path for this session\nSET search_path TO dbrosetta, public;\n-- =============================================================================\n-- TABLE: dialects\n-- Stores SQL dialect definitions and metadata\n-- =============================================================================\nCREATE TABLE dialects (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(50) NOT NULL UNIQUE,\n    display_name VARCHAR(100) NOT NULL,\n    version VARCHAR(20),\n    description TEXT,\n    is_active BOOLEAN NOT NULL DEFAULT true,\n    metadata JSONB,\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n-- Indexes for dialects table\nCREATE INDEX idx_dialects_name ON dialects (name);\nCREATE INDEX idx_dialects_active ON dialects (is_active);\nCREATE INDEX idx_dialects_metadata ON dialects USING GIN (metadata);\n-- Comments for dialects table\nCOMMENT ON TABLE dialects IS 'SQL dialect definitions and metadata for translation system';\nCOMMENT ON COLUMN dialects.name IS 'Unique identifier for the dialect (lowercase, no spaces)';\nCOMMENT ON COLUMN dialects.display_name IS 'Human-readable name for the dialect';\nCOMMENT ON COLUMN dialects.version IS 'Version identifier for the dialect';\nCOMMENT ON COLUMN dialects.is_active IS 'Flag to enable\/disable dialect for translations';\nCOMMENT ON COLUMN dialects.metadata IS 'Additional dialect configuration and features as JSON';\n-- =============================================================================\n-- TABLE: terms\n-- Stores canonical SQL terms and their definitions\n-- =============================================================================\nCREATE TABLE terms (\n    id SERIAL PRIMARY KEY,\n    canonical_term VARCHAR(200) NOT NULL UNIQUE,\n    category VARCHAR(50) NOT NULL,\n    subcategory VARCHAR(50),\n    description TEXT NOT NULL,\n    usage_context TEXT,\n    is_active BOOLEAN NOT NULL DEFAULT true,\n    metadata JSONB,\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n \n-- Indexes for terms table\nCREATE INDEX idx_terms_canonical ON terms (canonical_term);\nCREATE INDEX idx_terms_category ON terms (category);\nCREATE INDEX idx_terms_active ON terms (is_active);\nCREATE INDEX idx_terms_category_sub ON terms (category, subcategory);\nCREATE INDEX idx_terms_metadata ON terms USING GIN (metadata);\n-- Comments for terms table\nCOMMENT ON TABLE terms IS 'Canonical SQL terms and definitions for cross-dialect translation';\nCOMMENT ON COLUMN terms.canonical_term IS 'Standardized term identifier (case-sensitive)';\nCOMMENT ON COLUMN terms.category IS 'Primary classification of the term (e.g., function, operator, keyword)';\nCOMMENT ON COLUMN terms.subcategory IS 'Secondary classification for more specific grouping';\nCOMMENT ON COLUMN terms.usage_context IS 'Additional context about when and how to use this term';\nCOMMENT ON COLUMN terms.metadata IS 'Additional term properties and configuration as JSON';\n \n-- =============================================================================\n-- TABLE: translations\n-- Stores dialect-specific translations for canonical terms\n-- =============================================================================\nCREATE TABLE translations (\n    id SERIAL PRIMARY KEY,\n    term_id INTEGER NOT NULL REFERENCES terms(id) ON DELETE CASCADE,\n    dialect_id INTEGER NOT NULL REFERENCES dialects(id) ON DELETE CASCADE,\n    translated_term VARCHAR(500) NOT NULL,\n    syntax_pattern TEXT,\n    examples TEXT,\n    notes TEXT,\n    confidence_level INTEGER DEFAULT 100 CHECK (confidence_level >= 0 AND confidence_level <= 100),\n    is_active BOOLEAN NOT NULL DEFAULT true,\n    metadata JSONB,\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE(term_id, dialect_id)\n);\n \n-- Indexes for translations table\nCREATE INDEX idx_translations_term ON translations (term_id);\nCREATE INDEX idx_translations_dialect ON translations (dialect_id);\nCREATE INDEX idx_translations_active ON translations (is_active);\nCREATE INDEX idx_translations_confidence ON translations (confidence_level);\nCREATE INDEX idx_translations_term_dialect ON translations (term_id, dialect_id);\nCREATE INDEX idx_translations_metadata ON translations USING GIN (metadata);\n-- Comments for translations table\nCOMMENT ON TABLE translations IS 'Dialect-specific translations for canonical terms with caching support';\nCOMMENT ON COLUMN translations.translated_term IS 'The dialect-specific equivalent of the canonical term';\nCOMMENT ON COLUMN translations.syntax_pattern IS 'Template or pattern showing how to use the translated term';\nCOMMENT ON COLUMN translations.examples IS 'Example usage in the target dialect';\nCOMMENT ON COLUMN translations.confidence_level IS 'Translation accuracy confidence (0-100)';\nCOMMENT ON COLUMN translations.metadata IS 'Additional translation properties and caching hints as JSON';\n \n-- =============================================================================\n-- TABLE: artifacts\n-- Stores generated SQL artifacts and their metadata\n-- =============================================================================\nCREATE TABLE artifacts (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(200) NOT NULL,\n    artifact_type VARCHAR(50) NOT NULL,\n    source_dialect_id INTEGER REFERENCES dialects(id) ON DELETE SET NULL,\n    target_dialect_id INTEGER REFERENCES dialects(id) ON DELETE SET NULL,\n    original_sql TEXT,\n    translated_sql TEXT,\n    translation_summary TEXT,\n    status VARCHAR(20) NOT NULL DEFAULT 'draft',\n    metadata JSONB,\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n-- Indexes for artifacts table\nCREATE INDEX idx_artifacts_name ON artifacts (name);\nCREATE INDEX idx_artifacts_type ON artifacts (artifact_type);\nCREATE INDEX idx_artifacts_source_dialect ON artifacts (source_dialect_id);\nCREATE INDEX idx_artifacts_target_dialect ON artifacts (target_dialect_id);\nCREATE INDEX idx_artifacts_status ON artifacts (status);\nCREATE INDEX idx_artifacts_created ON artifacts (created_at);\nCREATE INDEX idx_artifacts_metadata ON artifacts USING GIN (metadata);\n-- Comments for artifacts table\nCOMMENT ON TABLE artifacts IS 'Generated SQL artifacts from translation processes';\nCOMMENT ON COLUMN artifacts.name IS 'User-friendly name for the artifact';\nCOMMENT ON COLUMN artifacts.artifact_type IS 'Type of artifact (e.g., script, procedure, schema)';\nCOMMENT ON COLUMN artifacts.original_sql IS 'Source SQL before translation';\nCOMMENT ON COLUMN artifacts.translated_sql IS 'SQL after dialect translation';\nCOMMENT ON COLUMN artifacts.translation_summary IS 'Summary of changes made during translation';\nCOMMENT ON COLUMN artifacts.status IS 'Artifact status (draft, validated, deployed, etc.)';\nCOMMENT ON COLUMN artifacts.metadata IS 'Additional artifact properties and processing metadata as JSON';\n-- =============================================================================\n-- EXAMPLE DATA\n-- Sample dialects and basic term translations\n-- =============================================================================\n \n-- Insert sample dialects\nINSERT INTO dialects (name, display_name, version, description, metadata) VALUES\n('postgresql', 'PostgreSQL', '13+', 'PostgreSQL database system', '{\"features\": [\"arrays\", \"jsonb\", \"cte\"], \"url\": \"https:\/\/postgresql.org\"}'),\n('sqlserver', 'Microsoft SQL Server', '2019+', 'Microsoft SQL Server database system', '{\"features\": [\"cte\", \"windowing\", \"xml\"], \"url\": \"https:\/\/microsoft.com\/sql\"}'),\n('mysql', 'MySQL', '8.0+', 'MySQL database system', '{\"features\": [\"json\", \"cte\"], \"url\": \"https:\/\/mysql.com\"}');\n-- Insert sample terms\nINSERT INTO terms (canonical_term, category, subcategory, description, usage_context) VALUES\n('CONCAT', 'function', 'string', 'Concatenates two or more strings together', 'String manipulation and formatting'),\n('ISNULL', 'function', 'conditional', 'Tests if a value is NULL and returns alternative', 'NULL handling and conditional logic'),\n('LIMIT', 'clause', 'query', 'Limits the number of rows returned by a query', 'Result set pagination and performance');\n-- Insert sample translations\nINSERT INTO translations (term_id, dialect_id, translated_term, syntax_pattern, examples, confidence_level) VALUES\n-- CONCAT translations\n(1, 1, 'CONCAT', 'CONCAT(string1, string2, ...)', 'SELECT CONCAT(''Hello'', '' '', ''World'');', 100),\n(1, 2, 'CONCAT', 'CONCAT(string1, string2, ...)', 'SELECT CONCAT(''Hello'', '' '', ''World'');', 100),\n(1, 3, 'CONCAT', 'CONCAT(string1, string2, ...)', 'SELECT CONCAT(''Hello'', '' '', ''World'');', 100),\n-- ISNULL translations\n(2, 1, 'COALESCE', 'COALESCE(value, replacement)', 'SELECT COALESCE(column, ''N\/A'') FROM table;', 95),\n(2, 2, 'ISNULL', 'ISNULL(value, replacement)', 'SELECT ISNULL(column, ''N\/A'') FROM table;', 100),\n(2, 3, 'IFNULL', 'IFNULL(value, replacement)', 'SELECT IFNULL(column, ''N\/A'') FROM table;', 100),\n-- LIMIT translations\n(3, 1, 'LIMIT', 'LIMIT count [OFFSET offset]', 'SELECT * FROM table LIMIT 10 OFFSET 20;', 100),\n(3, 2, 'TOP', 'TOP (count)', 'SELECT TOP (10) * FROM table ORDER BY id OFFSET 20 ROWS;', 90),\n(3, 3, 'LIMIT', 'LIMIT [offset,] count', 'SELECT * FROM table LIMIT 20, 10;', 100);\n \n-- =============================================================================\n-- GRANTS AND PERMISSIONS\n-- =============================================================================\n-- Grant schema usage (replace 'dbrosetta_owner' with actual role)\n-- GRANT USAGE ON SCHEMA dbrosetta TO dbrosetta_owner;\n-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA dbrosetta TO dbrosetta_owner;\n-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA dbrosetta TO dbrosetta_owner;\n \n-- =============================================================================\n-- SCHEMA VALIDATION\n-- =============================================================================\n-- Verify schema creation\nSELECT\n    schemaname,\n    tablename,\n    tableowner\nFROM pg_tables\nWHERE schemaname = 'dbrosetta'\nORDER BY tablename;\n-- Verify sample data\nSELECT\n    d.name as dialect,\n    t.canonical_term,\n    tr.translated_term,\n    tr.confidence_level\nFROM dialects d\nJOIN translations tr ON d.id = tr.dialect_id\nJOIN terms t ON tr.term_id = t.id\nORDER BY t.canonical_term, d.name;<\/pre><\/div>\n<\/details>\n\n\n\n
<\/p>\n\n\n\n
That’s a reasonable starting point. It also seeded sensible cross-dialect examples \u2014 ISNULL<\/code> in SQL Server maps to COALESCE<\/a><\/code> in PostgreSQL and IFNULL<\/code> in MySQL, each with a confidence score and an example query. I didn’t ask for confidence scores. It added them anyway. I’m leaving them in for now because they’re useful, but it’s a small reminder that the AI is making product decisions whenever you give it room. <\/p>\n\n\n\n
(The full schema, with all four tables, indexes and seeds, is in the repo<\/a>.)<\/em><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Accelerate and simplify database development with Redgate<\/h3>\n                
\n                                            Automate time-consuming tasks and support consistent workflows.                                    <\/div>\n            <\/div>\n                                            Learn more<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
<\/p>\n\n\n\n
What I’m less keen on is the indexing. There’s an index on practically every column that could be filtered or joined on, including separate single-column indexes for fields that only appear together in composite lookups. As a starting point on day one of a project with no production workload yet, it’s defensible. <\/p>\n\n\n\n
However, as a pattern to ship, it’s the kind of thing you’d flag in code review: this is going to cost you on writes, and most of these will never be used<\/em>. We’ll see whether it bites later.<\/p>\n\n\n\n
Copilot critiques Copilot<\/h2>\n\n\n\n
This is where it got genuinely interesting. After confirming the script ran, I fed the whole DDL<\/a> back to Copilot and asked it to validate the result against the acceptance criteria we’d set in part one. It came back with eight issues. <\/p>\n\n\n\n
The eight issues it presented (edited for length):<\/h3>\n\n\n
\n
    \n
  1. SERIAL<\/code> works on PostgreSQL 11+, but GENERATED ALWAYS AS IDENTITY<\/code> is more portable and avoids implicit sequence ownership issues on managed platforms.

    <\/li>\n\n\n\n
  2. updated_at<\/code> defaults to CURRENT_TIMESTAMP<\/code> but never changes on updates. Add triggers or maintain it in application code.

    <\/li>\n\n\n\n
  3. canonical_term<\/code> is unique but case-sensitive. If lookups should be case-insensitive, add a unique index on lower(canonical_term)<\/code> or store a normalised column.

    <\/li>\n\n\n\n
  4. Foreign keys and unique constraints aren’t named. Naming them (fk_translations_term<\/code>, etc.) improves clarity in migrations and error messages.

    <\/li>\n\n\n\n
  5. Pattern-length limits are arbitrary. Consider TEXT<\/code> instead of VARCHAR(500)<\/code> for fields like translated_term<\/code> and examples<\/code>.

    <\/li>\n\n\n\n
  6. The validation SELECT<\/code> statements at the end of the script are fine locally but noisy in CI.

    <\/li>\n\n\n\n
  7. Acceptance criteria mention prompt_version<\/code> and model_settings<\/code> in metadata. The schema doesn’t enforce them. Consider JSON Schema validation in the app layer, or a CHECK<\/code> constraint.

    <\/li>\n\n\n\n
  8. SET search_path TO dbrosetta, public<\/code> is fine interactively but should be avoided in scripts applied by CI or applications.<\/li>\n<\/ol>\n<\/div>\n\n\n
    Analyzing Copilot’s validation effort<\/h3>\n\n\n\n
    Most of those are good calls. I accepted six of them. I overrode two: the updated_at<\/code> trigger (overkill for v0, do it in app code when there’s an app) and the in-script validation SELECT<\/code>s (I’d rather pull those out entirely than maintain them as test artifacts). <\/p>\n\n\n\n
    I’m a little shocked Copilot didn’t flag the indexing strategy \u2014 every other surface gets a recommendation, but the index-everything pattern got a pass. That tells me something about what’s overrepresented in the training data. Indexes that exist look correct. Indexes that shouldn’t<\/em> exist look like prudence.<\/p>\n\n\n\n
    I asked Copilot to regenerate the script with the six accepted changes \u2014 and only<\/em> those changes. It did. The revised script applied cleanly. Database confirmed.<\/p>\n\n\n\n
    Mission accomplished – we have a database. And I’m pretty excited about it.<\/p>\n\n\n\n
    The awkward question<\/h2>\n\n\n\n
    Here’s the thing I haven’t fully resolved. I am, by background, a database person. I have opinions on schema design. When I accepted six of Copilot’s eight suggestions and rejected two, I did that on the basis of judgement formed over thirty-odd years of doing this work.<\/p>\n\n\n\n
    Is my intervention biasing the experiment towards a result I’m comfortable with?<\/strong><\/p>\n\n\n\n
    Yes. Obviously yes. The bigger question is whether that’s a problem.<\/p>\n\n\n\n
    If the experiment is “can an LLM build a working database with no human intervention,”<\/em> then every override is a contamination. If the experiment is “can an LLM build a working database faster than I could on my own, with me as a reviewer rather than an author,”<\/em>  then my overrides are exactly the point \u2014 they’re what makes<\/em> the experiment honest.<\/p>\n\n\n\n
    I’m going with the second framing, because I think the first one is a fantasy that no real engineering organization should be running.<\/p>\n\n\n\n
    The harder question this raises…<\/h3>\n\n\n\n
    Without my reviewing eye, what would have shipped? A database with an index on every column, no consistent foreign key naming, case-sensitive lookups in a field that almost certainly wants to be case-insensitive, and an updated_at<\/code> field that lies about when rows were last updated.<\/p>\n\n\n\n
    None of those are bugs. They all work. But they’re also all things that somebody is going to swear at five years from now, when this database is full of real data and somebody is trying to figure out why a particular query is slow, or why two rows that look identical are being treated as different.<\/p>\n\n\n\n
    What I learned (and what I’d now do differently)<\/h2>\n\n\n
    \n