aurora-dsql — Skillopedia

Amazon Aurora DSQL Overview Aurora DSQL is a serverless, PostgreSQL-compatible distributed SQL database. This skill provides direct database interaction via scripts and PostgreSQL drivers, schema management, migration support, multi-tenant patterns, and query-plan explainability. Key capabilities: - Direct query execution via with generated IAM auth tokens (see ) - Schema management with DSQL constraints (one DDL per transaction, async indexes) - Safe data migration (column-level, constraint-level, MySQL→DSQL) - Multi-tenant isolation via + parameterized SQL - IAM-based authentication with a…

LIMIT 100;\n```\n\n### Recovery from Failed Migration\n\n```sql\n-- Check table state\nSELECT table_name FROM information_schema.tables\nWHERE table_name IN ('target_table', 'target_table_new');\n```\n\n- **Both tables exist:** Original safe → `DROP TABLE IF EXISTS target_table_new` and restart\n- **Only new table exists:** Verify count, then complete rename\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":2322,"content_sha256":"b3bf07902c5bb1d935be2c9f770b115e780510ba1ac238950218c38fd355333a"},{"filename":"references/ddl-migrations/column-operations.md","content":"# DDL Migrations: Column Operations\n\nStep-by-step migration patterns for column-level changes using the Table Recreation Pattern.\n\n**MUST read [overview.md](overview.md) first** for destructive operation warnings and the common verify & swap pattern.\n\n---\n\n## DROP COLUMN Migration\n\n**Goal:** Remove a column from an existing table.\n\n### Pre-Migration Validation\n\n```sql\nSELECT COUNT(*) as total_rows FROM target_table;\nSELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;\n```\n\n### Migration Steps\n\n#### Step 1: Create new table excluding the column\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n tenant_id VARCHAR(255) NOT NULL,\n kept_column1 VARCHAR(255),\n kept_column2 INTEGER\n -- dropped_column is NOT included\n);\n```\n\n#### Step 2: Migrate data\n\n```sql\nINSERT INTO target_table_new (id, tenant_id, kept_column1, kept_column2)\nSELECT id, tenant_id, kept_column1, kept_column2\nFROM target_table;\n```\n\nFor tables > 3,000 rows, use [Batched Migration Pattern](batched-migration.md).\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## ALTER COLUMN TYPE Migration\n\n**Goal:** Change a column's data type.\n\n### Pre-Migration Validation\n\n**MUST validate data compatibility BEFORE migration** to prevent data loss.\n\n```sql\n-- Example: VARCHAR to INTEGER - check for non-numeric values\nSELECT COUNT(*) as invalid_count FROM target_table\nWHERE column_to_change !~ '^-?[0-9]+

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

;\n-- MUST abort if invalid_count > 0\n\n-- Show problematic rows\nSELECT id, column_to_change FROM target_table\nWHERE column_to_change !~ '^-?[0-9]+

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

LIMIT 100;\n```\n\n### Data Type Compatibility Matrix\n\n| From Type | To Type | Validation |\n| --------- | ---------- | ------------------------------------------------------- |\n| VARCHAR | INTEGER | MUST validate all values are numeric |\n| VARCHAR | BOOLEAN | MUST validate values are 'true'/'false'/'t'/'f'/'1'/'0' |\n| INTEGER | VARCHAR | Safe conversion |\n| TEXT | VARCHAR(n) | MUST validate max length ≤ n |\n| TIMESTAMP | DATE | Safe (truncates time) |\n| INTEGER | DECIMAL | Safe conversion |\n\n### Migration Steps\n\n#### Step 1: Create new table with changed type\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n converted_column INTEGER, -- Changed from VARCHAR\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data with type casting\n\n```sql\nINSERT INTO target_table_new (id, converted_column, other_column)\nSELECT id, CAST(converted_column AS INTEGER), other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## ALTER COLUMN SET/DROP NOT NULL Migration\n\n**Goal:** Change a column's nullability constraint.\n\n### Pre-Migration Validation (for SET NOT NULL)\n\n```sql\nSELECT COUNT(*) as null_count FROM target_table\nWHERE target_column IS NULL;\n-- MUST ABORT if null_count > 0, or plan to provide default values\n```\n\n### Migration Steps\n\n#### Step 1: Create new table with changed constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n target_column VARCHAR(255) NOT NULL, -- Changed from nullable\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data (with default for NULLs if needed)\n\n```sql\nINSERT INTO target_table_new (id, target_column, other_column)\nSELECT id, COALESCE(target_column, 'default_value'), other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## ALTER COLUMN SET/DROP DEFAULT Migration\n\n**Goal:** Add or remove a default value for a column.\n\n### Pre-Migration Validation\n\n```sql\nSELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;\n-- Identify current column definition and any existing defaults\n```\n\n### Migration Steps (SET DEFAULT)\n\n#### Step 1: Create new table with default value\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n status VARCHAR(50) DEFAULT 'pending', -- Added default\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, status, other_column)\nSELECT id, status, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n### Migration Steps (DROP DEFAULT)\n\n#### Step 1: Create new table without default\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n status VARCHAR(50), -- Removed DEFAULT\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, status, other_column)\nSELECT id, status, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5026,"content_sha256":"afc72f43a75afb72d02794377dc564b7cfb7c2186a615f7bc106254504a8625b"},{"filename":"references/ddl-migrations/constraint-operations.md","content":"# DDL Migrations: Constraint & Structural Operations\n\nStep-by-step migration patterns for constraint changes, primary key modifications, and column transformations.\n\n**MUST read [overview.md](overview.md) first** for destructive operation warnings and the common verify & swap pattern.\n\n---\n\n## ADD CONSTRAINT Migration\n\n**Goal:** Add a constraint (UNIQUE, CHECK) to an existing table.\n\n### Pre-Migration Validation\n\n**MUST validate existing data satisfies the new constraint.**\n\n```sql\n-- For UNIQUE constraint: check for duplicates\nSELECT target_column, COUNT(*) as cnt FROM target_table\nGROUP BY target_column HAVING COUNT(*) > 1 LIMIT 10;\n-- MUST ABORT if any duplicates exist\n\n-- For CHECK constraint: validate all rows pass\nSELECT COUNT(*) as invalid_count FROM target_table\nWHERE NOT (check_condition);\n-- MUST ABORT if invalid_count > 0\n```\n\n### Migration Steps\n\n#### Step 1: Create new table with the constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n email VARCHAR(255) UNIQUE, -- Added UNIQUE constraint\n age INTEGER CHECK (age >= 0), -- Added CHECK constraint\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, email, age, other_column)\nSELECT id, email, age, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## DROP CONSTRAINT Migration\n\n**Goal:** Remove a constraint (UNIQUE, CHECK) from a table.\n\n### Pre-Migration Validation\n\n```sql\n-- Identify existing constraints\nSELECT constraint_name, constraint_type\nFROM information_schema.table_constraints\nWHERE table_name = 'target_table'\n AND constraint_type IN ('UNIQUE', 'CHECK');\n```\n\n### Migration Steps\n\n#### Step 1: Create new table without the constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n email VARCHAR(255), -- Removed UNIQUE constraint\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, email, other_column)\nSELECT id, email, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## MODIFY PRIMARY KEY Migration\n\n**Goal:** Change which column(s) form the primary key.\n\n### Pre-Migration Validation\n\n**MUST validate new PK column has unique, non-null values.**\n\n```sql\n-- Check for duplicates\nSELECT new_pk_column, COUNT(*) as cnt FROM target_table\nGROUP BY new_pk_column HAVING COUNT(*) > 1 LIMIT 10;\n-- MUST ABORT if any duplicates exist\n\n-- Check for NULLs\nSELECT COUNT(*) as null_count FROM target_table\nWHERE new_pk_column IS NULL;\n-- MUST ABORT if null_count > 0\n```\n\n### Migration Steps\n\n#### Step 1: Create new table with new primary key\n\n```sql\nCREATE TABLE target_table_new (\n new_pk_column UUID PRIMARY KEY, -- New PK\n old_pk_column VARCHAR(255), -- Demoted to regular column\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (new_pk_column, old_pk_column, other_column)\nSELECT new_pk_column, old_pk_column, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))\n\n---\n\n## Column Transformations (Split/Merge)\n\n### Split Column\n\n**Goal:** Split one column into multiple (e.g., `full_name` → `first_name` + `last_name`).\n\n```sql\n-- Create new table with split columns\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n first_name VARCHAR(255),\n last_name VARCHAR(255)\n);\n\n-- Copy with transformation\nINSERT INTO target_table_new (id, first_name, last_name)\nSELECT id,\n SPLIT_PART(full_name, ' ', 1),\n SUBSTRING(full_name FROM POSITION(' ' IN full_name) + 1)\nFROM target_table;\n\n-- Verify, swap, re-index (see Common Pattern)\n```\n\n### Merge Columns\n\n**Goal:** Combine multiple columns into one (e.g., `first_name` + `last_name` → `display_name`).\n\n```sql\n-- Create new table with merged column\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n display_name VARCHAR(512)\n);\n\n-- Copy with concatenation\nINSERT INTO target_table_new (id, display_name)\nSELECT id,\n CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))\nFROM target_table;\n\n-- Verify, swap, re-index (see Common Pattern)\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":4240,"content_sha256":"e0976d03b183733eed27953e6675b3ffcf20022a3e17739d7eb83752a5cc6da1"},{"filename":"references/ddl-migrations/overview.md","content":"# DSQL DDL Migration Guide - Overview\n\nThis guide provides the **Table Recreation Pattern** for schema modifications that require rebuilding tables.\n\nFor column-level operations, see [column-operations.md](column-operations.md).\nFor constraint and structural operations, see [constraint-operations.md](constraint-operations.md).\nFor batched migration patterns, see [batched-migration.md](batched-migration.md).\n\n---\n\n## CRITICAL: Destructive Operations Warning\n\n**The Table Recreation Pattern involves DESTRUCTIVE operations that can result in DATA LOSS.**\n\nTable recreation requires dropping the original table, which is **irreversible**. If any step fails after the original table is dropped, data may be permanently lost.\n\n### Mandatory User Verification Requirements\n\nAgents MUST obtain explicit user approval before executing migrations on live tables:\n\n1. **MUST present the complete migration plan** to the user before any execution\n2. **MUST clearly state** that this operation will DROP the original table\n3. **MUST confirm** the user has a current backup or accepts the risk of data loss\n4. **MUST verify with the user** at each checkpoint before proceeding:\n - Before creating the new table structure\n - Before beginning data migration\n - Before dropping the original table (CRITICAL CHECKPOINT)\n - Before renaming the new table\n5. **MUST NOT proceed** with any destructive action without explicit user confirmation\n6. **MUST recommend** performing migrations on non-production environments first\n\n### Risk Acknowledgment\n\nBefore proceeding, the user MUST confirm:\n\n- [ ] They understand this is a destructive operation\n- [ ] They have a backup of the table data (or accept the risk)\n- [ ] They approve the agent to execute each step with verification\n- [ ] They understand the migration cannot be automatically rolled back after DROP TABLE\n\n---\n\n## Table Recreation Operations\n\nThe following ALTER TABLE operations MUST use the **Table Recreation Pattern**:\n\n| Operation | Key Approach |\n| ------------------------------ | ---------------------------------------------- |\n| DROP COLUMN | Exclude column from new table |\n| ALTER COLUMN TYPE | Cast data type in SELECT |\n| ALTER COLUMN SET/DROP NOT NULL | Change constraint in new table definition |\n| ALTER COLUMN SET/DROP DEFAULT | Define default in new table definition |\n| ADD CONSTRAINT | Include constraint in new table definition |\n| DROP CONSTRAINT | Remove constraint from new table definition |\n| MODIFY PRIMARY KEY | Define new PK, validate uniqueness first |\n| Split/Merge Columns | Use SPLIT_PART, SUBSTRING, or CONCAT in SELECT |\n\n**Note:** The following operations ARE supported directly. Each is still subject to the\none-DDL-per-transaction rule — issue each as its own `psql -c` invocation (or its own\n`BEGIN`/`COMMIT` block when scripted):\n\n- `ALTER TABLE ... RENAME COLUMN` - Rename a column\n- `ALTER TABLE ... RENAME TO` - Rename a table\n- `ALTER TABLE ... ADD COLUMN` - Add a new column\n\n---\n\n## CREATE INDEX ASYNC Syntax (DSQL)\n\nDSQL accepts a narrow subset of standard PostgreSQL `CREATE INDEX` syntax. The skill enforces\n`CREATE INDEX ASYNC` everywhere; additional clauses behave as follows (validated against a live\nDSQL cluster):\n\n| Clause | DSQL behavior |\n| ------------------------------------- | --------------------------------------------------------------------------------------------- |\n| `IF NOT EXISTS` | Accepted |\n| `INCLUDE (\u003ccolumns>)` | Accepted (covering indexes) |\n| `UNIQUE` (`CREATE UNIQUE INDEX ASYNC`)| Accepted |\n| `USING \u003cmethod>` (btree/hash/gin/...) | **Rejected**: `ERROR: USING not supported for CREATE INDEX` — DSQL is btree-only |\n| `WHERE \u003cpredicate>` | **Rejected**: `ERROR: WHERE not supported for CREATE INDEX` — partial indexes are unavailable |\n| `CONCURRENTLY` | **Rejected**: `ERROR: CONCURRENTLY not supported for CREATE INDEX` — use `ASYNC` instead (non-blocking by design) |\n\nWithout `ASYNC`, DSQL rejects with `ERROR: unsupported mode. please use CREATE INDEX ASYNC.` —\nuseful to grep for in failure logs.\n\nIf the migration source (e.g., a vanilla PostgreSQL dump) relies on partial indexes or non-btree\naccess methods, the pattern MUST be rewritten — denormalize via a filter column, or add a CHECK\nconstraint and a covering composite index. Document the workaround in the migration plan.\n\n---\n\n## Table Recreation Pattern Overview\n\nMUST follow this sequence with user verification at each step:\n\n1. **Plan & Confirm** - MUST present migration plan and obtain user approval to proceed\n2. **Validate** - Check data compatibility with new structure; MUST report findings to user\n3. **Create** - Create new table with desired structure; MUST verify with user before execution\n4. **Migrate** - Copy data (batched for tables > 3,000 rows); MUST report progress to user\n5. **Verify** - Confirm row counts match; MUST present comparison to user\n6. **Swap** - CRITICAL: MUST obtain explicit user confirmation before DROP TABLE\n7. **Re-index** - Recreate indexes using ASYNC; MUST confirm completion with user\n\n### Transaction Rules\n\nDefaults below; verify against the live limits via the AWS MCP Server's `aws___search_documentation` if available (`aurora dsql transaction limits`), or read the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/) directly:\n\n- **MUST batch** migrations exceeding 3,000 row mutations\n- **PREFER batches of 500-1,000 rows** for optimal throughput\n- **MUST respect** 10 MiB data size per transaction\n- **MUST respect** 5-minute transaction duration\n\n---\n\n## Common Verify & Swap Pattern\n\nAll migrations end with this pattern (referenced in [column-operations.md](column-operations.md) and [constraint-operations.md](constraint-operations.md)).\n\n**CRITICAL: MUST obtain explicit user confirmation before DROP TABLE step.**\n\n```sql\n-- MUST verify counts match\nSELECT COUNT(*) FROM target_table;\nSELECT COUNT(*) FROM target_table_new;\n\n-- CHECKPOINT: MUST present count comparison to user and obtain confirmation\n-- Agent MUST display: \"Original table has X rows, new table has Y rows.\n-- Proceeding will DROP the original table. This action is IRREVERSIBLE.\n-- Do you want to proceed? (yes/no)\"\n-- MUST NOT proceed without explicit \"yes\" confirmation\n\n-- MUST swap tables (DESTRUCTIVE - requires user confirmation above).\n-- Each DDL below MUST run in its own transaction (DSQL: one DDL per\n-- transaction). Run as separate `psql-connect.sh --command` calls,\n-- or as separate transactions in your driver:\nDROP TABLE target_table;\nALTER TABLE target_table_new RENAME TO target_table;\n\n-- MUST recreate indexes (each in its own transaction; CREATE INDEX ASYNC\n-- is non-blocking and required by DSQL):\nCREATE INDEX ASYNC idx_target_tenant ON target_table(tenant_id);\n```\n\n### Recovery — Row Counts Do Not Match\n\nWhen `target_table_new` has fewer rows than `target_table`, treat the migration as incomplete.\nThe original table still holds the authoritative data, so recovery is always possible — **MUST NOT**\nproceed with `DROP TABLE` until the counts agree.\n\n1. **Diagnose** — find the missing rows by comparing ranges (for cursor-based migrations, query\n `target_table` for IDs greater than `MAX(id)` in `target_table_new`; for OFFSET-based, check\n which batch dropped rows by re-running the SELECT portion of each batch and comparing counts).\n2. **Retry the missing batches** — insert only the gap rows into `target_table_new`. Filter out\n already-migrated rows to avoid PK collisions (which would roll back the entire batch):\n\n ```sql\n -- Cursor-based: only insert rows beyond what was already migrated\n INSERT INTO target_table_new (id, col1, col2)\n SELECT id, col1, col2 FROM target_table\n WHERE id > (SELECT COALESCE(MAX(id), 0) FROM target_table_new)\n ORDER BY id LIMIT 1000;\n\n -- For non-sequential gaps, use NOT EXISTS:\n INSERT INTO target_table_new (id, col1, col2)\n SELECT id, col1, col2 FROM target_table\n WHERE NOT EXISTS (\n SELECT 1 FROM target_table_new WHERE target_table_new.id = target_table.id\n )\n ORDER BY id LIMIT 1000;\n ```\n\n3. **If a type cast or constraint rejected rows** — migration cannot complete until the data is\n reconciled. Fix the source data in `target_table` (or adjust the new table's constraint),\n then re-run the missing batches.\n4. **Escape hatch** — if diagnosis stalls, drop `target_table_new` and restart the migration\n from a clean slate. The original table is untouched, so no data is at risk.\n\nRe-run the count comparison after each retry. Only proceed to `DROP TABLE` once\n`COUNT(*)` matches exactly.\n\n---\n\n## Best Practices Summary\n\n### User Verification (CRITICAL)\n\n- **MUST present** complete migration plan to user before any execution\n- **MUST obtain** explicit user confirmation before DROP TABLE operations\n- **MUST verify** with user at each checkpoint during migration\n- **MUST NOT** proceed with destructive actions without explicit user approval\n- **MUST recommend** testing migrations on non-production data first\n- **MUST confirm** user has backup or accepts data loss risk\n\n### Technical Requirements\n\n- **MUST validate** data compatibility before type changes\n- **MUST batch** tables exceeding 3,000 rows\n- **MUST verify** row counts before and after migration\n- **MUST recreate** indexes after table swap using ASYNC\n- **MUST NOT** drop original table until new table is verified\n- **PREFER** cursor-based batching for very large tables\n- **PREFER** batches of 500-1,000 rows for optimal throughput\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":10157,"content_sha256":"f26419c13dc853502f94e5596c1d1f0054b46fd7805c3915f9207dbd1d314f79"},{"filename":"references/development-guide.md","content":"# DSQL Development Guide\n\nDSQL is the **PREFERRED** relational database. This decision was made for fast serverless distributed SQL,\neffortless scaling, multi-region viability, among other advantages.\n\n---\n\n## Best Practices\n\n- **SHOULD read guidelines first** - Check [development-guide.md](development-guide.md) before making schema changes\n- **SHOULD use preferred language patterns** - Check [language.md](language.md)\n- **SHOULD Execute queries directly** - PREFER `psql` via [`scripts/psql-connect.sh`](../scripts/psql-connect.sh) for ad-hoc queries\n- **REQUIRED: Follow DDL Guidelines** - Refer to [DDL Rules](#schema-ddl-rules)\n- **SHALL repeatedly generate fresh tokens** - Refer to [Connection Limits](auth/authentication-guide.md#connection-rules)\n- **ALWAYS use ASYNC indexes** - `CREATE INDEX ASYNC` is mandatory\n- **MUST Serialize arrays/JSON as TEXT** - Store arrays/JSON as TEXT (comma separated, JSON.stringify)\n- **ALWAYS Batch within row limit** - maintain transaction limits (defaults: 3,000 rows, 10 MiB, 5 minutes — verify via the AWS MCP Server's `aws___search_documentation` if available, or check the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/) directly: `aurora dsql transaction limits`)\n- **REQUIRED: Sanitize SQL inputs with allowlists, regex, and quote escaping** - See [Input Validation](input-validation.md#rules)\n- **MUST follow correct Application Layer Patterns** - when multi-tenant isolation or application referential integrity are required; refer to [Application Layer Patterns](#application-layer-patterns)\n- **REQUIRED use DELETE for truncation** - DELETE is the only supported operation for truncation\n- **SHOULD test any migrations** - Verify DDL on dev clusters before production\n- **Plan for Horizontal Scale** - DSQL is designed to optimize for massive scales without latency drops; refer to [Horizontal Scaling](auth/scaling-guide.md)\n- **SHOULD use connection pooling in production applications** - Refer to [Connection Pooling](auth/authentication-guide.md#connection-pooling-recommended)\n- **SHOULD debug with the troubleshooting guide:** - Always refer to the resources and guidelines in [troubleshooting.md](troubleshooting.md)\n- **ALWAYS use scoped roles for applications** - Create database roles with `dsql:DbConnect`; refer to [Access Control](access-control.md)\n\n---\n\n## Detailed References\n\n- **[authentication-guide.md](auth/authentication-guide.md)** — IAM auth, token management, secrets, SSL/TLS, connection pooling, audit logging, access control\n- **[connectivity-tools.md](auth/connectivity-tools.md)** — Database drivers, ORMs, adapters, and data loading tools\n- **[scaling-guide.md](auth/scaling-guide.md)** — Horizontal scaling strategy, batch optimization, hot key avoidance, identifier types\n\n---\n\n## Operational Rules\n\n### Query Execution\n\n**For Ad-Hoc Queries and Data Exploration:**\n\n- MUST ALWAYS Execute via `psql` (use [`scripts/psql-connect.sh`](../scripts/psql-connect.sh) `--command` for single statements, `--script` for multi-statement files) or your driver's read path\n- SHOULD Return results immediately\n\n**Writing Scripts REQUIRES at least 1 of:**\n\n- Permanent migrations in database\n- Reusable utilities\n- EXPLICIT user request\n\n---\n\n### Schema Design Rules\n\n- MUST use **simple PostgreSQL types:** VARCHAR, TEXT, INTEGER, BOOLEAN, TIMESTAMP\n- MUST store arrays as TEXT (comma-separated is recommended)\n- MUST store JSON objects as TEXT (JSON.stringify)\n- ALWAYS include tenant_id in tables for multi-tenant isolation\n- SHOULD create async indexes for tenant_id and common query patterns\n\n### Schema (DDL) Rules\n\n- REQUIRED: **at most one DDL statement** per operation\n- ALWAYS separate schema (DDL) and data (DML) changes\n- MUST use **`CREATE INDEX ASYNC`:** No synchronous creation (defaults: max 24 indexes per table, 8 columns per index — verify via the AWS MCP Server's `aws___search_documentation` if available, or check the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/): `aurora dsql index limits`)\n - MAXIMUM: **24 indexes per table**\n - MAXIMUM: **8 columns per index**\n- **Asynchronous Execution:** DDL ALWAYS runs asynchronously\n- To add a column with DEFAULT or NOT NULL:\n 1. MUST issue ADD COLUMN specifying only the column name and data type\n 2. MUST then issue UPDATE to populate existing rows\n 3. MAY then issue ALTER COLUMN to apply the constraint\n- MUST issue a **separate ALTER TABLE statement for each column** modification.\n\n### Transaction Rules\n\nDefaults below; verify against the live limits via the AWS MCP Server's `aws___search_documentation` if available (`aurora dsql transaction limits`), or read the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/) directly:\n\n- SHOULD modify **at most 3,000 rows** per transaction\n- SHOULD have maximum **10 MiB data size** per write transaction\n- SHOULD expect **5-minute** transaction duration\n- ALWAYS expect repeatable read isolation\n\n---\n\n### Application-Layer Patterns\n\n**MANDATORY for Application Referential Integrity:**\nIf foreign key constraints (application referential integrity) are required,\nimplement the following pattern instead:\n\n- MUST validate parent references before INSERT\n- MUST check for dependents before DELETE\n- MUST implement cascade logic in application code\n- MUST handle orphaned records in application layer\n\n**MANDATORY for Multi-Tenant Isolation:**\n\n- tenantId is ALWAYS first parameter in repository methods\n- ALL queries include WHERE tenant_id = ?\n- ALWAYS validate tenant ownership before operations\n- ALWAYS reject cross-tenant data access\n\n### Migration Patterns\n\n- REQUIRED: One DDL statement per migration step\n- SHOULD Use IF NOT EXISTS for idempotency\n- SHOULD Add column first, then UPDATE with defaults\n- REQUIRED: Each DDL executes separately\n\n---\n\n## Quick Reference\n\n### Schema Operations\n\n```sql\nCREATE INDEX ASYNC idx_name ON table(column); ← ALWAYS ASYNC\nALTER TABLE t ADD COLUMN c VARCHAR(50); ← ONE AT A TIME\nALTER TABLE t ADD COLUMN c2 INTEGER; ← SEPARATE STATEMENT\nUPDATE table SET c = 'default' WHERE c IS NULL; ← AFTER ADD COLUMN\n```\n\n### Supported Data Types\n\n```\nVARCHAR, TEXT, INTEGER, DECIMAL, BOOLEAN, TIMESTAMP, UUID\n```\n\n### Supported Key\n\n```\nPRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT (in CREATE TABLE)\n```\n\nJoin on any keys; DSQL enforces PRIMARY KEY, UNIQUE, NOT NULL, and CHECK constraints\nat the database level. Foreign-key referential integrity must be enforced in the\napplication layer (see Application-Layer Patterns above).\n\n### Transaction Requirements\n\nDefaults below; verify against the live limits via the AWS MCP Server's `aws___search_documentation` if available (`aurora dsql transaction limits`), or read the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/) directly:\n\n```\nRows: 3,000 max\nSize: 10 MiB max\nDuration: 5 minutes max\nIsolation: Repeatable Read (fixed)\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7008,"content_sha256":"773f5db2543548fd9a4b2bbf169f26ed45d64e9330101f57330e8a7fee879b31"},{"filename":"references/documentation-tools.md","content":"# AWS Documentation Tools (via the AWS MCP Server)\n\nPart of the [Aurora DSQL Skill](../SKILL.md). When the [AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html)\nis connected, these tools are available for AWS documentation lookups, including DSQL service docs.\n\nThe canonical tool list lives at\n[Understanding the MCP Server tools](https://docs.aws.amazon.com/aws-mcp/latest/userguide/understanding-mcp-server-tools.html);\nthe entries below cover the ones load-bearing for DSQL workflows.\n\n---\n\n## AWS Knowledge Tools\n\n### `aws___search_documentation`\n\n**Use for:** finding relevant AWS documentation, looking up DSQL features, troubleshooting.\n\nSearch across all AWS documentation, including API references, best practices, service guides,\nand skills. Use the topic filter to search skills exclusively, or see\nskills alongside general knowledge search results. Find relevant information from multiple AWS\nknowledge sources.\n\n**Common DSQL queries:**\n\n- `aurora dsql transaction limits`\n- `aurora dsql index limits`\n- `aurora dsql connection limits`\n- `aurora dsql authentication token`\n\n### `aws___read_documentation`\n\n**Use for:** retrieving the full markdown of a specific AWS documentation page when a search\nresult snippet isn't enough. Pass the URL of the docs page you want to read.\n\n### `aws___recommend`\n\n**Use for:** getting content recommendations for a specific AWS documentation page based on\nrelated topics and commonly viewed content.\n\n### `aws___retrieve_skill`\n\n**Use for:** retrieving the full content of a domain-specific AWS skill (workflows, context, best\npractices, decision frameworks, step-by-step procedures). Discover available skills via\n`aws___search_documentation` first, then call `aws___retrieve_skill` with the skill name.\n\n### `aws___list_regions`\n\n**Use for:** enumerating the AWS region identifiers and names. Pair with\n`aws___get_regional_availability` when you need to confirm DSQL availability in a specific\nregion before recommending an architecture.\n\n### `aws___get_regional_availability`\n\n**Use for:** checking whether DSQL (or a feature you depend on) is available in the user's\ntarget region before recommending an architecture. Supports per-service / per-feature\navailability checks.\n\n---\n\n## AWS API Tools\n\n### `aws___call_aws`\n\n**Use for:** authenticated AWS API calls. Useful for `dsql:CreateCluster`, `dsql:GetCluster`,\n`dsql:ListClusters`, `dsql:DeleteCluster`, etc., when the assistant should drive cluster\nlifecycle directly. Long-running calls return a task ID — poll it with `aws___get_tasks`.\n\n### `aws___run_script`\n\n**Use for:** sandboxed Python with AWS API access. Useful for multi-step or parallel workflows\n(\"list every cluster in the region, check whose tags include `Environment=eval`, then call\n`GetCluster` on the matching ones\"). Long-running scripts return a task ID — poll with\n`aws___get_tasks`.\n\n### `aws___get_tasks`\n\n**Use for:** polling the status of long-running tasks started by `aws___call_aws` or\n`aws___run_script`. **MUST** call when a previous tool invocation returned a task ID with a\nworking status — without this, the agent can't observe completion or final output.\n\n### `aws___get_presigned_url`\n\n**Use for:** generating pre-signed Amazon S3 URLs for uploading/downloading files. Relevant\nwhen a DSQL workflow involves S3-hosted SQL scripts, exported data, or the bulk-loading guide.\n\n---\n\nFor tool-call shape, parameter details, and per-assistant invocation, see the official\n[AWS MCP Server tool reference](https://docs.aws.amazon.com/aws-mcp/latest/userguide/understanding-mcp-server-tools.html).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3622,"content_sha256":"1057ebc9f8cf0187319ddf35ec548ce3fc84bbfa1e4a67cbafb0831a80868834"},{"filename":"references/dsql-examples.md","content":"# Aurora DSQL Implementation Examples\n\nThis file contains DSQL integration code examples; only load this when actively implementing database code.\n\nFor language-specific framework selection, recommendations, and examples see [language.md](./language.md).\n\nFor developer rules, see [development-guide.md](./development-guide.md).\n\nFor additional samples, including in alternative language and driver support, refer to the\nofficial [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html).\n\n---\n\n## Detailed Examples\n\nLoad the relevant file for the specific implementation pattern you need:\n\n- **[examples/connection.md](examples/connection.md)** — Ad-hoc queries with psql, connection management, token generation\n- **[examples/schema.md](examples/schema.md)** — Table creation, index creation, column modifications\n- **[examples/data-operations.md](examples/data-operations.md)** — Basic CRUD, batch processing, concurrent inserts\n- **[examples/migrations.md](examples/migrations.md)** — Migration execution patterns\n- **[examples/patterns.md](examples/patterns.md)** — Multi-tenant isolation, referential integrity, sequences, data serialization\n\n## References\n\n- **Development Guide:** [development-guide.md](./development-guide.md)\n- **Language Guide:** [language.md](./language.md)\n- **Onboarding Guide:** [onboarding.md](./onboarding.md)\n- **AWS Documentation:** [DSQL User Guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/)\n- **Sample Code:** [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1637,"content_sha256":"bcebd4524d2991a2946d2c40e00c715891d785f9066b3558a55fa5a0c49ee4e6"},{"filename":"references/examples/connection.md","content":"# DSQL Examples: Connection & Ad-Hoc Queries\n\nPart of [Aurora DSQL Implementation Examples](../dsql-examples.md).\n\n---\n\n## Ad-Hoc Queries with psql\n\nPREFER connecting with a scoped database role using `generate-db-connect-auth-token`.\nReserve `admin` for role and schema setup only. See [access-control.md](../access-control.md).\n\n```bash\n# PREFERRED: Execute queries with a scoped role\nPGPASSWORD=\"$(aws dsql generate-db-connect-auth-token \\\n --hostname ${CLUSTER}.dsql.${REGION}.on.aws \\\n --region ${REGION})\" \\\npsql -h ${CLUSTER}.dsql.${REGION}.on.aws -U app_readwrite -d postgres \\\n -c \"SELECT COUNT(*) FROM objectives WHERE tenant_id = 'tenant-123';\"\n\n# Admin only — for role/schema setup\nPGPASSWORD=\"$(aws dsql generate-db-connect-admin-auth-token \\\n --hostname ${CLUSTER}.dsql.${REGION}.on.aws \\\n --region ${REGION})\" \\\nPGAPPNAME=\"\u003capp-name>/\u003cmodel-id>\" \\\npsql -h ${CLUSTER}.dsql.${REGION}.on.aws -U admin -d postgres\n```\n\n---\n\n## Connection Management\n\n### RECOMMENDED: DSQL Connector\n\nSource: Adapted from the JavaScript connector samples listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```javascript\nimport { AuroraDSQLPool } from \"@aws/aurora-dsql-node-postgres-connector\";\n\nfunction createPool(clusterEndpoint, user) {\n return new AuroraDSQLPool({\n host: clusterEndpoint,\n user: user,\n application_name: \"\u003capp-name>/\u003cmodel-id>\",\n max: 10,\n idleTimeoutMillis: 30000,\n connectionTimeoutMillis: 10000,\n });\n}\n\nasync function example() {\n const pool = createPool(process.env.CLUSTER_ENDPOINT, process.env.CLUSTER_USER);\n\n try {\n const result = await pool.query(\"SELECT $1::int as value\", [42]);\n console.log(`Result: ${result.rows[0].value}`);\n } finally {\n await pool.end();\n }\n}\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1810,"content_sha256":"4e37a1586c7c593a0574a3ca9a3b882812ae54c13e811cd0152d6887f69e3699"},{"filename":"references/examples/data-operations.md","content":"# DSQL Examples: Data Operations\n\nPart of [Aurora DSQL Implementation Examples](../dsql-examples.md).\n\n---\n\n## Data Operations: Basic CRUD\n\nSource: Adapted from the quickstart samples listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```sql\n-- Insert with transaction\nBEGIN;\nINSERT INTO owner (name, city) VALUES\n ('John Doe', 'New York'),\n ('Mary Major', 'Anytown');\nCOMMIT;\n\n-- Query with JOIN\nSELECT o.name, COUNT(p.id) as pet_count\nFROM owner o\nLEFT JOIN pet p ON p.owner_id = o.id\nGROUP BY o.name;\n\n-- Update and delete\nUPDATE owner SET city = 'Boston' WHERE name = 'John Doe';\nDELETE FROM owner WHERE city = 'Portland';\n```\n\n---\n\n## Data Operations: Batch Processing\n\n**Transaction Limits (defaults; verify via the AWS MCP Server's `aws___search_documentation` if available, or the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/): `aurora dsql transaction limits`):**\n\n- Maximum 3,000 rows per transaction\n- Maximum 10 MiB data size per transaction\n- Maximum 5 minutes per transaction\n\n### Safe Batch Insert\n\n```javascript\nasync function batchInsert(pool, tenantId, items) {\n const BATCH_SIZE = 500;\n\n for (let i = 0; i \u003c items.length; i += BATCH_SIZE) {\n const batch = items.slice(i, i + BATCH_SIZE);\n const client = await pool.connect();\n\n try {\n await client.query('BEGIN');\n\n for (const item of batch) {\n await client.query(\n `INSERT INTO entities (tenant_id, name, metadata)\n VALUES ($1, $2, $3)`,\n [tenantId, item.name, JSON.stringify(item.metadata)]\n );\n }\n\n await client.query('COMMIT');\n } catch (error) {\n await client.query('ROLLBACK');\n throw error;\n } finally {\n client.release();\n }\n }\n}\n```\n\n### Concurrent Batch Processing\n\n**Pattern:** SHOULD use concurrent connections for better throughput\n\nSource: Adapted from the JavaScript connector samples listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```javascript\n// Split into batches and process concurrently\nasync function concurrentBatchInsert(pool, tenantId, items) {\n const BATCH_SIZE = 500;\n const NUM_WORKERS = 8;\n\n const batches = [];\n for (let i = 0; i \u003c items.length; i += BATCH_SIZE) {\n batches.push(items.slice(i, i + BATCH_SIZE));\n }\n\n const workers = [];\n for (let i = 0; i \u003c NUM_WORKERS && i \u003c batches.length; i++) {\n workers.push(processBatches(pool, tenantId, batches, i, NUM_WORKERS));\n }\n\n await Promise.all(workers);\n}\n\nasync function processBatches(pool, tenantId, batches, startIdx, step) {\n for (let i = startIdx; i \u003c batches.length; i += step) {\n const batch = batches[i];\n const client = await pool.connect();\n\n try {\n await client.query('BEGIN');\n\n for (const item of batch) {\n await client.query(\n 'INSERT INTO entities (tenant_id, name, metadata) VALUES ($1, $2, $3)',\n [tenantId, item.name, JSON.stringify(item.metadata)]\n );\n }\n\n await client.query('COMMIT');\n } catch (error) {\n await client.query('ROLLBACK');\n throw error;\n } finally {\n client.release();\n }\n }\n}\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3254,"content_sha256":"87c7056034cb8940a0056dc903a044e94cb25f27e7d1eaaeda9f2938ebc98609"},{"filename":"references/examples/migrations.md","content":"# DSQL Examples: Migration Execution\n\nPart of [Aurora DSQL Implementation Examples](../dsql-examples.md).\n\n---\n\n## Migration Execution\n\n**Pattern:** MUST execute each DDL statement separately (DDL statements execute outside transactions)\n\nSource: Adapted from the Liquibase migration sample listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```javascript\nconst migrations = [\n {\n id: '001_initial_schema',\n description: 'Create owner and pet tables',\n statements: [\n `CREATE TABLE IF NOT EXISTS owner (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(30) NOT NULL,\n city VARCHAR(80) NOT NULL,\n telephone VARCHAR(20)\n )`,\n `CREATE TABLE IF NOT EXISTS pet (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(30) NOT NULL,\n birth_date DATE NOT NULL,\n owner_id UUID\n )`,\n ]\n },\n {\n id: '002_create_indexes',\n description: 'Create async indexes',\n statements: [\n 'CREATE INDEX ASYNC idx_owner_city ON owner(city)',\n 'CREATE INDEX ASYNC idx_pet_owner ON pet(owner_id)',\n ]\n },\n {\n id: '003_add_columns',\n description: 'Add status column',\n statements: [\n 'ALTER TABLE pet ADD COLUMN IF NOT EXISTS status VARCHAR(20)',\n \"UPDATE pet SET status = 'active' WHERE status IS NULL\",\n ]\n }\n];\n\nasync function runMigrations(pool, migrations) {\n for (const migration of migrations) {\n for (const statement of migration.statements) {\n if (statement.trim()) {\n await pool.query(statement);\n }\n }\n }\n}\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1661,"content_sha256":"13c8485f9e3f06788d61d41138f200588e3fa9a2b4b14c55e8fa5b239a59eaf7"},{"filename":"references/examples/patterns.md","content":"# DSQL Examples: Application Patterns\n\nPart of [Aurora DSQL Implementation Examples](../dsql-examples.md).\n\n> **`pool` in every example MUST be a DSQL Connector pool, not a bare driver pool.** Construct\n> it via `new AuroraDSQLPool(...)` from `@aws/aurora-dsql-node-postgres-connector` (or the\n> equivalent for your language — see [language.md](../language.md)). Bare `pg.Pool` /\n> `psycopg.connection` / `pgx.Pool` works until the first 15-minute token expiry and then starts\n> returning auth errors on every new connection — DSQL users who try the bare form report this\n> as a DSQL bug. Workflow 0b in SKILL.md covers Connector verification.\n\n---\n\n## Multi-Tenant Isolation\n\nALWAYS include tenant_id in WHERE clauses; tenant_id is always first parameter.\n\n```javascript\nasync function getOrders(pool, tenantId, status) {\n const result = await pool.query(\n 'SELECT * FROM orders WHERE tenant_id = $1 AND status = $2',\n [tenantId, status]\n );\n return result.rows;\n}\n\nasync function deleteOrder(pool, tenantId, orderId) {\n const check = await pool.query(\n 'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',\n [tenantId, orderId]\n );\n\n if (check.rows.length === 0) {\n throw new Error('Order not found or access denied');\n }\n\n await pool.query(\n 'DELETE FROM orders WHERE tenant_id = $1 AND order_id = $2',\n [tenantId, orderId]\n );\n}\n```\n\n---\n\n## Application-Layer Referential Integrity\n\nSHOULD validate references for custom business rules (DSQL provides database-level integrity).\n\n```javascript\nasync function createLineItem(pool, tenantId, lineItemData) {\n const orderCheck = await pool.query(\n 'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',\n [tenantId, lineItemData.order_id]\n );\n\n if (orderCheck.rows.length === 0) {\n throw new Error('Order does not exist');\n }\n\n await pool.query(\n 'INSERT INTO line_items (tenant_id, order_id, product_id, quantity) VALUES ($1, $2, $3, $4)',\n [tenantId, lineItemData.order_id, lineItemData.product_id, lineItemData.quantity]\n );\n}\n\nasync function deleteProduct(pool, tenantId, productId) {\n const check = await pool.query(\n 'SELECT COUNT(*) as count FROM line_items WHERE tenant_id = $1 AND product_id = $2',\n [tenantId, productId]\n );\n\n if (parseInt(check.rows[0].count) > 0) {\n throw new Error('Product has existing orders');\n }\n\n await pool.query(\n 'DELETE FROM products WHERE tenant_id = $1 AND product_id = $2',\n [tenantId, productId]\n );\n}\n```\n\n---\n\n## Sequences and Identity Columns\n\nSequences and IDENTITY columns generate integer values and are useful when compact or human-readable identifiers are needed.\n\n### Identity Columns\n\nAn identity column is a special column generated automatically from an implicit sequence. Use the `GENERATED ... AS IDENTITY` clause in `CREATE TABLE`. CACHE must be specified explicitly as either 1 or >= 65536.\n\n```sql\nCREATE TABLE people (\n id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 70000) PRIMARY KEY,\n name VARCHAR(255),\n address TEXT\n);\n\n-- Or with BY DEFAULT, which allows explicit value overrides\nCREATE TABLE orders (\n order_number BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 70000) PRIMARY KEY,\n tenant_id VARCHAR(255) NOT NULL,\n status VARCHAR(50) NOT NULL\n);\n```\n\nInserting rows without specifying the identity column generates values automatically:\n\n```sql\nINSERT INTO people (name, address) VALUES ('A', 'foo');\nINSERT INTO people (name, address) VALUES ('B', 'bar');\n\n-- Use DEFAULT to explicitly request the generated value\nINSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');\n```\n\n### Standalone Sequences\n\nUse `CREATE SEQUENCE` when you need a sequence independent of a specific table column:\n\n```sql\nCREATE SEQUENCE order_seq CACHE 1 START 101;\n\nSELECT nextval('order_seq');\n-- Returns: 101\n\nINSERT INTO distributors VALUES (nextval('order_seq'), 'nothing');\n```\n\n### Choosing a CACHE Size\n\n- **CACHE >= 65536** — high-frequency identifier generation, many concurrent sessions, tolerates gaps (e.g., IoT ingestion, job run IDs)\n- **CACHE = 1** — low allocation rates, identifiers should follow allocation order more closely, minimizing gaps matters (e.g., account numbers, reference numbers)\n\n---\n\n## Data Serialization\n\n**Pattern:** MUST store arrays and JSON as TEXT (runtime-only types). Per [DSQL docs](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types.html), cast to JSON at query time.\n\n```javascript\nfunction toTextArray(values) {\n return values.join(',');\n}\n\nfunction fromTextArray(textValue) {\n return textValue ? textValue.split(',').map(v => v.trim()) : [];\n}\n\nfunction toTextJSON(object) {\n return JSON.stringify(object);\n}\n\nfunction fromTextJSON(textValue) {\n if (!textValue) return null;\n try {\n return JSON.parse(textValue);\n } catch (err) {\n console.warn('Invalid JSON in column:', err.message);\n return null;\n }\n}\n\nconst categoriesText = toTextArray(['backend', 'api', 'database']);\nawait pool.query('INSERT INTO projects (project_id, categories) VALUES ($1, $2)', [projectId, categoriesText]);\n\nconst configText = toTextJSON({ theme: 'dark', notifications: true });\nawait pool.query('INSERT INTO user_settings (user_id, preferences) VALUES ($1, $2)', [userId, configText]);\n```\n\nQuery-time operations:\n\n```sql\nSELECT user_id, preferences::jsonb->>'theme' as theme\nFROM user_settings WHERE preferences::jsonb->>'notifications' = 'true';\n\nSELECT project_id, string_to_array(categories, ',') as category_array FROM projects;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5583,"content_sha256":"eea214b2d8ac5df3676fa66e52414ad3937cdf7e5cc1331dadbc275e9ab6e336"},{"filename":"references/examples/schema.md","content":"# DSQL Examples: Schema Design\n\nPart of [Aurora DSQL Implementation Examples](../dsql-examples.md).\n\n---\n\n## Schema Design: Table Creation\n\nSHOULD use UUIDs with `gen_random_uuid()` for distributed write performance. Source: Adapted from the Liquibase migration sample listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```sql\nCREATE TABLE IF NOT EXISTS owner (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(30) NOT NULL,\n city VARCHAR(80) NOT NULL,\n telephone VARCHAR(20)\n);\n\nCREATE TABLE IF NOT EXISTS orders (\n order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n tenant_id VARCHAR(255) NOT NULL,\n status VARCHAR(50) NOT NULL,\n tags TEXT,\n metadata TEXT,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n```\n\n---\n\n## Schema Design: Index Creation\n\nMUST use `CREATE INDEX ASYNC` (defaults: max 24 indexes per table, 8 columns per index — verify via the AWS MCP Server's `aws___search_documentation` if available, or the [DSQL documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/): `aurora dsql index limits`). Source: Adapted from the Liquibase migration sample listed at the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n```sql\nCREATE INDEX ASYNC idx_owner_city ON owner(city);\nCREATE INDEX ASYNC idx_orders_tenant ON orders(tenant_id);\nCREATE INDEX ASYNC idx_orders_status ON orders(tenant_id, status);\n```\n\n---\n\n## Schema Design: Column Modifications\n\nMUST use two-step process: add column, then UPDATE for defaults (ALTER COLUMN not supported).\n\n```sql\nALTER TABLE orders ADD COLUMN priority INTEGER;\nUPDATE orders SET priority = 0 WHERE priority IS NULL;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1756,"content_sha256":"05e0498c9ffd2cf9b058fa4ee7d3e5a6865b227e19be9bceb38acf51216b44b5"},{"filename":"references/input-validation.md","content":"# Input Validation for DSQL Queries\n\nPart of the [Aurora DSQL Skill](../SKILL.md).\n\nWhen constructing SQL strings (for `psql -c \"...\"`, ad-hoc shell pipelines, or any code path\nthat does not use the driver's native parameter binding), build every query with the\n[`safe_query`](../scripts/safe_query.py) helper. Do not interpolate values into SQL with\nf-strings, `%`, `.format()`, or concatenation.\n\nWhen using a Postgres driver in application code (psycopg, pgx, sqlx, JDBC, etc.), prefer the\ndriver's native parameter binding (`%s` for psycopg, `$1` for pgx, `?` for JDBC). `safe_query`\nis the canonical fallback whenever you must build a raw SQL string.\n\n---\n\n## Required Pattern\n\n```python\nfrom safe_query import build, allow, regex, ident, keyword, integer, literal, UnsafeSQLError\nfrom safe_query import TENANT_SLUG, UUID, ISO_DATE\n\nsql = build(\n \"SELECT * FROM {tbl} WHERE tenant_id = {tid} AND entity_id = {eid}\",\n tbl=ident(\"entities\"),\n tid=regex(tenant_id, TENANT_SLUG),\n eid=regex(entity_id, UUID),\n)\n# Pass `sql` to your driver: cur.execute(sql), conn.Query(ctx, sql), psql -c \"$sql\", etc.\n```\n\n`build()` raises `UnsafeSQLError` when a placeholder receives a raw string, so\n`build(\"... {x} ...\", x=user_input)` fails loudly at the call site.\n\n## Validator Selection\n\n| Value kind | Validator | Emits |\n|------------------------------------|------------------------|----------------------|\n| Known set (tenant ID, status enum) | `allow(v, SET)` | `'value'` |\n| Known set used as SQL keyword | `keyword(v, SET)` | `value` (unquoted) |\n| Strict format (UUID, slug) | `regex(v, PATTERN)` | `'value'` |\n| Table or column name | `ident(name)` | `\"value\"` |\n| Integer | `integer(v)` | `value` |\n| Free text (description, comment) | `literal(v)` | `$dq_xxx$value$dq_xxx aurora-dsql — Skillopedia |\n\nBuilt-in patterns in `safe_query.py`: `TENANT_SLUG` (`[a-z0-9-]{1,64}`),\n`UUID`, `INT`, `ISO_DATE` (structurally validated `YYYY-MM-DD` — month 01–12, day\n01–31; does NOT check month-day combinations such as Feb 30. The database will\nreject impossible calendar dates at execute time).\n\n## Authorization Is Separate\n\nFormat validation proves the value is shaped correctly. It does not prove the\ncaller is allowed to act on it. Authorize the caller against the tenant or\nresource **before** validating format or calling `build()`:\n\n```python\nassert_caller_has_tenant_access(caller, tenant_id) # authorization\nsql = build(\"... WHERE tenant_id = {tid}\", tid=regex(tenant_id, TENANT_SLUG))\n```\n\n## Why the Helper Exists\n\n- `psql -c \"...\"` and many shell-driven pipelines accept only SQL strings — there is no\n parameter-binding facility at that layer.\n- A Postgres driver's native parameter binding handles validation when the value reaches it as a\n bound parameter, but the moment you build a raw SQL string anywhere along the way (string\n interpolation, dynamic ORDER BY, dynamic table name, etc.), you are back to constructing a SQL\n text. `safe_query.build()` is the canonical way to do that without opening an injection vector.\n- Identifier interpolation (table name, column name) cannot be parameter-bound by any standard\n Postgres driver — it MUST be validated and emitted as a quoted identifier. `ident()` is the\n only safe way.\n\n## Rules\n\n- **MUST** build every dynamically-constructed SQL string with `safe_query.build()`.\n- **MUST** authorize the caller before validating format.\n- **MUST NOT** fall back to f-strings, `%`, `.format()`, or concatenation when\n a validator rejects a value — fix the caller or widen the validator.\n- **MUST NOT** catch `UnsafeSQLError` to recover silently. Re-raise or return\n an error to the caller.\n- **SHOULD** add new patterns to `safe_query.py` rather than inlining regex at\n call sites, so reviewers can audit them in one place.\n\n## Bash Deliverables\n\nBash deliverables (CI/CD steps, cron jobs, bastion runbooks) apply the same\nrule. **ALWAYS** compose the SQL in a `python3` subshell so `safe_query.py`\nactually executes. MUST NOT rely on native reference alone. **MUST** invoke to defend against injection.\n\n```bash\nSQL=$(python3 - \"$USER_ID\" \u003c\u003c'PY'\nimport sys\nfrom safe_query import build, regex, literal, TENANT_SLUG\nprint(build(\n \"INSERT INTO audit_log (action, user_id) VALUES ({a}, {u})\",\n a=literal(\"psql-write-smoke\"),\n u=regex(sys.argv[1], TENANT_SLUG), # raises on malformed input\n))\nPY\n)\nPGPASSWORD=$(aws dsql generate-db-connect-admin-auth-token --hostname \"$HOST\" --region \"$REGION\") \\\n psql -P pager=off \"host=$HOST port=5432 user=admin dbname=postgres sslmode=verify-full sslrootcert=system\" \u003c\u003c\u003c\"$SQL\"\n```\n\n`regex()` raises before `psql` runs; `literal()` escapes the validated\nvalue into the final SQL text.\n\n**ALWAYS** explicitly set SSL mode — DSQL rejects non-TLS connections\nand psql's default `PGSSLMODE=prefer` will attempt plaintext first.\nTwo equivalent forms, both valid per the\n[AWS DSQL psql guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/accessing-psql.html):\n\n- **Env-var form (canonical per AWS docs):** `export PGSSLMODE=verify-full PGSSLROOTCERT=system`\n before the psql invocation, with flag-form arguments\n (`psql --username admin --dbname postgres --host $HOST`).\n- **Connection-string form:** embed `sslmode=verify-full sslrootcert=system` in a libpq URI\n (`psql \"host=$HOST port=5432 user=admin dbname=postgres sslmode=verify-full sslrootcert=system\"`).\n\nUse `sslmode=verify-full sslrootcert=system` (matches `psql-connect.sh`'s default and what the\nJava/Rust/Python connectors enforce). Drop to `sslmode=require` only when the client genuinely\ncannot reach a trusted CA bundle — and document the downgrade in the runbook.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5853,"content_sha256":"05d689dc1a25666552fb97964e88cd819eaf75eaeb9dec92882a7549ce1a8c61"},{"filename":"references/language.md","content":"# DSQL Language-Specific Implementation Examples and Guides\n\n## Tenets\n\n- **MUST** use the official DSQL Connector for the chosen driver (when one exists). The Connectors are the canonical IAM-token-refresh path; memory-authored connection code drifts.\n- **MUST** follow the [official DSQL connectors, drivers, and ORM samples](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html) for client install, auth, and CRUD unless user requirements explicitly conflict.\n\n## Driver and Sample Index\n\nThe authoritative index of supported drivers, ORMs, adapters, and example repositories lives at\n[Aurora DSQL cluster connectivity tools](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html).\nPull the per-language sample link from that page rather than hardcoding repository paths here —\nthe AWS docs page tracks rename, relocation, and deprecation events.\n\n## Framework and Connection Notes for Languages and Drivers\n\n### Python\n\n**ALWAYS** use the [DSQL Python Connector](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/SECTION_program-with-dsql-connector-for-python.html) for automatic IAM auth. The single `aurora-dsql-python-connector` wheel ships support for all three drivers — install **only** the underlying driver you need:\n\n- **psycopg** (modern async/sync)\n - Install: `pip install aurora-dsql-python-connector psycopg[binary] psycopg-pool`\n - Canonical import: `import aurora_dsql_psycopg as dsql`\n- **psycopg2** (synchronous)\n - Install: `pip install aurora-dsql-python-connector psycopg2`\n - Canonical import: `import aurora_dsql_psycopg2 as dsql`\n- **asyncpg** (full async)\n - Install: `pip install aurora-dsql-python-connector asyncpg`\n - Canonical import: `import aurora_dsql_asyncpg as dsql`\n\nFor per-driver `example_preferred.py` files and pool/TLS/token-refresh examples, see the\n[AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html).\n\n#### SQLAlchemy\n\n- Supports `psycopg` and `psycopg2`\n- See the SQLAlchemy entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n- Dialect Source: [aurora-dsql-sqlalchemy](https://github.com/awslabs/aurora-dsql-sqlalchemy/tree/main/)\n\n#### JupyterLab\n\n- Still SHOULD PREFER using the python connector.\n- Popular data science option for interactive computing environment that combines code, text, and visualizations\n- Options for Local or using Amazon SageMaker\n- REQUIRES downloading the Amazon root certificate from the official trust store\n- For a Jupyter setup walkthrough, see the Python entries in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n### Go\n\n**ALWAYS** use the [DSQL Go Connector](https://github.com/awslabs/aurora-dsql-connectors/tree/main/go/pgx) for automatic IAM auth:\n\n- **pgx** (recommended)\n - Install: `go get github.com/awslabs/aurora-dsql-connectors/go/pgx`\n - Canonical import: `import \"github.com/awslabs/aurora-dsql-connectors/go/pgx/dsql\"`\n - Connector: [aurora-dsql-connectors/go/pgx](https://github.com/awslabs/aurora-dsql-connectors/tree/main/go/pgx)\n - For the `example_preferred.go` and pool patterns, see the Go entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n### JavaScript/TypeScript\n\n**ALWAYS** use one of the two DSQL Node.js connectors — [node-postgres](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/SECTION_program-with-dsql-connector-for-node-postgres.html) or [postgres-js](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/SECTION_program-with-dsql-connector-for-postgresjs.html). Even when the user asks for \"just node-postgres directly\" or \"just pg directly,\" the Connector **is** the node-postgres path — it wraps `pg` as its underlying driver while handling IAM auth token refresh and TLS defaults. A bare `pg.Pool`/`pg.Client` works until the first 15-minute IAM auth token expiry and then starts returning auth errors on every new connection; DSQL users who try the bare form hit this degraded state in production and report it as a DSQL bug, so the bare pattern is user-harmful by default. Deliver the Connector; treat \"just use pg\" as shorthand for \"I want a node-postgres solution,\" not as a veto on the Connector.\n\n#### node-postgres (pg)\n\n- Package: `@aws/aurora-dsql-node-postgres-connector`\n- Canonical import: `import { AuroraDSQLPool } from \"@aws/aurora-dsql-node-postgres-connector\";`\n- Construct: `new AuroraDSQLPool({ host, user, max?, idleTimeoutMillis?, connectionTimeoutMillis? })`\n- For the `example_preferred.js` and pool patterns, see the JavaScript node-postgres entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n#### postgres.js\n\n- Package: `@aws/aurora-dsql-postgresjs-connector`\n- Canonical import: `import { auroraDSQLPostgres } from \"@aws/aurora-dsql-postgresjs-connector\";`\n- Construct: `auroraDSQLPostgres({ host, user, max?, idle_timeout?, connect_timeout? })`\n- Lightweight alternative; good for serverless environments\n- For the `example_preferred.js` and pool patterns, see the JavaScript Postgres.js entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n#### Prisma\n\n- Custom `directUrl` with token refresh middleware\n- See the TypeScript Prisma entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n#### Sequelize\n\n- Configure `dialectOptions` for SSL\n- Token refresh in `beforeConnect` hook\n- See the TypeScript Sequelize entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n#### TypeORM\n\n- Custom DataSource with token refresh\n- Create migrations table manually via psql\n- See the TypeScript TypeORM entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n### Java\n\n**ALWAYS** use the [DSQL JDBC Connector](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/SECTION_program-with-jdbc-connector.html) for automatic IAM auth.\n\n**JDBC** (via DSQL JDBC Connector)\n\n- Gradle: `implementation(\"software.amazon.dsql:aurora-dsql-jdbc-connector:1.4.0\")`\n- Maven: `\u003cgroupId>software.amazon.dsql\u003c/groupId>\u003cartifactId>aurora-dsql-jdbc-connector\u003c/artifactId>\u003cversion>1.4.0\u003c/version>`\n- URL format: `jdbc:aws-dsql:postgresql://\u003cendpoint>/postgres`\n- Properties: `wrapperPlugins=iam`, `ssl=true`, `sslmode=verify-full`\n- See the Java pgJDBC entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n**HikariCP** (Connection Pooling)\n\n- Gradle: `implementation(\"com.zaxxer:HikariCP:7.0.2\")` alongside the JDBC connector\n- Wrap JDBC connection, configure max lifetime \u003c 1 hour\n- See the Java HikariCP + pgJDBC entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n### Rust\n\n**ALWAYS** use the DSQL Rust connector for automatic IAM auth.\n\n**SQLx** (async, recommended)\n\n- Cargo: `aurora-dsql-sqlx-connector = { version = \"0.2\", features = [\"pool\", \"occ\"] }`\n- Canonical use: wrap `sqlx::postgres::PgPool` via the connector's builder; the connector injects IAM auth tokens and handles rotation.\n- See the Rust SQLx entry in the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n\n**Tokio-Postgres** (lower-level async)\n\n- Only reach for raw `tokio-postgres` when the `aurora-dsql-sqlx-connector` doesn't fit the runtime. Implement periodic token refresh with `tokio::spawn`.\n- Connection format: `postgres://admin:{token}@{endpoint}:5432/postgres?sslmode=verify-full&application_name=\u003capp-name>/\u003cmodel-id>`\n\n### Elixir\n\n#### Postgrex\n\n- MUST use Erlang/OTP 26+\n- Driver: [Postgrex](https://hexdocs.pm/postgrex/) ~> 0.19\n - Use Postgrex.query! for all queries\n- Connection: Implement `Repo.init/2` callback for dynamic token injection\n - MUST set `ssl: true` with `ssl_opts: [verify: :verify_peer, cacerts: :public_key.cacerts_get()]`\n - MAY prefer AWS CLI via `System.cmd` to call `generate-db-connect-auth-token`\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":8299,"content_sha256":"ba68f124233831a456127e9e7903b1eaac63be1aef62cf33e79839e890bc0e9c"},{"filename":"references/mcp-setup.md","content":"# MCP Configuration for the Aurora DSQL Skill\n\nThis skill PREFERS direct `psql` execution (via [`scripts/psql-connect.sh`](../scripts/psql-connect.sh))\nfor ad-hoc DSQL queries, and the official **AWS MCP Server** for AWS knowledge lookups and AWS API\naccess.\n\n## When to use which\n\n| Need | Use |\n|-----------------------------------------------------|----------------------------------------------------------------------------|\n| Run an ad-hoc SELECT / DDL / DML against a cluster | [`scripts/psql-connect.sh`](../scripts/psql-connect.sh) |\n| Look up DSQL service limits, docs, or skills | AWS MCP Server `aws___search_documentation` / `aws___read_documentation` |\n| Make an AWS API call (`dsql:`, `iam:`, etc.) | AWS MCP Server `aws___call_aws` |\n| Run a sandboxed Python script that calls AWS APIs | AWS MCP Server `aws___run_script` |\n| Application code (Python, JS, Java, Go, Rust, etc.) | The language-specific [DSQL Connector](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html) |\n\n## AWS MCP Server (recommended)\n\nThe AWS MCP Server is the canonical AWS knowledge + API integration for coding assistants. It\nships with knowledge tools (`aws___search_documentation`, `aws___read_documentation`,\n`aws___recommend`, `aws___retrieve_skill`, `aws___list_regions`, `aws___get_regional_availability`)\nand AWS API tools (`aws___call_aws`, `aws___run_script`, `aws___get_tasks`,\n`aws___get_presigned_url`).\n\nSee [mcp-tools.md](mcp-tools.md) for the canonical surface and per-tool detail; the official tool\nlist lives at\n[Understanding the MCP Server tools](https://docs.aws.amazon.com/aws-mcp/latest/userguide/understanding-mcp-server-tools.html).\n\n**Setup:** Follow the official guide at\n[Setting up the AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html).\nThe setup steps differ per coding assistant (Claude Code, Gemini, Codex, Kiro, etc.) — defer to\nthe AWS docs page rather than caching invocation details here.\n\nAfter installation, this skill will use `aws___search_documentation` to verify DSQL service limits\non demand (see the limit table in [SKILL.md](../SKILL.md#aws-knowledge-via-the-aws-mcp-server-optional)).\n\n## Credential reminder\n\nWhichever path you use:\n\n- Tokens generated by `aws dsql generate-db-connect-auth-token` expire after 15 minutes — never\n persist them, regenerate per session\n- Reserve `generate-db-connect-admin-auth-token` for cluster setup, role grants, and DDL; use\n the scoped variant for app workloads\n- Always use `sslmode=verify-full` (or at minimum `require`) — DSQL rejects non-TLS connections\n\nFor end-to-end credential and connection guidance, see [authentication-guide.md](auth/authentication-guide.md).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":2970,"content_sha256":"6f546752b14c96707f5d5c1b8ef7f22d3377c0bc8d87ce96078a353d41383197"},{"filename":"references/mcp-tools.md","content":"# MCP Tools for the Aurora DSQL Skill\n\nThis file describes how the Aurora DSQL skill interacts with MCP servers. The skill PREFERS\ndirect `psql` (via [`scripts/psql-connect.sh`](../scripts/psql-connect.sh)) and PostgreSQL drivers\nover MCP-mediated DSQL execution. MCP is consulted primarily for **AWS knowledge** (docs lookup,\nservice limits, AWS API calls) via the official AWS MCP Server.\n\n## AWS MCP Server (recommended)\n\nWhen connected, the AWS MCP Server provides:\n\n**Knowledge tools** (no extra setup beyond the server itself):\n\n- `aws___search_documentation` — search across all AWS documentation, including DSQL service\n docs and skills. PREFER for verifying DSQL limits or finding the canonical doc page.\n- `aws___read_documentation` — fetch a specific AWS docs page in markdown form.\n- `aws___recommend` — content recommendations related to a specific docs page.\n- `aws___retrieve_skill` — fetch the full content of a domain-specific AWS skill discovered via\n `aws___search_documentation`.\n- `aws___list_regions` / `aws___get_regional_availability` — confirm DSQL or a dependent feature\n is available in the target region before recommending an architecture.\n\n**AWS API tools** (require IAM credentials):\n\n- `aws___call_aws` — execute an authenticated AWS API call. Useful for `dsql:CreateCluster`,\n `dsql:GetCluster`, `dsql:ListClusters`, etc., when the user wants the assistant to drive cluster\n lifecycle operations directly. For asynchronous DSQL operations (`CreateCluster`,\n `DeleteCluster`) poll readiness by re-invoking `aws___call_aws` with `dsql:GetCluster` — DSQL\n returns the cluster status directly, not an MCP task ID.\n- `aws___run_script` — sandboxed Python with AWS API access. Useful for multi-step or parallel\n workflows like \"list every cluster in the region, check whose tags include `Environment=eval`,\n then describe the matching ones.\" May return an MCP task ID for very long scripts.\n- `aws___get_presigned_url` — generate pre-signed Amazon S3 URLs for uploading/downloading files\n (e.g., DSQL bulk-loading source data).\n\n**MCP session tools** (no IAM):\n\n- `aws___get_tasks` — poll MCP-side task IDs returned by `aws___call_aws` or `aws___run_script`\n when the MCP wrapper queues a long-running invocation. **NOT** for polling AWS-API-side async\n operations like `dsql:CreateCluster` — those return their status field directly.\n\nSee [documentation-tools.md](documentation-tools.md) for per-tool detail and example calls.\n\nSetup, auth, and per-assistant invocation differ by client — see the official\n[AWS MCP Server docs](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html).\n\n## Database Operations\n\nDatabase operations against a DSQL cluster run through `psql` by default. The wrapper script\n[`scripts/psql-connect.sh`](../scripts/psql-connect.sh) handles IAM auth token generation, TLS\ndefaults, application_name tagging, and single-statement guards.\n\nSee [database-tools.md](database-tools.md) for the full read / write / schema-discovery patterns.\n\n## Detailed References\n\n- **[input-validation.md](input-validation.md)** — **MUST** load before building any query.\n Build SQL with `safe_query.build()`, which rejects raw strings by construction.\n- **[safe_query.py](../scripts/safe_query.py)** — the validated-query helper module.\n- **[database-tools.md](database-tools.md)** — `psql`-based read / write / schema patterns.\n- **[workflow-patterns.md](workflow-patterns.md)** — common multi-step workflow patterns.\n\n## Additional Resources\n\n- [Aurora DSQL Documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/)\n- [Aurora DSQL Connectivity Tools](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n- [AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3825,"content_sha256":"6e5186149206c97d666a3724308fc0706f057aa4235cbac96654c3c9a2f88240"},{"filename":"references/mysql-migrations/ddl-auto-increment.md","content":"# MySQL to DSQL: AUTO_INCREMENT Migration\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## AUTO_INCREMENT Migration\n\n**MySQL syntax:**\n\n```sql\nCREATE TABLE users (\n id INT AUTO_INCREMENT PRIMARY KEY,\n name VARCHAR(255)\n);\n```\n\nDSQL provides three options for replacing MySQL's AUTO_INCREMENT. Choose based on your workload requirements. See [Choosing Identifier Types](../auth/scaling-guide.md#choosing-identifier-types) in the scaling guide for detailed guidance.\n\n**When choosing integer auto-increment, ALWAYS use `GENERATED AS IDENTITY`** (not `SERIAL`, which DSQL does not support). UUIDs (Option 1) remain the recommended default.\n\n### Option 1: UUID Primary Key (Recommended for Scalability)\n\nUUIDs are the recommended default because they avoid coordination and scale well for distributed writes.\n\n```sql\nCREATE TABLE users (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(255)\n);\n```\n\n> **DSQL: `gen_random_uuid()` is built-in; do NOT run `CREATE EXTENSION pgcrypto`.** DSQL ships\n> PostgreSQL 16's core `gen_random_uuid()`, so the extension is unnecessary AND `CREATE EXTENSION`\n> is rejected by DSQL (`ERROR: unsupported statement: CreateExtension`). Other `pgcrypto`\n> functions (`crypt()`, `digest()`, `hmac()`, etc.) are unavailable — implement those at the\n> application layer.\n\n### Option 2: IDENTITY Column (Recommended for Integer Auto-Increment)\n\nUse `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` when compact, human-readable integer IDs\nare needed.\n\n> **DSQL: `CACHE` is mandatory.** DSQL has **no implicit default** and rejects identity columns\n> declared without it: `ERROR: identity column is not supported without an explicit cache size.\n> please define CACHE greater than or equal to 65536 or equal to 1`. A migration tool replaying\n> a vanilla PostgreSQL dump (where `CACHE` defaults to 1) will fail at the first `IDENTITY`\n> column. Always include `(CACHE 1)` for strict ordering or `(CACHE 65536)` (or higher) for\n> high-throughput workloads — see [scaling-guide.md](../auth/scaling-guide.md#choosing-identifier-types).\n\n```sql\n-- GENERATED ALWAYS: DSQL always generates the value; explicit inserts rejected unless OVERRIDING SYSTEM VALUE\nCREATE TABLE users (\n id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 65536) PRIMARY KEY,\n name VARCHAR(255)\n);\n\n-- GENERATED BY DEFAULT: DSQL generates a value unless an explicit value is provided (closer to MySQL AUTO_INCREMENT behavior)\nCREATE TABLE users (\n id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,\n name VARCHAR(255)\n);\n```\n\n#### Choosing a CACHE Size\n\n**REQUIRED:** Specify CACHE explicitly. Supported values are `1` or `>= 65536`.\n\n- **CACHE >= 65536** — High-frequency inserts, many concurrent sessions, tolerates gaps and ordering effects (e.g., IoT/telemetry, job IDs, order numbers)\n- **CACHE = 1** — Low allocation rates, identifiers should follow allocation order closely, minimizing gaps matters more than throughput (e.g., account numbers, reference numbers)\n\n### Option 3: Explicit SEQUENCE\n\nUse a standalone sequence when multiple tables share a counter or when you need `nextval`/`setval` control.\n\n```sql\n-- Create the sequence (CACHE MUST be 1 or >= 65536)\nCREATE SEQUENCE users_id_seq CACHE 65536 START 1;\n\n-- Create table using the sequence\nCREATE TABLE users (\n id BIGINT PRIMARY KEY DEFAULT nextval('users_id_seq'),\n name VARCHAR(255)\n);\n```\n\n### Migrating Existing AUTO_INCREMENT Data\n\n#### To UUID Primary Key\n\n```sql\nCREATE TABLE users_new (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n legacy_id INTEGER, -- Preserve original AUTO_INCREMENT ID for reference\n name VARCHAR(255)\n);\n\nINSERT INTO users_new (id, legacy_id, name)\nSELECT gen_random_uuid(), id, name\nFROM users;\n```\n\nIf other tables reference the old integer ID, update those references to use the new UUID or the `legacy_id` column.\n\n#### To IDENTITY Column (Preserving Integer IDs)\n\n```sql\n-- Use GENERATED BY DEFAULT to allow explicit ID values during migration\nCREATE TABLE users_new (\n id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,\n name VARCHAR(255)\n);\n\n-- Migrate with original integer IDs preserved\nINSERT INTO users_new (id, name)\nSELECT id, name\nFROM users;\n\n-- Set the identity sequence to continue after the max existing ID\n-- Get the max ID first:\nSELECT MAX(id) as max_id FROM users_new;\n-- Then reset the sequence (find the sequence name via:\n-- SELECT pg_get_serial_sequence('users_new', 'id');):\nSELECT setval('users_new_id_seq', (SELECT MAX(id) FROM users_new));\n```\n\n**Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":4768,"content_sha256":"26ccf49f9b0c86950e7c277ce4a6fe438868ad74ccb8ef415dd141379ff369ea"},{"filename":"references/mysql-migrations/ddl-batching.md","content":"# MySQL to DSQL: Batched Migration & Error Handling\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## Batched Migration Pattern\n\n**REQUIRED for tables exceeding 3,000 rows.**\n\nSee [ddl-migrations/batched-migration.md](../ddl-migrations/batched-migration.md) for the full pattern including OFFSET-based batching, cursor-based batching, progress tracking, and error handling.\n\n### MySQL-Specific Considerations\n\nWhen migrating from MySQL, additional validation checks may be needed:\n\n- **Type conversion failures:** Non-numeric VARCHAR to INTEGER (check with regex validation)\n- **Value truncation:** TEXT to VARCHAR(n) where values exceed target length\n- **UNSIGNED check:** Negative values in columns that were MySQL UNSIGNED types\n\n```sql\n-- Find values exceeding target VARCHAR length\nSELECT id, LENGTH(text_column) as len FROM target_table\nWHERE LENGTH(text_column) > 255 LIMIT 100;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1028,"content_sha256":"2b8bb851e5478326a0e8839eb6bcc8c0d5b170245aeb11038d4bb73de38fe6dd"},{"filename":"references/mysql-migrations/ddl-column-changes.md","content":"# MySQL to DSQL: Column Changes\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## ALTER TABLE ... ALTER COLUMN (Change Column Type)\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name ALTER COLUMN column_name datatype;\n-- or MySQL-specific:\nALTER TABLE table_name MODIFY COLUMN column_name new_datatype;\nALTER TABLE table_name CHANGE COLUMN old_name new_name new_datatype;\n```\n\n**DSQL:** MUST use **Table Recreation Pattern** — see [column-operations.md ALTER COLUMN TYPE](../ddl-migrations/column-operations.md#alter-column-type-migration) for the full step-by-step pattern including pre-migration validation and data type compatibility matrix.\n\n---\n\n## ALTER TABLE ... DROP COLUMN\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name DROP COLUMN column_name;\n```\n\n**DSQL:** MUST use **Table Recreation Pattern** — see [column-operations.md DROP COLUMN](../ddl-migrations/column-operations.md#drop-column-migration) for the full step-by-step pattern.\n\nFor tables > 3,000 rows, use [Batched Migration Pattern](ddl-batching.md).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1165,"content_sha256":"3415766b082fb3d1842637d6ada2b7ccb3876393d1bfbb59998e6caf8f7b432d"},{"filename":"references/mysql-migrations/ddl-constraints.md","content":"# MySQL to DSQL: NULL and DEFAULT Constraints\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## ALTER COLUMN SET/DROP NOT NULL Migration\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name MODIFY COLUMN column_name datatype NOT NULL;\nALTER TABLE table_name MODIFY COLUMN column_name datatype NULL;\n```\n\n**DSQL:** MUST use **Table Recreation Pattern**.\n\n### Pre-Migration Validation (for SET NOT NULL)\n\n```sql\nSELECT COUNT(*) as null_count FROM target_table\nWHERE target_column IS NULL;\n-- MUST ABORT if null_count > 0, or plan to provide default values\n```\n\n### Migration Steps\n\n#### Step 1: Create new table with changed constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n target_column VARCHAR(255) NOT NULL, -- Changed from nullable\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data (with default for NULLs if needed)\n\n```sql\nINSERT INTO target_table_new (id, target_column, other_column)\nSELECT id, COALESCE(target_column, 'default_value'), other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n\n---\n\n## ALTER COLUMN SET/DROP DEFAULT Migration\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;\nALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;\n```\n\n**DSQL:** MUST use **Table Recreation Pattern**.\n\n### Migration Steps (SET DEFAULT)\n\n#### Step 1: Create new table with default value\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n status VARCHAR(50) DEFAULT 'pending', -- Added default\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, status, other_column)\nSELECT id, status, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n\n### Migration Steps (DROP DEFAULT)\n\n#### Step 1: Create new table without default\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n status VARCHAR(50), -- Removed DEFAULT\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, status, other_column)\nSELECT id, status, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":2433,"content_sha256":"41337c21edded768ca305dc0a25b532d8405dd15293692b96bdc2885962eb366"},{"filename":"references/mysql-migrations/ddl-operations.md","content":"# MySQL to DSQL Migration: DDL Operations\n\nMigration patterns for specific MySQL DDL operations to DSQL-compatible equivalents.\n\n**MUST read [type-mapping.md](type-mapping.md) first** for data type mappings and the CRITICAL Destructive Operations Warning.\n**MUST read [ddl-migrations/overview.md](../ddl-migrations/overview.md)** for the general Table Recreation Pattern and user verification requirements.\n\n---\n\n## Table Recreation Pattern Overview\n\nMUST follow this sequence with user verification at each step:\n\n1. **Plan & Confirm** - MUST present migration plan and obtain user approval to proceed\n2. **Validate** - Check data compatibility with new structure; MUST report findings to user\n3. **Create** - Create new table with desired structure; MUST verify with user before execution\n4. **Migrate** - Copy data (batched for tables > 3,000 rows); MUST report progress to user\n5. **Verify** - Confirm row counts match; MUST present comparison to user\n6. **Swap** - CRITICAL: MUST obtain explicit user confirmation before DROP TABLE\n7. **Re-index** - Recreate indexes using ASYNC; MUST confirm completion with user\n\n### Transaction Rules\n\n- **MUST batch** migrations exceeding 3,000 row mutations\n- **PREFER batches of 500-1,000 rows** for optimal throughput\n- **MUST respect** 10 MiB data size per transaction\n- **MUST respect** 5-minute transaction duration\n\n---\n\n## Common Verify & Swap Pattern\n\nAll migrations end with this pattern (referenced in examples below).\n\n**CRITICAL: MUST obtain explicit user confirmation before DROP TABLE step.**\n\n```sql\n-- MUST verify counts match\nSELECT COUNT(*) FROM target_table;\nSELECT COUNT(*) FROM target_table_new;\n\n-- CHECKPOINT: MUST present count comparison to user and obtain confirmation\n-- Agent MUST display: \"Original table has X rows, new table has Y rows.\n-- Proceeding will DROP the original table. This action is IRREVERSIBLE.\n-- Do you want to proceed? (yes/no)\"\n-- MUST NOT proceed without explicit \"yes\" confirmation\n\n-- MUST swap tables (DESTRUCTIVE - requires user confirmation above).\n-- Each DDL below MUST run in its own transaction (DSQL: one DDL per\n-- transaction):\nDROP TABLE target_table;\nALTER TABLE target_table_new RENAME TO target_table;\n\n-- MUST recreate indexes (each in its own transaction):\nCREATE INDEX ASYNC idx_target_tenant ON target_table(tenant_id);\n```\n\n---\n\n## Detailed Migration Patterns\n\nLoad the relevant file for the specific MySQL DDL operation you need to migrate:\n\n- **[ddl-column-changes.md](ddl-column-changes.md)** — ALTER COLUMN type, DROP COLUMN\n- **[ddl-auto-increment.md](ddl-auto-increment.md)** — AUTO_INCREMENT to UUID/IDENTITY/SEQUENCE\n- **[ddl-type-alternatives.md](ddl-type-alternatives.md)** — ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY\n- **[ddl-constraints.md](ddl-constraints.md)** — SET/DROP NOT NULL, SET/DROP DEFAULT\n- **[ddl-structural.md](ddl-structural.md)** — ADD/DROP CONSTRAINT, MODIFY PRIMARY KEY\n- **[ddl-batching.md](ddl-batching.md)** — Batched migration pattern, error handling and recovery\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3035,"content_sha256":"6b3f66917a703b02ed0545f6c67487534eed4e78dd8b1bcbc1e0ea99154af9e0"},{"filename":"references/mysql-migrations/ddl-structural.md","content":"# MySQL to DSQL: Structural Changes\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## ADD/DROP CONSTRAINT Migration\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);\nALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);\nALTER TABLE table_name DROP CONSTRAINT constraint_name;\n-- or MySQL-specific:\nALTER TABLE table_name DROP INDEX index_name;\nALTER TABLE table_name DROP CHECK constraint_name;\n```\n\n**DSQL:** MUST use **Table Recreation Pattern**.\n\n### Pre-Migration Validation (for ADD CONSTRAINT)\n\n**MUST validate existing data satisfies the new constraint.**\n\n```sql\n-- For UNIQUE constraint: check for duplicates\nSELECT target_column, COUNT(*) as cnt FROM target_table\nGROUP BY target_column HAVING COUNT(*) > 1 LIMIT 10;\n-- MUST ABORT if any duplicates exist\n\n-- For CHECK constraint: validate all rows pass\nSELECT COUNT(*) as invalid_count FROM target_table\nWHERE NOT (check_condition);\n-- MUST ABORT if invalid_count > 0\n```\n\n### Migration Steps (ADD CONSTRAINT)\n\n#### Step 1: Create new table with the constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n email VARCHAR(255) UNIQUE, -- Added UNIQUE constraint\n age INTEGER CHECK (age >= 0), -- Added CHECK constraint\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (id, email, age, other_column)\nSELECT id, email, age, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n\n### Migration Steps (DROP CONSTRAINT)\n\n#### Step 1: Identify existing constraints\n\n```sql\nSELECT constraint_name, constraint_type\nFROM information_schema.table_constraints\nWHERE table_name = 'target_table'\n AND constraint_type IN ('UNIQUE', 'CHECK');\n```\n\n#### Step 2: Create new table without the constraint\n\n```sql\nCREATE TABLE target_table_new (\n id UUID PRIMARY KEY,\n email VARCHAR(255), -- Removed UNIQUE constraint\n other_column TEXT\n);\n```\n\n#### Step 3: Copy data\n\n```sql\nINSERT INTO target_table_new (id, email, other_column)\nSELECT id, email, other_column\nFROM target_table;\n```\n\n**Step 4: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n\n---\n\n## MODIFY PRIMARY KEY Migration\n\n**MySQL syntax:**\n\n```sql\nALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (new_column);\n```\n\n**DSQL:** MUST use **Table Recreation Pattern**.\n\n### Pre-Migration Validation\n\n**MUST validate new PK column has unique, non-null values.**\n\n```sql\n-- Check for duplicates\nSELECT new_pk_column, COUNT(*) as cnt FROM target_table\nGROUP BY new_pk_column HAVING COUNT(*) > 1 LIMIT 10;\n-- MUST ABORT if any duplicates exist\n\n-- Check for NULLs\nSELECT COUNT(*) as null_count FROM target_table\nWHERE new_pk_column IS NULL;\n-- MUST ABORT if null_count > 0\n```\n\n### Migration Steps\n\n#### Step 1: Create new table with new primary key\n\n```sql\nCREATE TABLE target_table_new (\n new_pk_column UUID PRIMARY KEY, -- New PK\n old_pk_column VARCHAR(255), -- Demoted to regular column\n other_column TEXT\n);\n```\n\n#### Step 2: Copy data\n\n```sql\nINSERT INTO target_table_new (new_pk_column, old_pk_column, other_column)\nSELECT new_pk_column, old_pk_column, other_column\nFROM target_table;\n```\n\n**Step 3: Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3503,"content_sha256":"126bf5d120b377457beedbbe05cfe9f78781e09e7b16a8a97642b81072c81a0d"},{"filename":"references/mysql-migrations/ddl-type-alternatives.md","content":"# MySQL to DSQL: Type Alternatives\n\nPart of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.\n\n---\n\n## ENUM Type Migration\n\n**MySQL syntax:**\n\n```sql\nCREATE TABLE orders (\n id INT AUTO_INCREMENT PRIMARY KEY,\n status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL\n);\n```\n\n**DSQL equivalent using VARCHAR with CHECK:**\n\n```sql\nCREATE TABLE orders (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n status VARCHAR(255) NOT NULL CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))\n);\n```\n\n### Migrating Existing ENUM Data\n\n```sql\n-- ENUM values are already stored as strings; direct copy is safe\nINSERT INTO orders_new (id, status)\nSELECT gen_random_uuid(), status\nFROM orders;\n```\n\n---\n\n## SET Type Migration\n\n**MySQL syntax:**\n\n```sql\nCREATE TABLE user_preferences (\n id INT AUTO_INCREMENT PRIMARY KEY,\n permissions SET('read', 'write', 'delete', 'admin')\n);\n```\n\n**DSQL equivalent using TEXT (comma-separated):**\n\n```sql\nCREATE TABLE user_preferences (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n permissions TEXT -- Stored as comma-separated: 'read,write,admin'\n);\n```\n\n**Note:** Application layer MUST validate and parse SET values. MySQL stores SET values as comma-separated strings internally, so direct migration preserves the format.\n\n---\n\n## ON UPDATE CURRENT_TIMESTAMP Migration\n\n**MySQL syntax:**\n\n```sql\nCREATE TABLE records (\n id INT AUTO_INCREMENT PRIMARY KEY,\n data TEXT,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n```\n\n**DSQL equivalent:**\n\n```sql\nCREATE TABLE records (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n data TEXT,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n```\n\n**MUST explicitly set** `updated_at = CURRENT_TIMESTAMP` in every UPDATE statement to replicate `ON UPDATE CURRENT_TIMESTAMP` behavior:\n\n```sql\nUPDATE records SET data = 'new_value', updated_at = CURRENT_TIMESTAMP\nWHERE id = 'record-uuid';\n```\n\n---\n\n## FOREIGN KEY Migration\n\n**MySQL syntax:**\n\n```sql\nCREATE TABLE orders (\n id INT AUTO_INCREMENT PRIMARY KEY,\n customer_id INT,\n FOREIGN KEY (customer_id) REFERENCES customers(id)\n);\n```\n\n**MUST implement referential integrity at the application layer:**\n\n```sql\n-- Create table with reference column (enforce integrity in application layer)\nCREATE TABLE orders (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n customer_id UUID NOT NULL\n);\n\n-- Create index for the reference column\nCREATE INDEX ASYNC idx_orders_customer ON orders(customer_id);\n```\n\n**Application layer MUST enforce referential integrity:**\n\n```sql\n-- Before INSERT: validate parent exists\nSELECT id FROM customers WHERE id = 'customer-uuid';\n-- MUST abort INSERT if parent not found\n\n-- Before DELETE of parent: check for dependents\nSELECT COUNT(*) as dependent_count FROM orders\nWHERE customer_id = 'customer-uuid';\n-- MUST abort DELETE if dependent_count > 0\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":2998,"content_sha256":"44f505eb403ddce51ec78651ce6442a5e970b786486545dc891acc612e2ccfda"},{"filename":"references/mysql-migrations/full-example.md","content":"# MySQL to DSQL Migration: Full Example\n\nEnd-to-end example migrating a complete MySQL CREATE TABLE to DSQL.\n\n**MUST read [type-mapping.md](type-mapping.md) first** for data type mappings and the CRITICAL Destructive Operations Warning.\n**MUST read [ddl-operations.md](ddl-operations.md)** for DDL operation patterns.\n\n---\n\n## Original MySQL Schema\n\n```sql\nCREATE TABLE products (\n id INT AUTO_INCREMENT PRIMARY KEY,\n tenant_id INT NOT NULL,\n name VARCHAR(255) NOT NULL,\n description MEDIUMTEXT,\n price DECIMAL(10,2) NOT NULL,\n category ENUM('electronics', 'clothing', 'food', 'other') DEFAULT 'other',\n tags SET('sale', 'new', 'featured'),\n metadata JSON,\n stock INT UNSIGNED DEFAULT 0,\n is_active TINYINT(1) DEFAULT 1,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (tenant_id) REFERENCES tenants(id),\n INDEX idx_tenant (tenant_id),\n INDEX idx_category (category),\n FULLTEXT INDEX idx_name_desc (name, description)\n) ENGINE=InnoDB;\n```\n\n---\n\n## Migrated DSQL Schema\n\n```sql\n-- Step 1: Create table (one DDL per transaction)\nCREATE TABLE products (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n tenant_id VARCHAR(255) NOT NULL,\n name VARCHAR(255) NOT NULL,\n description TEXT,\n price DECIMAL(10,2) NOT NULL,\n category VARCHAR(255) DEFAULT 'other' CHECK (category IN ('electronics', 'clothing', 'food', 'other')),\n tags TEXT,\n metadata TEXT,\n stock INTEGER DEFAULT 0 CHECK (stock >= 0),\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Step 2: Create indexes (each in separate transaction, MUST use ASYNC)\nCREATE INDEX ASYNC idx_products_tenant ON products(tenant_id);\nCREATE INDEX ASYNC idx_products_category ON products(tenant_id, category);\n-- MUST implement text search at application layer for FULLTEXT index equivalent\n```\n\n---\n\n## Migration Decisions Summary\n\n| MySQL Feature | DSQL Decision |\n| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------ |\n| `AUTO_INCREMENT` | UUID with `gen_random_uuid()`, or IDENTITY column with CACHE, or SEQUENCE (see [AUTO_INCREMENT Migration](ddl-auto-increment.md#auto_increment-migration)) |\n| `INT` tenant_id | `VARCHAR(255)` for multi-tenant pattern |\n| `MEDIUMTEXT` | `TEXT` |\n| `ENUM(...)` | `VARCHAR(255)` with `CHECK` constraint |\n| `SET(...)` | `TEXT` (comma-separated) |\n| `JSON` | `TEXT` (JSON.stringify) |\n| `UNSIGNED` | `CHECK (col >= 0)` |\n| `TINYINT(1)` | `BOOLEAN` |\n| `DATETIME` | `TIMESTAMP` |\n| `ON UPDATE CURRENT_TIMESTAMP` | Application-layer `SET updated_at = CURRENT_TIMESTAMP` |\n| `FOREIGN KEY` | Application-layer referential integrity |\n| `INDEX` | `CREATE INDEX ASYNC` |\n| `FULLTEXT INDEX` | Application-layer text search |\n| `ENGINE=InnoDB` | MUST omit |\n\n---\n\n## Best Practices Summary\n\n### User Verification (CRITICAL)\n\n- **MUST present** complete migration plan to user before any execution\n- **MUST obtain** explicit user confirmation before DROP TABLE operations\n- **MUST verify** with user at each checkpoint during migration\n- **MUST obtain** explicit user approval before proceeding with destructive actions\n- **MUST recommend** testing migrations on non-production data first\n- **MUST confirm** user has backup or accepts data loss risk\n\n### MySQL-Specific Migration Rules\n\n- **MUST map** all MySQL data types to DSQL equivalents before creating tables\n- **MUST convert** AUTO_INCREMENT to one of: UUID with `gen_random_uuid()` (preferred for distributed workloads), IDENTITY column with `GENERATED AS IDENTITY (CACHE ...)`, or explicit SEQUENCE. When choosing integer auto-increment, ALWAYS use `GENERATED AS IDENTITY` syntax (not SERIAL). See [AUTO_INCREMENT Migration](ddl-auto-increment.md#auto_increment-migration).\n- **MUST replace** ENUM with VARCHAR and CHECK constraint\n- **MUST replace** SET with TEXT (comma-separated)\n- **MUST replace** JSON columns with TEXT\n- **MUST replace** FOREIGN KEY constraints with application-layer referential integrity\n- **MUST replace** ON UPDATE CURRENT_TIMESTAMP with application-layer updates\n- **MUST convert** all index creation to use CREATE INDEX ASYNC\n- **MUST omit** ENGINE, CHARSET, COLLATE, and other MySQL-specific table options\n- **MUST replace** UNSIGNED with CHECK (col >= 0) constraint\n- **MUST convert** TINYINT(1) to BOOLEAN\n\n### Technical Requirements\n\n- **MUST validate** data compatibility before type changes\n- **MUST batch** tables exceeding 3,000 rows\n- **MUST verify** row counts before and after migration\n- **MUST recreate** indexes after table swap using ASYNC\n- **MUST verify** new table before dropping original table\n- **PREFER** cursor-based batching for very large tables\n- **PREFER** batches of 500-1,000 rows for optimal throughput\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6935,"content_sha256":"391a93021f6229420d329505df72d0c6dae61a8c0535debe39856b0043166f66"},{"filename":"references/mysql-migrations/type-mapping.md","content":"# MySQL to DSQL Migration: Type Mapping & Feature Alternatives\n\nThis guide provides migration patterns for converting MySQL DDL operations to Aurora DSQL-compatible equivalents, including the **Table Recreation Pattern** for schema modifications that require rebuilding tables.\n\nFor DDL operation details, see [ddl-operations.md](ddl-operations.md). For a full migration example, see [full-example.md](full-example.md).\n\n---\n\n## CRITICAL: Destructive Operations Warning\n\n**The Table Recreation Pattern involves DESTRUCTIVE operations that can result in DATA LOSS.**\n\nTable recreation requires dropping the original table, which is **irreversible**. If any step fails after the original table is dropped, data may be permanently lost.\n\n### Mandatory User Verification Requirements\n\nAgents MUST obtain explicit user approval before executing migrations on live tables:\n\n1. **MUST present the complete migration plan** to the user before any execution\n2. **MUST clearly state** that this operation will DROP the original table\n3. **MUST confirm** the user has a current backup or accepts the risk of data loss\n4. **MUST verify with the user** at each checkpoint before proceeding:\n - Before creating the new table structure\n - Before beginning data migration\n - Before dropping the original table (CRITICAL CHECKPOINT)\n - Before renaming the new table\n5. **MUST NOT proceed** with any destructive action without explicit user confirmation\n6. **MUST recommend** performing migrations on non-production environments first\n\n### Risk Acknowledgment\n\nBefore proceeding, the user MUST confirm:\n\n- [ ] They understand this is a destructive operation\n- [ ] They have a backup of the table data (or accept the risk)\n- [ ] They approve the agent to execute each step with verification\n- [ ] They understand the migration cannot be automatically rolled back after DROP TABLE\n\n---\n\n## MySQL Data Type Mapping to DSQL\n\nMap MySQL data types to their DSQL equivalents.\n\n### Numeric Types\n\n| MySQL Type | DSQL Equivalent | Notes |\n| --------------------------- | ----------------------------------------------- | ------------------------------------------------------ |\n| TINYINT | SMALLINT | DSQL has no TINYINT; SMALLINT is smallest integer type |\n| SMALLINT | SMALLINT | Direct equivalent |\n| MEDIUMINT | INTEGER | DSQL has no MEDIUMINT; use INTEGER |\n| INT / INTEGER | INTEGER | Direct equivalent |\n| BIGINT | BIGINT | Direct equivalent |\n| TINYINT(1) | BOOLEAN | MySQL convention for booleans maps to native BOOLEAN |\n| FLOAT | REAL | Direct equivalent |\n| DOUBLE | DOUBLE PRECISION | Direct equivalent |\n| DECIMAL(p,s) / NUMERIC(p,s) | DECIMAL(p,s) / NUMERIC(p,s) | Direct equivalent |\n| BIT(1) | BOOLEAN | Single bit maps to BOOLEAN |\n| BIT(n) | BYTEA | Multi-bit maps to BYTEA |\n| UNSIGNED integers | Use next-larger signed type or CHECK constraint | DSQL has no UNSIGNED; use CHECK (col >= 0) |\n\n### String Types\n\n| MySQL Type | DSQL Equivalent | Notes |\n| ----------------- | ---------------------------------- | ---------------------------------------------------------------------------------------- |\n| CHAR(n) | CHAR(n) | Direct equivalent |\n| VARCHAR(n) | VARCHAR(n) | Direct equivalent |\n| TINYTEXT | TEXT | DSQL uses TEXT for all unbounded strings |\n| TEXT | TEXT | Direct equivalent |\n| MEDIUMTEXT | TEXT | DSQL uses TEXT for all unbounded strings |\n| LONGTEXT | TEXT | DSQL uses TEXT for all unbounded strings |\n| ENUM('a','b','c') | VARCHAR(255) with CHECK constraint | See [ENUM Migration](ddl-type-alternatives.md#enum-type-migration) |\n| SET('a','b','c') | TEXT | Store as comma-separated TEXT; see [SET Migration](ddl-type-alternatives.md#set-type-migration) |\n\n### Date/Time Types\n\n| MySQL Type | DSQL Equivalent | Notes |\n| ---------- | --------------- | ---------------------------------------------------------------- |\n| DATE | DATE | Direct equivalent |\n| DATETIME | TIMESTAMP | DATETIME maps to TIMESTAMP |\n| TIMESTAMP | TIMESTAMP | Direct equivalent; MUST manage auto-updates in application layer |\n| TIME | TIME | Direct equivalent |\n| YEAR | INTEGER | Store as 4-digit integer |\n\n### Binary Types\n\n| MySQL Type | DSQL Equivalent | Notes |\n| ------------ | --------------- | ----------------------------------- |\n| BINARY(n) | BYTEA | DSQL uses BYTEA for binary data |\n| VARBINARY(n) | BYTEA | DSQL uses BYTEA for binary data |\n| TINYBLOB | BYTEA | DSQL uses BYTEA for all binary data |\n| BLOB | BYTEA | DSQL uses BYTEA for all binary data |\n| MEDIUMBLOB | BYTEA | DSQL uses BYTEA for all binary data |\n| LONGBLOB | BYTEA | DSQL uses BYTEA for all binary data |\n\n### Other Types\n\n| MySQL Type | DSQL Equivalent | Notes |\n| -------------- | --------------------------------------------------------- | ------------------------------------------------------------------------------------------------ |\n| JSON | TEXT | MUST store as TEXT |\n| AUTO_INCREMENT | UUID with gen_random_uuid(), IDENTITY column, or SEQUENCE | See [AUTO_INCREMENT Migration](ddl-auto-increment.md#auto_increment-migration) for all three options |\n\n---\n\n## MySQL Features Requiring DSQL Alternatives\n\nMUST use the following DSQL alternatives for these MySQL features:\n\n| MySQL Feature | DSQL Alternative |\n| ---------------------------------- | --------------------------------------------------- |\n| FOREIGN KEY constraints | Application-layer referential integrity |\n| FULLTEXT indexes | Application-layer text search |\n| SPATIAL indexes | Application-layer spatial queries |\n| ENGINE=InnoDB/MyISAM | MUST omit (DSQL manages storage automatically) |\n| ON UPDATE CURRENT_TIMESTAMP | Application-layer timestamp management |\n| GENERATED columns (virtual/stored) | Application-layer computation |\n| PARTITION BY | MUST omit (DSQL manages distribution automatically) |\n| TRIGGERS | Application-layer logic |\n| STORED PROCEDURES / FUNCTIONS | Application-layer logic |\n\n---\n\n## MySQL DDL Operation Mapping\n\n### Directly Supported Operations\n\nThese MySQL operations have direct DSQL equivalents:\n\n| MySQL DDL | DSQL Equivalent |\n| ------------------------------------------ | --------------------------------------------------- |\n| `CREATE TABLE ...` | `CREATE TABLE ...` (with type adjustments) |\n| `DROP TABLE table_name` | `DROP TABLE table_name` |\n| `ALTER TABLE ... ADD COLUMN col type` | `ALTER TABLE ... ADD COLUMN col type` |\n| `ALTER TABLE ... RENAME COLUMN old TO new` | `ALTER TABLE ... RENAME COLUMN old TO new` |\n| `ALTER TABLE ... RENAME TO new_name` | `ALTER TABLE ... RENAME TO new_name` |\n| `CREATE INDEX idx ON t(col)` | `CREATE INDEX ASYNC idx ON t(col)` (MUST use ASYNC) |\n| `DROP INDEX idx ON t` | `DROP INDEX idx` (MUST omit the ON clause) |\n\n### Operations Requiring Table Recreation Pattern\n\nThese MySQL operations MUST use the **Table Recreation Pattern** in DSQL:\n\n| MySQL DDL | DSQL Approach |\n| -------------------------------------------------------------- | ------------------------------------------------------------- |\n| `ALTER TABLE ... MODIFY COLUMN col new_type` | Table recreation with type cast |\n| `ALTER TABLE ... CHANGE COLUMN old new new_type` | Table recreation (type change) or RENAME COLUMN (rename only) |\n| `ALTER TABLE ... ALTER COLUMN col datatype` | Table recreation with type cast |\n| `ALTER TABLE ... DROP COLUMN col` | Table recreation excluding the column |\n| `ALTER TABLE ... ALTER COLUMN col SET DEFAULT val` | Table recreation with DEFAULT in new definition |\n| `ALTER TABLE ... ALTER COLUMN col DROP DEFAULT` | Table recreation without DEFAULT |\n| `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE` | Table recreation with constraint |\n| `ALTER TABLE ... ADD CONSTRAINT ... CHECK` | Table recreation with constraint |\n| `ALTER TABLE ... DROP CONSTRAINT ...` | Table recreation without constraint |\n| `ALTER TABLE ... DROP PRIMARY KEY, ADD PRIMARY KEY (new_cols)` | Table recreation with new PK |\n\n### Operations Requiring Application-Layer Implementation\n\nMUST implement these MySQL operations at the application layer:\n\n| MySQL DDL | DSQL Approach |\n| -------------------------------------- | --------------------------------------------------------- |\n| `ALTER TABLE ... ADD FOREIGN KEY` | MUST implement referential integrity in application layer |\n| `ALTER TABLE ... ADD FULLTEXT INDEX` | MUST implement text search in application layer |\n| `ALTER TABLE ... ADD SPATIAL INDEX` | MUST implement spatial queries in application layer |\n| `ALTER TABLE ... ENGINE=...` | MUST omit |\n| `ALTER TABLE ... AUTO_INCREMENT=...` | Use SEQUENCE with setval() or IDENTITY column |\n| `CREATE TRIGGER` | MUST implement in application-layer logic |\n| `CREATE PROCEDURE` / `CREATE FUNCTION` | MUST implement in application-layer logic |\n\n---\n\n## MySQL-to-DSQL Type Conversion Validation Matrix\n\n| MySQL From Type | DSQL To Type | Validation |\n| ----------------------------- | ------------------ | ------------------------------------------------------- |\n| VARCHAR -> INT/INTEGER | VARCHAR -> INTEGER | MUST validate all values are numeric |\n| VARCHAR -> TINYINT(1)/BOOLEAN | VARCHAR -> BOOLEAN | MUST validate values are 'true'/'false'/'t'/'f'/'1'/'0' |\n| INT/INTEGER -> VARCHAR | INTEGER -> VARCHAR | Safe conversion |\n| TEXT -> VARCHAR(n) | TEXT -> VARCHAR(n) | MUST validate max length \u003c= n |\n| DATETIME -> DATE | TIMESTAMP -> DATE | Safe (truncates time) |\n| INT -> DECIMAL | INTEGER -> DECIMAL | Safe conversion |\n| ENUM -> VARCHAR | VARCHAR -> VARCHAR | Safe (already stored as VARCHAR in DSQL) |\n| MEDIUMINT -> BIGINT | INTEGER -> BIGINT | Safe conversion |\n| FLOAT -> DECIMAL | REAL -> DECIMAL | May lose precision; MUST validate acceptable |\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":13749,"content_sha256":"0949d33728d6d83b2285e44d5510f2c2a5c8972e1bcde38df8bff1f47b8fdc30"},{"filename":"references/onboarding.md","content":"# Aurora DSQL Get Started Guide\n\n## Overview\n\nThis guide provides steps to help users get started with Aurora DSQL in their project. It sets up their DSQL cluster with IAM authentication and connects their database to their code by understanding the context within the codebase.\n\n## Use Case\n\nThese guidelines apply when users say \"Get started with DSQL\" or similar phrases. The user's codebase may be mature (with existing database connections) or have little to no code - the guidelines should apply to both cases.\n\n## Contents\n\n- [Overview](#overview)\n- [Use Case](#use-case)\n- [Agent Communication Style](#agent-communication-style)\n- [Get Started with DSQL (Interactive Guide)](#get-started-with-dsql-interactive-guide) — 10-step linear walkthrough\n- [DSQL Best Practices](#dsql-best-practices)\n- [Additional Resources](#additional-resources)\n\n## Agent Communication Style\n\n**Keep all responses succinct:**\n\n- ALWAYS tell the user what you did.\n - Responses MUST be concise and concrete.\n - ALWAYS contain descriptions to necessary steps.\n - ALWAYS remove unnecessary verbiage.\n - Example:\n - \"Created an inventory table with 4 columns\"\n - \"Updated the product column to be NOT NULL\"\n- Ask direct questions when needed:\n - ALWAYS ask clarifying questions to avoid inaccurate assumptions\n - User ambiguity SHOULD result in questions.\n - MUST clarify incompatible user decisions\n - Example:\n - \"What column names would you like in this table?\"\n - \"What is the column name of the primary key?\"\n - \"JSON must be serialized. Would you like to stringify the JSON to serialize it as TEXT?\"\n\n**Examples:**\n\n- **Good**: \"Generated auth token. Ready to connect with psql?\"\n- **Bad**: \"I'm going to generate an authentication token using the AWS CLI which will allow you to connect to your database. This token will be valid for...\"\n\n---\n\n## Get Started with DSQL (Interactive Guide)\n\n**TRIGGER PHRASE:** When the user says \"Get started with DSQL\", \"Get started with Aurora DSQL\", or similar phrases, provide an interactive onboarding experience by following these steps:\n\n**Before starting:** Let the user know they can pause and resume anytime by saying \"Continue with DSQL setup\" if they need to come back later.\n\n**RESUME TRIGGER:** If the user says \"Continue with DSQL setup\" or similar, check what's already configured (AWS credentials, clusters, AWS MCP Server installation if applicable, connection tested) and resume from where they left off. Ask them which step they'd like to continue from or analyze their setup to determine automatically.\n\n### Step 1: Verify Prerequisites\n\n**Check AWS credentials:**\n\n```bash\naws sts get-caller-identity\n```\n\n**If not configured:**\n\n- Guide them through `aws configure`\n- MUST verify IAM permissions include `dsql:CreateCluster`, `dsql:GetCluster`, `dsql:DbConnectAdmin`\n- For initial setup, use a scoped inline policy with only the minimum permissions needed. `dsql:CreateCluster` and `dsql:ListClusters` cannot target a specific cluster ARN (the cluster does not yet exist, and `ListClusters` is a list operation), so they go in a separate statement with `Resource: \"*\"`. Cluster-scoped actions stay on the specific ARN:\n\n ```json\n {\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Effect\": \"Allow\",\n \"Action\": [\n \"dsql:CreateCluster\",\n \"dsql:ListClusters\"\n ],\n \"Resource\": \"*\"\n },\n {\n \"Effect\": \"Allow\",\n \"Action\": [\n \"dsql:GetCluster\",\n \"dsql:DeleteCluster\",\n \"dsql:DbConnectAdmin\"\n ],\n \"Resource\": \"arn:aws:dsql:\u003cregion>:\u003caccount-id>:cluster/\u003ccluster-id>\"\n }\n ]\n }\n ```\n\n Replace `\u003ccluster-id>` with the actual cluster ID returned from cluster creation. After Step 4, narrow the first statement further by adding an `aws:RequestTag/\u003ckey>` condition to `dsql:CreateCluster` (or remove `dsql:CreateCluster` entirely once the cluster exists). `aws:RequestTag` is required here because `dsql:CreateCluster` runs before any cluster (and any resource tag) exists. Revoke `dsql:DbConnectAdmin` after scoped database roles are established (Step 9).\n\n**Check PostgreSQL client:**\n\n```bash\npsql --version\n```\n\n**If missing OR version \u003c=14:**\nDSQL requires SNI support from psql >=14.\n\n- macOS: `brew install postgresql@17`\n- Linux (Debian/Ubuntu): `sudo apt-get install postgresql-client`\n- Linux (RHEL/CentOS/Amazon Linux):\n\n ```bash\n sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm\n sudo yum install -y postgresql17\n ```\n\n### Step 2: Check for Existing Clusters\n\n**Set region (uses AWS_REGION or REGION if set, defaults to us-east-1):**\n\n```bash\nREGION=${AWS_REGION:-${REGION:-us-east-1}}\necho $REGION\n```\n\n**List clusters in the region:**\n\n```bash\naws dsql list-clusters --region $REGION\n```\n\n**If they have NO clusters:**\n\n- Ask: \"Would you like to create a new DSQL cluster in $REGION or a different region?\"\n - If yes, proceed to create single-region cluster\n - If they want different region, ask which one and update REGION variable\n\n**If they have ANY clusters:**\n\n- List ALL cluster identifiers with creation dates and status\n- Ask: \"Would you like to use one of these clusters or create a new one?\"\n - If using existing, proceed to Step 3.\n - If creating new:\n - \"Which region would you like to create a enw cluster in?\"\n - Immediately update REGION variable\n- Confirm all selections before proceeding.\n\n**Create cluster command (if needed):**\n\n```bash\naws dsql create-cluster --region $REGION --tags '{\"Name\":\"my-dsql-cluster\",\"created_by\":\"\u003cmodel-id>\"}'\n```\n\n**Wait for ACTIVE status** (takes ~60 seconds):\n\n```bash\naws dsql get-cluster --identifier CLUSTER_ID --region $REGION\n```\n\n### Step 3: Get Cluster Connection Details\n\n**Construct cluster endpoint:**\n\n```bash\nCLUSTER_ID=\"\u003cselected-cluster-id>\"\nCLUSTER_ENDPOINT=\"${CLUSTER_ID}.dsql.${REGION}.on.aws\"\necho $CLUSTER_ENDPOINT\n```\n\n**Store endpoint for their project environment:**\n\n- Check for `.env` file or environment config\n- Add or update: `DSQL_ENDPOINT=\u003cendpoint>`\n- Add region: `AWS_REGION=$REGION`\n- ALWAYS try reading `.env` first before modifying\n- If file is unreadable, use: `echo \"DSQL_ENDPOINT=$CLUSTER_ENDPOINT\" >> .env`\n\n### Step 4: Set Up the AWS MCP Server (Optional)\n\nWould the user like AWS knowledge tools (documentation search/read, AWS API access) wired into\ntheir coding assistant?\n\nIf so, install the [AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html) per the AWS docs. It provides:\n\n- `aws___search_documentation` / `aws___read_documentation` / `aws___recommend` — DSQL docs lookup\n- `aws___call_aws` — authenticated AWS API calls (for `dsql:` actions like cluster management)\n- `aws___run_script` — sandboxed Python with AWS API access\n\nA custom DSQL-specific MCP is **optional**. If the user has one configured already, it can stay\nalongside the AWS MCP Server. For ad-hoc DSQL queries, this skill PREFERS direct `psql` via\n[`scripts/psql-connect.sh`](../scripts/psql-connect.sh) over MCP-mediated execution.\n\n### Step 5: Test Connection\n\n> **⚠️ Security Note:** The admin connection (`generate-db-connect-admin-auth-token` + `admin` user) should **only** be used for the initial setup steps below (creating roles, granting permissions). Once scoped roles are established in Step 9, all subsequent operations should use the scoped role with `generate-db-connect-auth-token`. Consider revoking `dsql:DbConnectAdmin` from the setup IAM role after scoped roles are in place.\n\n**Generate authentication token and connect:**\n\n```bash\nexport PGPASSWORD=$(aws dsql generate-db-connect-admin-auth-token \\\n --region $REGION \\\n --hostname $CLUSTER_ENDPOINT \\\n --expires-in 3600)\n\nexport PGSSLMODE=verify-full\nexport PGAPPNAME=\"\u003capp-name>/\u003cmodel-id>\"\n\npsql --quiet -h $CLUSTER_ENDPOINT -U admin -d postgres\n```\n\n**Verify with test query:**\n\n```sql\nSELECT current_database(), version();\n```\n\n**If connection fails:**\n\n- Check token expiration (regenerate if needed)\n- Verify SSL mode is set\n- Confirm cluster is ACTIVE\n- Check IAM permissions\n\n### Step 6: Understand the Project\n\n**First, check if this is an empty/new project:**\n\n- Look for existing source code, routes, or application logic\n- Check if it's just minimal boilerplate\n\n**If empty or near-empty project:**\n\n- Ask briefly (1-2 questions): What are they building? Any specific tech preferences?\n- Remember context for subsequent steps\n\n**If established project:**\n\n- Skip questions - infer from codebase\n- Check for existing database code or ORMs\n- Update relevant code to use DSQL\n\n**ALWAYS reference [`./development-guide.md`](./development-guide.md) before making schema changes**\n\n### Step 7: Install Database Driver\n\n**Based on their language, install appropriate driver (some examples):**\n\n**JavaScript/TypeScript:**\n\n```bash\nnpm install @aws/aurora-dsql-node-postgres-connector tsx\n```\n\n**Python:**\n\n```bash\npip install aurora-dsql-python-connector 'psycopg[binary]' psycopg-pool\n```\n\n**Go:**\n\n```bash\ngo get github.com/awslabs/aurora-dsql-connectors/go/pgx\n```\n\n**Rust:**\n\n```bash\ncargo add aurora-dsql-sqlx-connector --features pool,occ\ncargo add sqlx tokio --features postgres,runtime-tokio-native-tls,full\n```\n\n**For implementation patterns, reference [`./dsql-examples.md`](./dsql-examples.md) and [`./language.md`](./language.md)**\n\n### Step 8: Schema Setup\n\n**Check for existing schema:**\n\n- Search for `.sql` files, migration folders, ORM schemas (Prisma, Drizzle, TypeORM)\n\n**If existing schema found:**\n\n- Show what you found\n- Ask: \"Found existing schema definitions. Want to migrate these to DSQL?\"\n- If yes, MUST verify DSQL compatibility:\n - No SERIAL types (use `GENERATED AS IDENTITY` with sequences, or UUID)\n - No foreign keys (implement in application)\n - No array/JSON column types (serialize as TEXT)\n - Reference [`./development-guide.md`](./development-guide.md) for full constraints\n\n**If no schema found:**\n\n- Ask if they want to:\n 1. Create simple example table\n 2. Design custom schema together\n 3. Skip for now\n\n**If creating example table:**\n\nUse `scripts/psql-connect.sh --admin` to execute:\n\n```sql\nCREATE TABLE users (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n email VARCHAR(255) UNIQUE NOT NULL,\n name VARCHAR(255),\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE INDEX ASYNC idx_users_email ON users(email);\n```\n\n**For custom schema:**\n\n- Ask about their app's needs\n- Design tables following DSQL constraints\n- Reference [`./dsql-examples.md`](./dsql-examples.md) for patterns\n- ALWAYS use `CREATE INDEX ASYNC` for all indexes\n\n### Step 9: Set Up Scoped Database Roles\n\n**Recommend creating scoped roles before application development begins.**\n\n- Ask: \"Would you like to set up scoped database roles for your application? This is recommended over using `admin` directly.\"\n- If yes, follow [access-control.md](./access-control.md) for detailed guidance\n- At minimum, guide creating one application role:\n\n```sql\n-- As admin\nCREATE ROLE app_user WITH LOGIN;\nAWS IAM GRANT app_user TO 'arn:aws:iam::\u003caccount-id>:role/\u003cAppIAMRole>';\nGRANT USAGE ON SCHEMA public TO app_user;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;\n```\n\n- If the application handles sensitive user data, recommend a separate schema:\n\n```sql\nCREATE SCHEMA users_schema;\nGRANT USAGE ON SCHEMA users_schema TO app_user;\nGRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA users_schema TO app_user;\nGRANT CREATE ON SCHEMA users_schema TO app_user;\n```\n\n- After setup, application connections should use `generate-db-connect-auth-token` (not the admin variant)\n\n### Step 10: What's Next\n\nLet them know you're ready to help with more:\n\n\"You're all set! Here are some things I can help with - feel free to ask about any of these (or anything else):\n\n- Schema design and migrations following DSQL best practices\n- Writing queries with proper tenant isolation\n- Connection pooling and token refresh strategies\n- Multi-region cluster setup for high availability\n- Performance optimization with indexes and query patterns\n- Setting up additional scoped roles for different services\"\n\n### Important Notes:\n\n- ALWAYS be succinct - guide step-by-step without verbose explanations\n- ALWAYS check [`./development-guide.md`](./development-guide.md) before schema operations\n- ALWAYS prefer `scripts/psql-connect.sh` for DSQL queries; reach for the AWS MCP Server when AWS knowledge or `dsql:` API calls are needed\n- ALWAYS validate DSQL compatibility for existing schemas\n- ALWAYS provide working, tested commands\n- MUST handle token expiration gracefully (15-minute default, 1-hour recommended)\n\n---\n\n## DSQL Best Practices\n\n### Critical Constraints\n\n**ALWAYS follow these rules:**\n\n1. **Indexes:** Use `CREATE INDEX ASYNC` - synchronous index creation not supported\n2. **Serialization:** Store arrays/JSON as TEXT (comma-separated or JSON.stringify)\n3. **Referential Integrity:** Implement foreign key validation in application code\n4. **DDL Operations:** Execute one DDL per transaction, no mixing with DML\n5. **Transaction Limits:** Maximum 3,000 row modifications, 10 MiB data size per transaction\n6. **Token Refresh:** Regenerate auth tokens before 15-minute expiration\n7. **SSL Required:** Always set `PGSSLMODE=verify-full` or `sslmode=verify-full` (use `require` as a fallback only when the CA bundle is unavailable)\n\n### DSQL-Specific Features\n\n**Leverage Aurora DSQL capabilities:**\n\n1. **Serverless:** True scale-to-zero with consumption-based pricing\n2. **Distributed:** Active-active writes across multiple regions\n3. **Strong Consistency:** Immediate read-your-writes across all regions\n4. **IAM Authentication:** No password management, automatic token rotation\n5. **PostgreSQL Compatible:** Supports many [database drivers, ORMs, and adapters](./auth/connectivity-tools.md) — see the [AWS DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html) for the current list.\n\n**For detailed patterns, see [`./development-guide.md`](./development-guide.md)**\n\n## Additional Resources\n\n- [Aurora DSQL Documentation](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/)\n- [Aurora DSQL Starter Kit](https://github.com/awslabs/aurora-dsql-starter-kit/tree/main)\n- [Aurora DSQL Connectivity Tools (drivers, ORMs, samples)](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n- [IAM Authentication Guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/using-database-and-iam-roles.html)\n- [Getting Started Guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/getting-started.html)\n- [PostgreSQL Compatibility](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html)\n- [Incompatible PostgreSQL Features](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-unsupported-features.html)\n- [CloudFormation Resource](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-dsql-cluster.html)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":15148,"content_sha256":"ecefd12afa762bc4002b4b1e7f4292ed404a85ebc1603c086aa953bc17bd856a"},{"filename":"references/platforms/claude-code.md","content":"# MCP Setup: Claude Code\n\nPart of [MCP Server Setup](../mcp-setup.md). The skill PREFERS direct `psql` for ad-hoc DSQL\nqueries (via [`scripts/psql-connect.sh`](../../scripts/psql-connect.sh)) and the\n[AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html) for AWS\nknowledge and AWS API access.\n\n---\n\n## AWS MCP Server (recommended)\n\nFollow the official setup guide at\n[Setting up the AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html).\nThe AWS docs page tracks the canonical install command, scopes (`local`, `project`, `user`), and\nauth configuration for Claude Code — defer to it rather than caching the invocation here.\n\n### Choosing the Right Scope\n\nClaude Code offers 3 different scopes: local (default), project, and user.\n\n1. **Local-scoped** servers represent the default configuration level and are stored in\n `~/.claude.json` under your project's path. They're **both** private to you and only accessible\n within the current project directory. This is the default `scope` when creating MCP servers.\n2. **Project-scoped** servers **enable team collaboration** while still only being accessible in a\n project directory. Project-scoped servers add a `.mcp.json` file at your project's root directory.\n This file is designed to be checked into version control, ensuring all team members have access\n to the same MCP tools and services.\n3. **User-scoped** servers are stored in `~/.claude.json` and are available across all projects on\n your machine while remaining **private to your user account.**\n\n### Verification\n\nAfter setup:\n\n```bash\nclaude mcp list\n```\n\nYou should see the AWS MCP Server listed and connected.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1723,"content_sha256":"c1dd3cf7c0cc15bb24d36e393833ff4934ed2e26ce4c8a1a6286e5b2d1f0877d"},{"filename":"references/platforms/codex.md","content":"# MCP Setup: Codex\n\nPart of [MCP Server Setup](../mcp-setup.md). The skill PREFERS direct `psql` for ad-hoc DSQL\nqueries (via [`scripts/psql-connect.sh`](../../scripts/psql-connect.sh)) and the\n[AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html) for AWS\nknowledge and AWS API access.\n\n---\n\n## AWS MCP Server (recommended)\n\nFollow the official setup guide at\n[Setting up the AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html)\nfor the Codex-specific install command and `~/.codex/config.toml` entry.\n\n### Verification\n\n```bash\ncodex mcp\n```\n\nYou should see the AWS MCP Server listed and connected.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":686,"content_sha256":"49b73a6bb5491fdbe1c60f3211569219a02e6194f97d828c8eba5d5e8e27a892"},{"filename":"references/platforms/gemini.md","content":"# MCP Setup: Gemini\n\nPart of [MCP Server Setup](../mcp-setup.md). The skill PREFERS direct `psql` for ad-hoc DSQL\nqueries (via [`scripts/psql-connect.sh`](../../scripts/psql-connect.sh)) and the\n[AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html) for AWS\nknowledge and AWS API access.\n\n---\n\n## AWS MCP Server (recommended)\n\nFollow the official setup guide at\n[Setting up the AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html)\nfor the Gemini-specific install command and config-file paths.\n\n### Choosing the Right Scope\n\nGemini offers 2 scopes: project (default) and user.\n\n1. **Project-Scoped** servers are only accessible from the project's root directory and added to\n the project configuration: `.gemini/settings.json`. Useful for project-specific tools that\n should stay within the codebase.\n2. **User-Scoped** servers are accessible from all projects you work on with the Gemini CLI and\n added to global configuration: `~/.gemini/settings.json`.\n\n### Verification\n\n```bash\ngemini mcp list\n```\n\nYou should see the AWS MCP Server listed and connected.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1151,"content_sha256":"6050815039ed674a0b0bc81b0a840699f23588014245506cb027ca2730dbe2c6"},{"filename":"references/platforms/kiro.md","content":"# MCP Setup: Kiro\n\nPart of [MCP Server Setup](../mcp-setup.md). The skill PREFERS direct `psql` for ad-hoc DSQL\nqueries (via [`scripts/psql-connect.sh`](../../scripts/psql-connect.sh)) and the\n[AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html) for AWS\nknowledge and AWS API access.\n\n---\n\n## AWS MCP Server (recommended)\n\nFollow the official setup guide at\n[Setting up the AWS MCP Server](https://docs.aws.amazon.com/aws-mcp/latest/userguide/getting-started-aws-mcp-server.html)\nfor the Kiro-specific install instructions.\n\n### Choosing the Right Scope\n\nKiro offers 2 scopes: workspace (default) and user.\n\n1. **Workspace-Scoped** servers live at `.kiro/settings/mcp.json` in the project root and are\n only accessible from the current workspace. Useful for project-specific tools that should\n stay within the codebase and can be checked into version control.\n2. **User-Scoped** servers live at `~/.kiro/settings/mcp.json` and are accessible across all\n workspaces the user opens in Kiro.\n\nWhen both files define the same server name, **workspace settings take precedence**.\n\n### Kiro-Specific Fields\n\n- `disabled` (bool) — set `true` to suspend a server without deleting its entry\n- `autoApprove` (string array) — tool names that skip the per-call approval prompt.\n Leave empty to require approval for every call. For tools that can mutate state\n (cluster lifecycle APIs, write SQL paths), keep this empty so the user approves each call.\n- `disabledTools` (string array) — hide specific tools from this server\n- `env` supports `${VAR}` expansion from the shell environment,\n e.g. `\"AWS_PROFILE\": \"${DSQL_PROFILE}\"`\n\n### Verification\n\nOpen the command palette (`Cmd/Ctrl+Shift+P`) → search `MCP` → open the MCP view in the Kiro\npanel. The AWS MCP Server should appear in the server list with an active status.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1863,"content_sha256":"2b293d1f792c00fdd2b939417ccea106ba9b6a335a69bd076bfc704a1764bad0"},{"filename":"references/query-plan/catalog-queries.md","content":"# Catalog Queries Reference\n\nExact SQL for interrogating optimizer statistics and actual cardinalities against the DSQL cluster.\n\n## Table of Contents\n\n1. [Table-Level Statistics (pg_class)](#table-level-statistics)\n2. [Column Statistics (pg_stats)](#column-statistics)\n3. [Index Definitions](#index-definitions)\n4. [Actual Row Counts](#actual-row-counts)\n5. [Actual Distinct Counts](#actual-distinct-counts)\n6. [Value Distribution Analysis](#value-distribution-analysis)\n\n---\n\n## Table-Level Statistics\n\nRetrieve optimizer's view of table size for all referenced tables:\n\n```sql\nSELECT\n schemaname,\n relname,\n reltuples::bigint AS estimated_rows,\n relpages\nFROM pg_class c\nJOIN pg_namespace n ON n.oid = c.relnamespace\nWHERE n.nspname = '{schema}'\n AND c.relname IN ('{table1}', '{table2}', '{table3}');\n```\n\nCompare `reltuples` against actual `COUNT(*)`. A divergence >20% on the table-stats snapshot indicates stale `reltuples` requiring `ANALYZE`. This is distinct from the row-estimate-vs-actual error thresholds used for plan findings (see plan-interpretation.md: 2x–5x minor, 5x–50x significant, 50x+ severe).\n\n## Column Statistics\n\nRetrieve statistics for columns involved in joins, WHERE clauses, and estimation errors:\n\n```sql\nSELECT\n tablename,\n attname,\n null_frac,\n n_distinct,\n most_common_vals,\n most_common_freqs,\n histogram_bounds,\n correlation\nFROM pg_stats\nWHERE schemaname = '{schema}'\n AND tablename = '{table}'\n AND attname IN ('{col1}', '{col2}');\n```\n\n**Key fields:**\n\n| Field | Use |\n| ------------------- | ------------------------------------------------------ |\n| `n_distinct` | Negative = fraction of rows; Positive = absolute count |\n| `most_common_vals` | Values the optimizer considers frequent |\n| `most_common_freqs` | Corresponding frequencies (sum \u003c 1.0) |\n| `histogram_bounds` | Equal-frequency bucket boundaries for non-MCV values |\n| `correlation` | Physical row order correlation (-1 to 1) |\n\n## Index Definitions\n\nRetrieve existing indexes on referenced tables. DSQL does not populate the cumulative `pg_stat_user_indexes` counters (`idx_scan`, `idx_tup_read`, `idx_tup_fetch`) that standard PostgreSQL exposes — infer index usage from the EXPLAIN plan instead.\n\n```sql\nSELECT\n tablename,\n indexname,\n indexdef\nFROM pg_indexes\nWHERE schemaname = '{schema}'\n AND tablename IN ('{table1}', '{table2}', '{table3}')\nORDER BY tablename, indexname;\n```\n\n## Actual Row Counts\n\nRetrieve ground-truth row counts for comparison against `pg_class.reltuples`:\n\n```sql\nSELECT COUNT(*) AS actual_rows FROM {schema}.{table};\n```\n\nRun for each referenced table. Present results as:\n\n| Table | pg_class.reltuples | Actual COUNT(*) | Difference |\n| ------ | ------------------ | --------------- | ------------------ |\n| table1 | N | M | X% over/undercount |\n\n## Actual Distinct Counts\n\nRetrieve actual distinct values for columns in joins and WHERE predicates:\n\n```sql\nSELECT COUNT(DISTINCT {column}) AS distinct_count FROM {schema}.{table};\n```\n\nCompare against `pg_stats.n_distinct`:\n\n- If `n_distinct` is positive: compare directly\n- If `n_distinct` is negative: multiply absolute value by actual row count to get estimated distinct count\n\n## Value Distribution Analysis\n\nFor columns with suspected data skew, retrieve the actual top-N value frequencies:\n\n```sql\nSELECT\n {column},\n COUNT(*) AS freq,\n ROUND(COUNT(*)::numeric / (SELECT COUNT(*) FROM {schema}.{table}), 5) AS fraction\nFROM {schema}.{table}\nGROUP BY {column}\nORDER BY freq DESC\nLIMIT 20;\n```\n\nCompare results against `most_common_vals` and `most_common_freqs` from pg_stats. Flag:\n\n- Values present in data but missing from `most_common_vals`\n- Values whose actual frequency differs >2x from `most_common_freqs`\n- Skewed distributions where top values account for >50% of rows\n\n### Correlated Predicate Verification\n\nTo verify predicate correlation, measure the actual combined selectivity:\n\n```sql\nSELECT COUNT(*) AS combined_count\nFROM {schema}.{table}\nWHERE {predicate1} AND {predicate2};\n```\n\nThen compare against the independence assumption:\n\n```\nExpected (independent) = (count_pred1 / total_rows) × (count_pred2 / total_rows) × total_rows\nActual = combined_count\nError = actual / expected\n```\n\nAn error >3x indicates significant predicate correlation.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":4447,"content_sha256":"863f00cf56d47c6e6b2a7d357a3f394172ec87af8cc85409e00016ceb31d32bb"},{"filename":"references/query-plan/guc-experiments.md","content":"# GUC Experiments and Redundant Predicate Testing\n\n## Table of Contents\n\n1. [GUC Experiment Procedure](#guc-experiment-procedure)\n2. [Transaction Isolation](#transaction-isolation)\n3. [Interpreting GUC Results](#interpreting-guc-results)\n4. [Redundant Predicate Testing](#redundant-predicate-testing)\n5. [Handling Regressions](#handling-regressions)\n\n---\n\n## GUC Experiment Procedure\n\nGUC (Grand Unified Configuration) experiments temporarily disable specific planner strategies to test whether viable alternatives exist.\n\n### Experiments to Run\n\nPer SKILL.md Phase 1, the `{original_sql}` reaching this phase is always a **SELECT** (DML is rewritten to SELECT before plan capture, INSERT and pl/pgsql are rejected). Execute two variants against that SELECT:\n\n**Experiment 1 — Default baseline** (read-only):\n\n```sql\nEXPLAIN ANALYZE VERBOSE {original_sql};\n```\n\nRun via `./scripts/psql-connect.sh --cluster \u003cid> --command \"EXPLAIN ANALYZE VERBOSE {original_sql}\"` (the wrapper accepts a single trailing semicolon) or your driver's read path.\n\n**Experiment 2 — Merge join only.** Needs `SET LOCAL` to scope GUC changes to a single transaction. The four statements MUST execute in one transaction so `SET LOCAL` takes effect. Use `--script` mode (multi-statement file via stdin); `--command` rejects multi-statement input.\n\n**Safety rules the caller MUST apply:**\n\n- `{original_sql}` **MUST** be a SELECT — verify by reading the first non-comment token. Reject and abort otherwise.\n- **MUST** interpolate `{original_sql}` only as a single trusted SELECT body. Do not concatenate it with another statement.\n- The script file MUST contain only the three `SET LOCAL` + one `EXPLAIN ANALYZE VERBOSE SELECT` statements wrapped in `BEGIN`/`COMMIT` — no INSERT/UPDATE/DELETE/DDL.\n- If any statement fails (the `EXPLAIN` errors, etc.), halt and report; do not chain additional recovery SQL.\n\nWrite the script then run it:\n\n```sql\n-- experiment-2.sql\nBEGIN;\nSET LOCAL enable_hashjoin = off;\nSET LOCAL enable_nestloop = off;\nSET LOCAL enable_mergejoin = on;\nEXPLAIN ANALYZE VERBOSE {original_sql};\nCOMMIT;\n```\n\n```bash\n./scripts/psql-connect.sh --cluster \u003cid> --script ./experiment-2.sql\n```\n\n`SET LOCAL` confines the GUC change to the surrounding transaction; the change is automatically discarded at commit.\n\n### Execution Gate\n\n| Original query time | Action |\n| ------------------- | -------------------------------------------------------------- |\n| ≤30 seconds | Perform both experiments |\n| >30 seconds | Skip experimentation; note in report; recommend manual testing |\n\n**When original query ran >30 seconds**, the report **MUST** include a section explicitly stating that GUC experimentation was skipped due to execution time exceeding the 30-second threshold, and **MUST** provide the manual testing SQL verbatim so the customer can run it themselves in psql (session scope — no `BEGIN`/`COMMIT` needed when run interactively):\n\n```sql\nSET enable_hashjoin = off;\nSET enable_nestloop = off;\nSET enable_mergejoin = on;\nEXPLAIN ANALYZE VERBOSE {original_sql};\n```\n\nDo not re-run the original query for redundant predicate testing either when execution exceeded 30s — recommend rewrites and explain expected impact from statistics.\n\n## Transaction Isolation\n\n**Each experiment MUST execute in a fresh transaction.** `SET LOCAL` confines the GUC to the surrounding transaction, so the settings MUST NOT carry into the next experiment. Submit each `BEGIN`/`COMMIT` block as a separate transaction (a separate psql session, or a separate driver-level transaction).\n\n## Handling experiment failures\n\nIf a transaction returns an error mid-batch (e.g., a `SET` is rejected, or the EXPLAIN fails), record the error under a \"GUC experiment failed\" finding in the report and **do not** compare partial results against the default baseline. The transaction auto-rolls back on any error, so session state is clean — but the missing plan means you cannot claim the planner chose suboptimally; surface the error verbatim instead.\n\n## Interpreting GUC Results\n\n### Plan Structure Changed\n\nWhen the disabled strategy is replaced by a different one:\n\n- Compare execution time between variants\n- Compare DPU estimates\n- Compare rows scanned and memory usage\n- If the alternative is faster: the planner's cost model chose suboptimally\n- If the alternative is slower: the planner's original choice was correct despite the estimation error\n\n### Disabled Strategy Still Used (Inflated Cost)\n\nWhen the planner uses the disabled strategy anyway, it adds ~10 billion to the node cost as a penalty. This indicates:\n\n- No viable alternative join strategy exists for that node\n- The bottleneck is the data access pattern (full scan, missing index), not the join choice\n- Focus recommendations on improving the scan/index layer rather than join strategy\n\n### Comparison Table Format\n\nPresent results as:\n\n| Metric | Default | Merge Join Only |\n| -------------------- | ---------- | --------------- |\n| Plan structure | [describe] | [describe] |\n| Execution time | Xms | Yms |\n| DPU (Total) | N | M |\n| Key node differences | [describe] | [describe] |\n| Strategy inflated? | N/A | Yes/No |\n\n## Redundant Predicate Testing\n\nA redundant predicate is a join or filter predicate that is semantically true given business rules but not logically derivable from the existing join chain alone.\n\n### When to Identify Redundant Predicates\n\nLook for this pattern:\n\n1. A table is accessed via a full scan or unselective scan\n2. No direct filter predicate matches a leading index column\n3. A business-rule relationship exists between columns across tables in the join chain\n4. Adding an explicit predicate would match an existing composite index's leading column\n\n### How Aurora DSQL Handles Predicate Inference\n\nAurora DSQL's optimizer performs transitive closure on equality predicates via EquivalenceClasses:\n\n- Given `A = B` and `B = C`, it infers `A = C`\n- Given `A = B` and `B = 42`, it propagates the constant: `A = 42`\n\nThe optimizer **cannot** infer business-rule relationships (e.g., \"all orders for a user belong to the same tenant as the user\"). These require explicit predicates.\n\n### Testing Procedure\n\n**When original query ran ≤30s:**\n\n1. Identify all redundant predicates\n2. Add all simultaneously to the SQL statement\n3. Execute `EXPLAIN ANALYZE VERBOSE` with all predicates via `psql` (or your driver's read path)\n4. Compare against original: execution time, plan structure, rows scanned, DPU estimate\n\n**When original query ran >30s:**\n\nSkip automatic testing. Recommend the rewrites and explain expected impact from index statistics.\n\n### Before/After Comparison Format\n\n```markdown\n### Redundant Predicate Test Results\n\n**Predicates added:**\n\n- `table.column = value` (derived from: business rule explanation)\n\n| Metric | Original | With Redundant Predicates |\n| ------------------- | ---------- | ------------------------- |\n| Execution time | Xms | Yms |\n| DPU (Total) | N | M |\n| Plan structure | [describe] | [describe] |\n| Rows scanned (node) | A | B |\n```\n\n## Handling Regressions\n\nWhen adding all redundant predicates simultaneously causes a regression (higher execution time or DPU):\n\n1. Analyze which predicate(s) caused the regression by comparing plan structure changes\n2. Identify the mechanism (e.g., planner changed a targeted Nested Loop to a broad Merge Join scan)\n3. Recommend applying only the beneficial predicates\n4. Explain why the regressing predicate caused a worse plan\n\nPresent as a separate finding in the diagnostic report with the tag \"Redundant Predicate Experiment\".\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7950,"content_sha256":"d294fe639e19ccf73e3abb5b51c4a9fee05241129099a9678d920635ddd89c56"},{"filename":"references/query-plan/plan-interpretation.md","content":"# Plan Interpretation Reference\n\n## Table of Contents\n\n1. [DSQL Node Types](#dsql-node-types)\n2. [Layered Plan Structure](#layered-plan-structure)\n3. [Calculating Node Duration](#calculating-node-duration)\n4. [Detecting Estimation Errors](#detecting-estimation-errors)\n5. [Nested Loop Amplification](#nested-loop-amplification)\n6. [Post-Scan Filter Selectivity](#post-scan-filter-selectivity)\n7. [Hash Table Resizing](#hash-table-resizing)\n8. [High-Loop Storage Lookups](#high-loop-storage-lookups)\n9. [Anomalous Values](#anomalous-values)\n10. [Projections and Row Width](#projections-and-row-width)\n\n---\n\n## DSQL Node Types\n\nDSQL stores all table data in B-Tree structures. Secondary indexes are also B-Tree, and contain the primary table keys for the secondary index values that make up the tree. DSQL extends standard PostgreSQL with storage-layer node types:\n\n### DSQL-Specific Nodes\n\n| Node Type | Description |\n| ----------------------- | ------------------------------------------------------------------------------- |\n| Full Scan (btree-table) | Full table scan |\n| Storage Scan | Physical read of >1 rows of data from storage layer via Pushdown Compute Engine |\n| B-Tree Scan | Physical read of rows from storage |\n| Storage Lookup | Point lookup of a row by internal row pointer (follows index scan) |\n| B-Tree Lookup | Point lookup of a table entry by key |\n\n### Standard PostgreSQL Nodes\n\n| Node Type | Description |\n| --------------- | ------------------------------------------------------ |\n| Nested Loop | Iterates inner side once per outer row |\n| Hash Join | Builds hash table from one side, probes with the other |\n| Merge Join | Merges two pre-sorted inputs |\n| Index Scan | Scans an index and fetches matching rows |\n| Index Only Scan | Retrieves all data from index access (no table access) |\n| Seq Scan | Sequential full table scan |\n| Sort | Sorts rows for Merge Join or ORDER BY |\n| Aggregate | Computes GROUP BY / aggregate functions |\n\n## Layered Plan Structure\n\nA logical scan decomposes into a Storage Scan, which itself has a B-Tree Scan child — not two siblings. Index Scan adds a **second, parallel** Storage Lookup branch (its own B-Tree Lookup child) for columns the index does not cover.\n\n**Full Scan (single branch):**\n\n```\nFull Scan (btree-table) on tablename\n Filter: col_a = 'v' ← query processor filter (post-transfer)\n -> Storage Scan on tablename\n Filters: col_b = 'v' ← storage filter (pre-transfer)\n -> B-Tree Scan on tablename\n```\n\n**Index Scan (two parallel branches; Storage Lookup is a sibling of Storage Scan, not a child):**\n\n```\nIndex Scan using idx on tablename\n Index Cond: col_a = 'v'\n -> Storage Scan on idx\n -> B-Tree Scan on tablename\n -> Storage Lookup on tablename ← separate branch for non-covered columns\n -> B-Tree Lookup on tablename\n```\n\nA child's timing and row counts roll up into its parent's totals — not into a sibling branch.\n\n## Calculating Node Duration\n\nDSQL follows the standard PostgreSQL EXPLAIN convention: `actual time` is reported **per iteration**, not cumulative. The node's total wall-clock time is:\n\n```\nNode Duration = actual_time_end × loops\n```\n\nWhere:\n\n- `actual_time_end` is the per-iteration time reported for the node (in ms)\n- `loops` is the number of times the node executed (always 1 at the top level; >1 for the inner side of a Nested Loop)\n\nRank all nodes by total duration descending. Begin analysis from the most expensive node.\n\n## Detecting Estimation Errors\n\nAn estimation error exists when estimated rows diverge significantly from actual rows:\n\n| Error Magnitude | Classification |\n| --------------- | --------------------------------------------------------- |\n| 2x–5x | Minor — note but low priority |\n| 5x–50x | Significant — investigate statistics |\n| 50x+ | Severe — likely correlated predicates or stale statistics |\n\nCalculate error ratio: `actual_rows / estimated_rows` (or inverse if estimate is higher).\n\nFor each significant error, record:\n\n- The node type and table\n- The estimated vs actual row count\n- The index or scan method used\n- Any filter predicates applied\n\n## Nested Loop Amplification\n\nFlag when a Nested Loop's outer input has a significant estimation error:\n\n**Pattern:**\n\n```\nNested Loop (est: N rows, actual: M rows)\n├── [Outer] Hash Join / Scan (est: X, actual: Y where Y >> X)\n└── [Inner] Index Scan (per-loop cost × Y loops)\n```\n\n**Explanation:** The planner chose Nested Loop expecting X iterations on the inner side. With Y actual iterations (where Y >> X), total inner-side cost = per-loop cost × Y. A Hash Join or Merge Join would have been more efficient at this cardinality.\n\n**Quantify:**\n\n- Expected total inner time: per-loop time × estimated outer rows\n- Actual total inner time: per-loop time × actual outer rows\n- Amplification factor: actual / estimated\n\n## Post-Scan Filter Selectivity\n\nCalculate filter waste when a node applies a post-scan filter:\n\n```\nFilter Selectivity = Rows Removed by Filter / (Rows Removed by Filter + Actual Rows)\n```\n\n| Selectivity | Interpretation |\n| ----------- | ------------------------------------------------ |\n| \u003c10% | Minimal waste — filter removes few rows |\n| 10%–50% | Moderate — consider composite index |\n| >50% | High waste — strong candidate for index pushdown |\n\nFor nodes inside loops, calculate total filter waste:\n\n```\nTotal rows scanned = (Actual Rows + Rows Removed) × loops\nTotal rows filtered = Rows Removed × loops\n```\n\n## Hash Table Resizing\n\nWhen a Hash Join reports `Buckets: originally N, now M` (where M > N):\n\n- The planner underestimated the build-side cardinality\n- The hash table was dynamically resized during execution\n- This adds memory pressure and execution overhead\n\nFlag the build-side estimation error and trace it to the source scan node.\n\n## High-Loop Storage Lookups\n\nWhen a Storage Lookup has a high loop count:\n\n```\nTotal I/O operations = actual_rows × loops\n```\n\nFlag when total I/O operations exceed 10,000. Each Storage Lookup involves a point read from the storage layer — high loop counts with even modest per-loop rows create significant cumulative I/O.\n\n## Anomalous Values\n\nDetect physically impossible row counts in DSQL plan nodes:\n\n**Detection criteria:**\n\n- A node reports `actual rows` exceeding the table's known total row count by 10x or more\n- Particularly common on Storage Lookup nodes under high loop counts\n\n**Example:** Storage Lookup reporting 7.7 trillion actual rows for a table with 379,484 rows.\n\n**Action:**\n\n- Flag as a potential DSQL reporting bug\n- Verify query results are correct (they typically are — only EXPLAIN output is affected)\n- Include in support request template\n\nThese anomalous values do not affect query correctness — only diagnostic output accuracy.\n\n## Projections and Row Width\n\nCapture Projections lists from Storage Scan and Storage Lookup nodes:\n\n```\nProjections: [col1, col2, col3, ...]\n```\n\nAssess row width overhead:\n\n- Count projected columns per node\n- Note when `SELECT *` pulls all columns from wide tables\n- Flag tables with 50+ columns or estimated row width >5,000 bytes\n\nWide projections increase I/O on Storage Lookups and memory usage in Hash Joins. Impact scales with result set size.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7970,"content_sha256":"ea6b88f8b46aa8b388c057ffb38ea9c7ba96f76d847b05783c53a0fd2b7a1d44"},{"filename":"references/query-plan/report-format.md","content":"# Diagnostic Report Format\n\nThe diagnostic report is produced as Markdown, rendered inline in the agent's response. **Produce a full report for every explainability request**, even ones that feel simple — the structure is the deliverable, not a formality.\n\n## Required Elements Checklist\n\nEvery report **MUST** contain all of these. Missing any one of them is a regression:\n\n- [ ] `# SQL Query Explainability — Diagnostic Report` as the H1\n- [ ] `Preview Only - not for distribution` on the line immediately below the H1\n- [ ] `## Query Information` table with Query Identifier, Planning Time, Execution Time, DPU Estimate\n- [ ] `## SQL Statement` section with the SQL in a fenced block\n- [ ] `## Plan Overview` section with the plan tree in a fenced block\n- [ ] `## Findings` section with numbered findings ordered by Node Duration (most expensive first)\n- [ ] Each finding uses `#### What we observed`, `#### Why it happened`, `#### Recommendation` as H4 subheadings, verbatim\n- [ ] Final `## Summary` table with columns `# | Finding | Severity | Recommendation | Expected Impact`\n- [ ] Closing `## Next Steps` block inviting the user to say \"reassess\" (or equivalent) after applying any recommendation, so the skill can measure the actual impact against the predicted Expected Impact\n\n### Conditional requirements\n\n- **Execution Time >30s:** the report **MUST** include a section stating GUC experimentation was skipped due to the 30-second threshold, AND the verbatim manual GUC testing SQL (see the skipped-query block under [GUC Comparison Table](#guc-comparison-table)). Do **not** re-run the query for redundant predicate testing either.\n- **Anomalous EXPLAIN values (e.g., trillion-row counts on small tables):** the report **MUST** explicitly confirm to the user that **query results are correct** despite the anomalous EXPLAIN output, flag the anomaly as a potential DSQL reporting bug, and include a [Support Request Template](#support-request-template) with Query ID, table statistics (reltuples, actual COUNT), and full plan output — no raw customer data values.\n\n## Table of Contents\n\n1. [Report Structure](#report-structure)\n2. [Finding Format](#finding-format)\n3. [Severity Levels](#severity-levels)\n4. [Summary Table](#summary-table)\n5. [GUC Comparison Table](#guc-comparison-table)\n6. [Support Request Template](#support-request-template)\n\n---\n\n## Report Structure\n\nProduce the report using this exact structure:\n\n```markdown\n# SQL Query Explainability — Diagnostic Report\n\nPreview Only - not for distribution\n\n## Query Information\n\n| Field | Value |\n| ---------------- | ---------------------------------------------------------------- |\n| Query Identifier | {query_id} |\n| Planning Time | {planning_time} ms |\n| Execution Time | {execution_time} ms |\n| DPU Estimate | Compute: {compute}, Read: {read}, Write: {write}, Total: {total} |\n\n## SQL Statement\n\n\\`\\`\\`sql\n{sql_statement}\n\\`\\`\\`\n\n## Plan Overview\n\n\\`\\`\\`\n{formatted_plan_tree}\n\\`\\`\\`\n\n## Findings\n\nEach finding is presented with three H4 subsections, verbatim: \"What we observed\" → \"Why it happened\" → \"Recommendation\".\nFindings are ordered by duration impact, starting from the most expensive.\n\n{findings}\n\n## Summary\n\n{summary_table}\n```\n\n## Finding Format\n\nEach finding follows this structure:\n\n```markdown\n### Finding N: {Title} ({Severity} — {duration_or_context})\n\n**Applies to:** {query_variant_tag}\n\n#### What we observed\n\n{Specific problem identified. Include a metrics table when quantitative evidence is available:}\n\n| Metric | Estimated | Actual | Error |\n| -------- | --------- | ------ | -------- |\n| {metric} | {est} | {act} | {ratio}x |\n\n#### Why it happened\n\n{Root cause analysis with evidence from the plan, optimizer statistics, and actual cardinalities.\nShow the optimizer's calculation when relevant (selectivity math, independence assumption).}\n\n#### Recommendation\n\n{Specific, actionable recommendation.}\n\n{When the recommendation involves SQL, include the exact statement:}\n\n\\`\\`\\`sql\n{recommended_sql}\n\\`\\`\\`\n\n**Expected impact:** {What improvement the customer should expect. Ground the prediction in the\nevidence you gathered — actual-vs-estimated row counts, Node Duration math, filter selectivity,\nDPU breakdown. When the evidence supports a concrete prediction, state it that way (e.g.,\n\"Storage Lookup drops from 50 rows per loop × 2000 loops to 1 per loop ≈ 50× less read DPU;\nexecution should go from ~4s to ~80ms\"). When the evidence is insufficient for a numeric\nprediction, **do not fabricate one** — name the missing evidence explicitly (e.g., \"Cannot\npredict magnitude without `most_common_freqs` on this column; expected qualitative direction\nis a reduction in Node Duration\"). Honesty about what you don't know is always preferable to\na plausible-sounding number with no data behind it.}\n```\n\n### Query Variant Tags\n\nTag each finding with which query variant it applies to:\n\n| Tag | Meaning |\n| ------------------------------ | ------------------------------------------- |\n| Original Query | Finding from the original SQL execution |\n| GUC Experiment | Finding from GUC-based plan experimentation |\n| Redundant Predicate Experiment | Finding from redundant predicate testing |\n\n### Linking Cascading Findings\n\nWhen one finding's root cause is another finding:\n\n```markdown\n#### Recommendation\n\nThis finding is a consequence of Finding N — resolving that finding addresses this one.\nNo separate action needed.\n```\n\n## Severity Levels\n\n| Severity | Criteria |\n| ---------- | ------------------------------------------------------------ |\n| CRITICAL | >50% of execution time; primary bottleneck |\n| HIGH | Root cause of a CRITICAL finding or 20–50% of execution time |\n| MODERATE | Measurable impact; worth fixing independently |\n| LOW | Minor overhead; fix if convenient |\n| BUG REPORT | Anomalous behavior indicating a potential DSQL bug |\n\n## Summary Table\n\nConclude the report with a summary table:\n\n```markdown\n## Summary\n\n| # | Finding | Severity | Recommendation | Expected Impact |\n| - | ------- | ---------- | ------------------------- | ----------------- |\n| 1 | {title} | {severity} | {one-line recommendation} | {one-line impact} |\n| 2 | {title} | {severity} | {one-line recommendation} | {one-line impact} |\n```\n\n## GUC Comparison Table\n\nWhen GUC experiments were performed, include a comparison:\n\n```markdown\n## GUC Experiment Results\n\n| Metric | Default | Merge Join Only |\n| ----------------------------- | ---------- | --------------- |\n| Plan structure | {describe} | {describe} |\n| Execution time | {X}ms | {Y}ms |\n| DPU (Total) | {N} | {M} |\n| Key differences | {describe} | {describe} |\n| Disabled strategy still used? | N/A | {Yes/No} |\n```\n\nWhen GUC experiments were skipped (query >30s):\n\n```markdown\n## GUC Experiment Results\n\nGUC experimentation skipped — original query execution time ({X}s) exceeds 30-second threshold.\nRecommend testing alternative strategies manually:\n\n\\`\\`\\`sql\nSET enable_hashjoin = off;\nSET enable_nestloop = off;\nSET enable_mergejoin = on;\nEXPLAIN ANALYZE VERBOSE {original_sql};\n\\`\\`\\`\n```\n\n## Support Request Template\n\nProduce when a potential DSQL bug is identified:\n\n```markdown\n## Support Request Template\n\n**Subject:** {one-line description of the anomaly}\n\n**Query Identifier:** {query_id}\n\n**Description:**\n{2-3 sentences explaining what was observed, why it is anomalous, and that the query\nresults are correct but diagnostic output appears affected.}\n\n**Table Statistics:**\n\n- {table}: reltuples={N}, relpages={M}, actual COUNT(*)={X}\n- Index used: {index_name} ({index_columns})\n- {additional context specific to the anomaly}\n\n**DPU Estimate:** Compute={N}, Read={M}, Write={W}, Total={T}\n\n**Full EXPLAIN ANALYZE VERBOSE output:**\n\\`\\`\\`\n{full_plan_output}\n\\`\\`\\`\n```\n\n**Rules for the support template:**\n\n- **MUST** include Query ID, full plan output, optimizer statistics, actual cardinalities, index definitions, DPU estimate\n- **MUST NOT** include actual customer data values from tables\n- Include only metadata, statistics, cardinalities, and plan output\n\n## Next Steps (closing block of every report)\n\nEnd the report with this block so the user knows to come back for a reassessment:\n\n```markdown\n## Next Steps\n\n1. Apply the recommendations in order — Finding 1 first, then re-measure before deciding whether the subsequent findings still matter.\n2. When any recommendation is in place, say **\"reassess\"** (or \"I added the index\" / \"re-run the analysis\"). I'll re-capture the plan, compare against the numbers above, and append an \"Addendum: After-Change Performance\" section to this report — so you can see the actual impact against the Expected Impact column.\n3. If the observed change diverges significantly from the Expected Impact, I'll investigate the gap as a new finding rather than closing it out.\n```\n\n## Addendum: After-Change Performance (Phase 5)\n\nWhen the user signals a reassessment, append a new H2 section to the **same** report — do not produce a separate report. The addendum has:\n\n```markdown\n## Addendum: After-Change Performance\n\n**Change applied:** {one-line description of what the user did, e.g., \"Added composite index (clientid, _transactionstartdatetime) on associate\"}\n\n**Re-captured plan:** Query Identifier {new_query_id}, Execution Time {new_ms} ms, DPU {new_total}\n\n| Metric | Before | After | Improvement |\n| ---------------------- | ------------- | ------------ | ---------------- |\n| Total Query Cost | {before_cost} | {after_cost} | {pct}% ↓ |\n| Scan Type (main node) | {before_scan} | {after_scan} | {status} |\n| Estimated Rows Scanned | {before_est} | {after_est} | {pct}% ↓ |\n| Execution Time | {before_ms} | {after_ms} | {pct}% ↓ |\n| DPU (Total) | {before_dpu} | {after_dpu} | {pct}% ↓ |\n| Result Set | {before_rows} | {after_rows} | Unchanged / Diff |\n\n**Match against Expected Impact:** {Yes — matches the N% latency reduction predicted in Finding 1 / No — only X% observed, investigating}.\n\n**Remaining findings status:** {Finding 2 still applies / Findings 2–3 now trivial given this change}.\n```\n\nIf the Result Set row count changed, flag that prominently — the change should be performance-neutral semantically, and any row-count drift means the recommendation altered query correctness (which should never happen for an index addition, and indicates something else is wrong).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":11118,"content_sha256":"a4dd65e1a582882aa4410335b043ffba91bacd78d6793c8eec5c638417d1be20"},{"filename":"references/scripts-guide.md","content":"# Aurora DSQL Scripts\n\nBash scripts for common Aurora DSQL cluster management and connection operations.\nThese scripts can be executed directly, used as agent tools, or configured as hooks.\n\n## Prerequisites\n\n- AWS CLI configured with credentials (`aws configure`)\n- `psql` client installed (for psql-connect.sh)\n- `jq` installed (for JSON parsing)\n- Appropriate IAM permissions:\n - `dsql:CreateCluster` (for create-cluster.sh)\n - `dsql:DeleteCluster` (for delete-cluster.sh)\n - `dsql:GetCluster` (for cluster-info.sh)\n - `dsql:ListClusters` (for list-clusters.sh)\n - `dsql:DbConnect` or `dsql:DbConnectAdmin` (for psql-connect.sh)\n\n## Using Scripts as Tools\n\nAgents can execute these scripts directly via shell tool calls. Each script supports `--help` for usage:\n\n```bash\n# List available clusters\n./scripts/list-clusters.sh --region us-east-1\n\n# Get cluster details\n./scripts/cluster-info.sh abc123def456\n\n# Connect and run a query\n./scripts/psql-connect.sh --cluster abc123def456 --command \"SELECT COUNT(*) FROM entities\"\n```\n\n## Available Scripts\n\n### create-cluster.sh\n\nCreate a new Aurora DSQL cluster.\n\n```bash\n./scripts/create-cluster.sh --created-by claude-opus-4-6\n./scripts/create-cluster.sh --created-by claude-opus-4-6 --region us-east-1\n./scripts/create-cluster.sh --created-by claude-opus-4-6 --region us-west-2 --tags Environment=dev,Project=myapp\n```\n\n**Output:** Cluster identifier, endpoint, and ARN. Exports environment variables for use with other scripts.\n\n---\n\n### delete-cluster.sh\n\nDelete an existing Aurora DSQL cluster.\n\n```bash\n./scripts/delete-cluster.sh abc123def456\n./scripts/delete-cluster.sh abc123def456 --region us-west-2\n./scripts/delete-cluster.sh abc123def456 --force\n```\n\n**Note:** Deletion is permanent and cannot be undone.\n\n---\n\n### psql-connect.sh\n\nConnect to Aurora DSQL using psql with automatic IAM authentication.\n\n```bash\n# Pass the cluster id as a positional arg, --cluster flag, or via $CLUSTER:\n./scripts/psql-connect.sh abc123def456 --region us-west-2\n./scripts/psql-connect.sh --cluster abc123def456 --region us-west-2\n\n# Single-statement command (one trailing semicolon allowed; no statement chaining):\n./scripts/psql-connect.sh --cluster abc123def456 --command \"SELECT * FROM entities LIMIT 5\"\n\n# Multi-statement file (BEGIN/COMMIT, multiple SET LOCAL, migrations, etc.):\n./scripts/psql-connect.sh --cluster abc123def456 --script ./migration.sql\n\n# DDL / role grants (IAM admin auth token):\n./scripts/psql-connect.sh --cluster abc123def456 --admin --command \"CREATE TABLE ...\"\n\n# Connection-tracking tag for the model that issued the queries:\n./scripts/psql-connect.sh --cluster abc123def456 --ai-model claude-opus-4-6\n```\n\n**Features:**\n\n- Automatically generates IAM auth token (valid for 15 minutes); use `--admin` for `dsql:DbConnectAdmin`\n- Supports interactive sessions, single-statement `--command`, and multi-statement `--script` files\n- Defaults to `sslmode=verify-full` against the OS trust store (`PGSSLROOTCERT=system`)\n- Uses `admin` user by default (override with `--user` or `$DB_USER`)\n- `--ai-model MODEL_ID` appends model identifier to PostgreSQL `application_name` for connection tracking\n- `--skip-cert-verify` downgrades to `sslmode=require` (encrypt only — vulnerable to MITM; do NOT use in production)\n\n---\n\n### list-clusters.sh\n\nList all Aurora DSQL clusters in a region.\n\n```bash\n./scripts/list-clusters.sh\n./scripts/list-clusters.sh --region us-west-2\n```\n\n---\n\n### cluster-info.sh\n\nGet detailed information about a specific cluster.\n\n```bash\n./scripts/cluster-info.sh abc123def456\n./scripts/cluster-info.sh abc123def456 --region us-west-2\n```\n\n**Output:** JSON with cluster identifier, endpoint, ARN, status, and creation time.\n\n---\n\n### Bulk data loading\n\nBulk loading is not bundled in this skill. For supported file formats, install instructions, and\nloader options, see the official AWS guide:\n[Loading data into Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/loading-data.html).\n\n---\n\n## Python Helpers\n\nTwo Python modules under `scripts/` back Workflow 4a (Rubric-Critical SQL construction):\n\n### safe_query.py\n\nBuilds DSQL SQL strings with validator-enforced interpolation — the canonical defense against\nSQL injection on raw-SQL paths (`psql -c`, shell pipelines, dynamic identifiers). See\n[input-validation.md](input-validation.md) for the full pattern.\n\nBuilt-in regex patterns: `TENANT_SLUG`, `UUID`, `INT`, `ISO_DATE`. Validators: `allow`, `regex`,\n`ident`, `keyword`, `integer`, `literal`. Raises `UnsafeSQLError` on raw-string interpolation.\n\n**Smoke test (recommended after edits):**\n\n```bash\npython3 scripts/safe_query.py # runs the embedded _selftest()\n# Expected: \"safe_query self-test passed\"\n```\n\n### tenant_query.py\n\nDemonstrates the canonical multi-tenant `SELECT` pattern using `safe_query.build()` + a\ndriver-supplied cursor. Illustrative example, not a runtime dependency — useful as a template\nwhen building tenant-scoped queries in application code.\n\n---\n\n## Environment Variables\n\nScripts respect these environment variables:\n\n- `CLUSTER` - Default cluster identifier\n- `REGION` - Default AWS region\n- `AWS_REGION` - Fallback AWS region if `REGION` not set\n- `DB_USER` - Default database user (defaults to 'admin')\n- `AWS_PROFILE` - AWS CLI profile to use\n\n## Quick Start Workflow\n\n```bash\n# 1. Create a cluster\n./scripts/create-cluster.sh --created-by claude-opus-4-6 --region us-east-1\n\n# Copy the export commands from output\nexport CLUSTER=abc123def456\nexport REGION=us-east-1\n\n# 2. Connect with psql\n./scripts/psql-connect.sh\n\n# 3. Inside psql, create a table\nCREATE TABLE entities (\n entity_id VARCHAR(255) PRIMARY KEY,\n tenant_id VARCHAR(255) NOT NULL,\n name VARCHAR(255) NOT NULL\n);\n\n# 4. Exit psql and run a query from command line\n./scripts/psql-connect.sh --command \"SELECT * FROM information_schema.tables WHERE table_schema='public'\"\n\n# 5. When done, delete the cluster\n./scripts/delete-cluster.sh $CLUSTER\n```\n\n## Notes\n\n- **Token Expiry:** IAM auth tokens expire after 15 minutes.\n- **Connection Limit:** DSQL supports up to 10,000 concurrent connections per cluster.\n- **Database Name:** Always use `postgres` (only database available in DSQL).\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6211,"content_sha256":"8823941325ab22e97038a8a38eb62221563e8ed3d71904059bde5d35dc47939a"},{"filename":"references/troubleshooting.md","content":"# Troubleshooting in DSQL\n\nThis file contains common additional errors encountered while working with DSQL and\nguidelines for how to solve them.\n\nBefore referring to any listed errors, refer to the complete [DSQL troubleshooting guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/troubleshooting.html#troubleshooting-connections)\n\n## Connection and Authorization\n\n### Token Expiration\n\n### Error: \"Token has expired\"\n\n**Cause:** Authentication token older than 15 minutes\n**Solutions:**\n\n- Auto-regenerate tokens per connection or query OR\n- Use connection pool hooks to refresh before expiration OR\n- Implement retry logic with token regeneration\n\n**Additional Recommendations:**\n\n- Refresh connections within 15 minutes\n- Auto-reconnect after observing auth errors\n\n### Connection Timeouts\n\n**Problem**: Database connections time out after 1 hour.\n**Solution**:\n\n- Configure connection pool lifetime \u003c 1 hour\n- Implement connection health checks\n- Handle disconnection gracefully with retries\n\n### Schema Privileges\n\n**Problem**: Non-admin users get permission denied errors.\n\n**Solution**:\n\n- Non-admin users need explicit `GRANT` statements from admin to access any schema (including `public`). See [access-control.md](access-control.md) for the canonical role + grant setup.\n- For sensitive data (PII, credentials, tokens), prefer a dedicated schema (e.g., `users_schema`) with scoped grants, separate from `public`. See [access-control.md](access-control.md#schema-separation-for-sensitive-data).\n- Use `ALTER DEFAULT PRIVILEGES IN SCHEMA \u003cschema> GRANT ... TO \u003crole>` so tables created later inherit the grants automatically.\n- Link database roles to IAM roles for authentication via `AWS IAM GRANT \u003crole> TO '\u003ciam-role-arn>'`.\n\n### SSL Certificate Verification\n\n**Problem**: SSL verification fails with certificate errors.\n\n**Solution**:\n\n- Use psql ≥14 (or a TLS library that supports SNI — required by DSQL's shared endpoint)\n- Set `sslmode=verify-full` and point `sslrootcert` at a CA bundle that includes Amazon Root CAs (`sslrootcert=system` works on most OSes; libpq otherwise looks at `~/.postgresql/root.crt`)\n- Use native TLS libraries (not OpenSSL 1.0.x)\n\n### `root certificate file \"/Users/\u003cyou>/.postgresql/root.crt\" does not exist`\n\n**Problem**: `psql` with `sslmode=verify-full` aborts because libpq is looking for a per-user CA bundle that doesn't exist. Common on a fresh macOS / Linux dev box that never had a personal CA bundle provisioned.\n\n**Solution**: tell libpq to use the OS trust store instead. Either pass `sslrootcert=system` in the connection string, or set `PGSSLROOTCERT=system` in the environment. The bundled `scripts/psql-connect.sh` does this by default; only override `PGSSLROOTCERT` if you have a corporate CA bundle to point at.\n\n```bash\nexport PGSSLROOTCERT=system # libpq ≥16 supports `system` directly\npsql \"host=$ENDPOINT sslmode=verify-full sslrootcert=system\" ...\n```\n\n**libpq \u003c16:** the wrapper's `PGSSLROOTCERT=system` default will fail with `invalid value for parameter \"sslrootcert\": \"system\"`. Install the Amazon Root CAs into `~/.postgresql/root.crt` and override the env-var before invoking the wrapper:\n\n```bash\nPGSSLROOTCERT=\"$HOME/.postgresql/root.crt\" ./scripts/psql-connect.sh --cluster \u003cid> --command \"...\"\n```\n\nSee the [accessing psql guide](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/accessing-psql.html) for the canonical CA-bundle setup.\n\n## Incompatibility\n\nWhen migrating from PostgreSQL, remember DSQL doesn't support:\n\n- **Foreign key constraints** - Enforce referential integrity in application code\n- **SERIAL types** - Use `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` with sequences instead\n- **Extensions** - No PL/pgSQL, PostGIS, pgvector, etc.\n- **Triggers** - Implement logic in application layer\n- **Temporary tables** - Use regular tables or application-level caching\n- **TRUNCATE** - Use `DELETE FROM table` instead\n- **Multiple databases** - Single `postgres` database per cluster\n- **Custom types** - Limited type system support\n- **Partitioning** - Manage data distribution in application\n\nSee [full list of unsupported features](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-unsupported-features.html).\n\n### Error: \"Foreign key constraint not supported\"\n\n**Cause:** Attempting to create FOREIGN KEY constraint\n**Solution:**\n\n1. Remove FOREIGN KEY from DDL\n2. Implement validation in application code\n3. Check parent exists before INSERT\n4. Check dependents before DELETE\n\n### Error: \"Datatype array not supported\"\n\n**Cause:** Using TEXT[] or other array types\n**Solution:**\n\n1. Change column to TEXT\n2. Store as comma-separated: `\"tag1,tag2,tag3\"`\n3. Or use JSON.stringify: `\"[\"tag1\",\"tag2\",\"tag3\"]\"`\n4. Deserialize in application layer\n\n### Error: \"Please use CREATE INDEX ASYNC\"\n\n**Cause:** Creating index without ASYNC keyword\n**Solution:**\n\n```sql\n-- Wrong\nCREATE INDEX idx_name ON table(column);\n\n-- Correct\nCREATE INDEX ASYNC idx_name ON table(column);\n```\n\n### Error: \"Transaction exceeds 3000 rows\"\n\n**Cause:** Modifying too many rows in single transaction\n**Solution:**\n\n1. Batch operations into chunks of 500-1000 rows\n2. Process each batch separately\n3. Add WHERE clause to limit scope\n\n### Error: \"OC001 - Concurrent DDL operation\"\n\n**Cause:** Multiple DDL operations on same resource\n**Solution:**\n\n1. Wait for current DDL to complete\n2. Retry with exponential backoff\n3. Execute DDL operations sequentially\n\n### Error: OCC / serialization failure (\"could not serialize access\" / \"concurrent update\")\n\n**Cause:** Two concurrent transactions wrote to overlapping rows. DSQL uses optimistic concurrency\ncontrol — the loser of the race is aborted at COMMIT time and MUST be retried.\n\n**Solution:**\n\n1. **Retry with backoff.** Wrap writes in a retry loop (exponential, jittered, capped at 3–5 attempts). Most OCC errors clear on the first retry once the conflicting transaction commits.\n2. **Check for hot keys.** If retries persist beyond a couple of attempts, the workload likely concentrates writes on a small set of keys. Diagnostics:\n - Run the query with `EXPLAIN ANALYZE` (Workflow 8) and inspect node-level row counts.\n - Cross-reference against the [scaling-guide.md \"Hot Keys\"](auth/scaling-guide.md) section.\n3. **Reduce write fan-in.** Common fixes: introduce per-shard counters instead of a global one, batch writes by tenant rather than mixing tenants in one transaction, partition heavy-write tables by a high-cardinality dimension.\n\nIf the workload genuinely requires strict serial writes on the same key, accept the OCC retry cost\nor move that subset to a different consistency primitive — DSQL's contract is optimistic.\n\n## Protocol Compatibility\n\n**Problem**: Some PostgreSQL clients send unsupported protocol messages.\n\n**Solution**:\n\n- Use officially tested drivers and connectors from the [Aurora DSQL connectivity tools page](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html)\n- Test client compatibility before production deployment\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7079,"content_sha256":"7b1ef17f1bd88f159c88d243dacdd677afebb3762269c60bb0055056ee22138b"},{"filename":"references/workflow-patterns.md","content":"# Common DSQL Workflow Patterns\n\nPart of the [Aurora DSQL Skill](../SKILL.md). The patterns below assume execution via a Postgres\ndriver (psycopg, pgx, etc.) using the language-specific [DSQL Connector](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html),\nor via [`scripts/psql-connect.sh`](../scripts/psql-connect.sh) for ad-hoc shells.\n\n---\n\n## Pattern 1: Explore Schema\n\n```sql\n-- List tables\nSELECT table_name FROM information_schema.tables WHERE table_schema = 'public';\n\n-- Inspect a specific table\nSELECT column_name, data_type, is_nullable\nFROM information_schema.columns\nWHERE table_name = 'entities'\nORDER BY ordinal_position;\n\n-- Sample data\nSELECT * FROM entities LIMIT 10;\n```\n\n## Pattern 2: Create Table with Index\n\n```python\n# WRONG - Combined DDL and index in single transaction\nwith conn.transaction():\n conn.execute(\"CREATE TABLE entities (...)\")\n conn.execute(\"CREATE INDEX ASYNC idx_tenant ON entities(tenant_id)\") # ❌ Will fail\n\n# CORRECT - Separate transactions (one DDL each)\nconn.execute(\"CREATE TABLE entities (...)\")\nconn.execute(\"CREATE INDEX ASYNC idx_tenant ON entities(tenant_id)\")\n```\n\n## Pattern 3: Safe Data Migration\n\n```python\nfrom safe_query import build, allow, regex, TENANT_SLUG\n\nSTATUSES = {\"active\", \"archived\", \"pending\"}\n\n# Step 1: Add column (its own transaction)\nconn.execute(\"ALTER TABLE entities ADD COLUMN status VARCHAR(50)\")\n\n# Step 2: Populate in batches — each in its own transaction, under 3,000 rows\npopulate = build(\n \"UPDATE entities SET status = {s} \"\n \"WHERE entity_id IN (\"\n \" SELECT entity_id FROM entities WHERE status IS NULL LIMIT 1000\"\n \")\",\n s=allow(\"active\", STATUSES),\n)\nconn.execute(populate)\nconn.execute(populate)\n\n# Step 3: Verify\nrows = conn.execute(\"SELECT COUNT(*) AS total, COUNT(status) AS with_status FROM entities\").fetchall()\n\n# Step 4: Create index in a separate transaction\nconn.execute(\"CREATE INDEX ASYNC idx_status ON entities(tenant_id, status)\")\n```\n\n## Pattern 4: Batch Inserts\n\n```python\nfrom safe_query import build, regex, literal, UUID, TENANT_SLUG\n\nwith conn.transaction(): # one transaction per chunk\n for row in rows[:2500]: # keep each transaction under 3,000 rows\n sql = build(\n \"INSERT INTO entities (entity_id, tenant_id, name) \"\n \"VALUES ({eid}, {tid}, {name})\",\n eid=regex(row[\"entity_id\"], UUID),\n tid=regex(row[\"tenant_id\"], TENANT_SLUG),\n name=literal(row[\"name\"]),\n )\n conn.execute(sql)\n```\n\n## Pattern 5: Application-Layer Foreign Key Check\n\n```python\nfrom safe_query import build, regex, literal, UUID, TENANT_SLUG\n\ncheck = build(\n \"SELECT entity_id FROM entities \"\n \"WHERE entity_id = {eid} AND tenant_id = {tid}\",\n eid=regex(parent_id, UUID),\n tid=regex(tenant_id, TENANT_SLUG),\n)\nif not conn.execute(check).fetchall():\n raise ValueError(\"Invalid parent reference\")\n\ninsert = build(\n \"INSERT INTO objectives (objective_id, entity_id, tenant_id, title) \"\n \"VALUES ({oid}, {eid}, {tid}, {title})\",\n oid=regex(new_objective_id, UUID),\n eid=regex(parent_id, UUID),\n tid=regex(tenant_id, TENANT_SLUG),\n title=literal(objective_title),\n)\nconn.execute(insert)\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":3251,"content_sha256":"1e7e68b79fa30e4d09a17da2fadb17005a918aaf848be961662a9a9a39e449e4"},{"filename":"scripts/cluster-info.sh","content":"#!/usr/bin/env bash\n# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n#\n# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\nset -euo pipefail\n\n# cluster-info.sh - Get detailed information about a DSQL cluster\n#\n# Usage: ./cluster-info.sh CLUSTER_IDENTIFIER [--region REGION]\n#\n# Examples:\n# ./cluster-info.sh abc123def456\n# ./cluster-info.sh abc123def456 --region us-west-2\n\nif [[ $# -lt 1 ]]; then\n echo \"Usage: $0 CLUSTER_IDENTIFIER [--region REGION]\"\n echo \"\"\n echo \"Get detailed information about an Aurora DSQL cluster.\"\n echo \"\"\n echo \"Arguments:\"\n echo \" CLUSTER_IDENTIFIER The cluster identifier\"\n echo \"\"\n echo \"Options:\"\n echo \" --region REGION AWS region (default: \\$AWS_REGION or us-east-1)\"\n exit 1\nfi\n\nCLUSTER_ID=\"$1\"\nshift\n\nREGION=\"${REGION:-${AWS_REGION:-us-east-1}}\"\n\n# Parse remaining arguments\nwhile [[ $# -gt 0 ]]; do\n case $1 in\n --region)\n REGION=\"$2\"\n shift 2\n ;;\n *)\n echo \"Unknown option: $1\"\n exit 1\n ;;\n esac\ndone\n\necho \"Fetching cluster information for: $CLUSTER_ID\"\necho \"\"\n\n# Get cluster details — capture output first so AWS CLI failures aren't hidden by the pipe\nCLUSTER_JSON=$(aws dsql get-cluster \\\n --identifier \"$CLUSTER_ID\" \\\n --region \"$REGION\" \\\n --output json)\n\necho \"$CLUSTER_JSON\" | jq '{\n identifier: .identifier,\n endpoint: .endpoint,\n arn: .arn,\n status: .status,\n creationTime: .creationTime,\n deletionProtectionEnabled: .deletionProtectionEnabled,\n tags: .tags\n }'\n\necho \"\"\necho \"To connect with psql:\"\necho \"export CLUSTER=$CLUSTER_ID\"\necho \"export REGION=$REGION\"\necho \"./scripts/psql-connect.sh\"\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":2143,"content_sha256":"bff6be18e56adc6795e498082246f728fcbe2978f9215db3f2108f335ec23e40"},{"filename":"scripts/create-cluster.sh","content":"#!/usr/bin/env bash\n# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n#\n# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\nset -euo pipefail\n\n# create-cluster.sh - Create an Aurora DSQL cluster\n#\n# Usage: ./create-cluster.sh --created-by MODEL_ID [--region REGION] [--tags KEY=VALUE,...]\n#\n# Examples:\n# ./create-cluster.sh --created-by claude-opus-4-6\n# ./create-cluster.sh --created-by claude-opus-4-6 --region us-east-1\n# ./create-cluster.sh --created-by claude-opus-4-6 --region us-west-2 --tags Environment=dev,Project=myapp\n\nREGION=\"${REGION:-${AWS_REGION:-us-east-1}}\"\nTAGS=\"\"\nCREATED_BY=\"\"\nDELETION_PROTECTION=true\n\n# Parse arguments\nwhile [[ $# -gt 0 ]]; do\n case $1 in\n --region)\n REGION=\"$2\"\n shift 2\n ;;\n --tags)\n TAGS=\"$2\"\n shift 2\n ;;\n --created-by)\n CREATED_BY=\"$2\"\n shift 2\n ;;\n --no-deletion-protection)\n DELETION_PROTECTION=false\n shift\n ;;\n -h|--help)\n echo \"Usage: $0 --created-by MODEL_ID [--region REGION] [--tags KEY=VALUE,...] [--no-deletion-protection]\"\n echo \"\"\n echo \"Creates an Aurora DSQL cluster in the specified region.\"\n echo \"Deletion protection is enabled by default.\"\n echo \"\"\n echo \"Options:\"\n echo \" --region REGION AWS region (default: \\$AWS_REGION or us-east-1)\"\n echo \" --tags TAGS Comma-separated tags (e.g., Env=dev,Project=app)\"\n echo \" --created-by ID Model/agent identifier added as a 'created_by' cluster tag\"\n echo \" --no-deletion-protection Disable deletion protection (default: enabled)\"\n echo \" -h, --help Show this help message\"\n exit 0\n ;;\n *)\n echo \"Unknown option: $1\"\n exit 1\n ;;\n esac\ndone\n\necho \"Creating Aurora DSQL cluster in $REGION...\"\n\n# Prepend created_by tag if --created-by was provided\nif [[ -n \"$CREATED_BY\" ]]; then\n # Validate: allow only alphanumeric, hyphens, underscores, and dots (e.g. claude-opus-4-6)\n if [[ ! \"$CREATED_BY\" =~ ^[a-zA-Z0-9._-]+$ ]]; then\n echo \"Error: --created-by must contain only alphanumeric characters, hyphens, underscores, and dots.\" >&2\n exit 1\n fi\n if [[ -n \"$TAGS\" ]]; then\n TAGS=\"created_by=${CREATED_BY},${TAGS}\"\n else\n TAGS=\"created_by=${CREATED_BY}\"\n fi\nfi\n\n# Build the AWS CLI command as an array to avoid eval and shell injection\nCMD=(aws dsql create-cluster --region \"$REGION\")\n\n# Enable deletion protection by default (use --no-deletion-protection to opt out)\nif [[ \"$DELETION_PROTECTION\" == \"true\" ]]; then\n CMD+=(--deletion-protection-enabled)\nelse\n CMD+=(--no-deletion-protection-enabled)\nfi\n\n# Add tags if provided\nif [[ -n \"$TAGS\" ]]; then\n # Convert comma-separated tags to JSON format using jq for safe escaping\n TAG_JSON=$(printf '%s\\n' \"$TAGS\" | tr ',' '\\n' | jq -Rn '\n [inputs | split(\"=\") | {(.[0]): .[1:] | join(\"=\")}] | add // {}\n ') || {\n echo \"Error: Failed to convert tags to JSON.\" >&2\n exit 1\n }\n if [[ -z \"$TAG_JSON\" || \"$TAG_JSON\" == \"{}\" ]]; then\n echo \"Error: Tags produced empty JSON. Check format: KEY=VALUE,...\" >&2\n exit 1\n fi\n CMD+=(--tags \"$TAG_JSON\")\nfi\n\n# Execute the command directly (no eval)\nTEMP_FILE=$(mktemp)\ntrap 'rm -f \"$TEMP_FILE\"' EXIT\n\"${CMD[@]}\" > \"$TEMP_FILE\"\n\n# Extract cluster identifier and endpoint\nCLUSTER_ID=$(jq -r '.identifier' \"$TEMP_FILE\")\nCLUSTER_ARN=$(jq -r '.arn' \"$TEMP_FILE\")\n\nif [[ -z \"$CLUSTER_ID\" || \"$CLUSTER_ID\" == \"null\" ]]; then\n echo \"Error: Failed to extract cluster identifier from response.\" >&2\n echo \"Response:\" >&2\n cat \"$TEMP_FILE\" >&2\n exit 1\nfi\n\nCLUSTER_ENDPOINT=\"${CLUSTER_ID}.dsql.${REGION}.on.aws\"\n\necho \"\"\necho \"✓ Cluster created successfully!\"\necho \"\"\necho \"Cluster Identifier: $CLUSTER_ID\"\necho \"Cluster Endpoint: $CLUSTER_ENDPOINT\"\necho \"Cluster ARN: $CLUSTER_ARN\"\necho \"Region: $REGION\"\necho \"\"\necho \"Export these environment variables for use with psql-connect.sh and other scripts:\"\necho \"\"\necho \"export CLUSTER=$CLUSTER_ID\"\necho \"export REGION=$REGION\"\necho \"\"\necho \"To connect with psql:\"\necho \"./scripts/psql-connect.sh\"\n\n# Clean up handled by trap\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":4663,"content_sha256":"0ac5228b7634891ec0a6fc8cb8fdd3f0e7ddcb1fd9fd70d5f567c00462fce1e2"},{"filename":"scripts/delete-cluster.sh","content":"#!/usr/bin/env bash\n# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n#\n# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\nset -euo pipefail\n\n# delete-cluster.sh - Delete an Aurora DSQL cluster\n#\n# Usage: ./delete-cluster.sh CLUSTER_IDENTIFIER [--region REGION] [--force]\n#\n# Examples:\n# ./delete-cluster.sh abc123def456\n# ./delete-cluster.sh abc123def456 --region us-west-2\n# ./delete-cluster.sh abc123def456 --force\n\nif [[ $# -lt 1 ]]; then\n echo \"Usage: $0 CLUSTER_IDENTIFIER [--region REGION] [--force]\"\n echo \"\"\n echo \"Deletes an Aurora DSQL cluster.\"\n echo \"\"\n echo \"Arguments:\"\n echo \" CLUSTER_IDENTIFIER The cluster identifier to delete\"\n echo \"\"\n echo \"Options:\"\n echo \" --region REGION AWS region (default: \\$AWS_REGION or us-east-1)\"\n echo \" --force Skip confirmation prompt\"\n exit 1\nfi\n\nCLUSTER_ID=\"$1\"\nshift\n\nREGION=\"${REGION:-${AWS_REGION:-us-east-1}}\"\nFORCE=false\n\n# Parse remaining arguments\nwhile [[ $# -gt 0 ]]; do\n case $1 in\n --region)\n REGION=\"$2\"\n shift 2\n ;;\n --force)\n FORCE=true\n shift\n ;;\n *)\n echo \"Unknown option: $1\"\n exit 1\n ;;\n esac\ndone\n\n# Confirmation prompt unless --force is used\nif [[ \"$FORCE\" != \"true\" ]]; then\n if [[ ! -t 0 ]]; then\n echo \"Error: No TTY available for confirmation. Use --force to skip.\" >&2\n exit 1\n fi\n echo \"⚠️ WARNING: This will permanently delete cluster: $CLUSTER_ID\"\n echo \"\"\n read -p \"Are you sure you want to continue? (type 'yes' to confirm): \" CONFIRM\n\n if [[ \"$CONFIRM\" != \"yes\" ]]; then\n echo \"Deletion cancelled.\"\n exit 0\n fi\nfi\n\necho \"Deleting Aurora DSQL cluster: $CLUSTER_ID in $REGION...\"\n\n# Delete the cluster\naws dsql delete-cluster \\\n --identifier \"$CLUSTER_ID\" \\\n --region \"$REGION\"\n\necho \"\"\necho \"✓ Cluster deletion initiated!\"\necho \"\"\necho \"Note: The cluster may take a few minutes to fully delete.\"\necho \"Check status with: aws dsql get-cluster --identifier $CLUSTER_ID --region $REGION\"\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":2501,"content_sha256":"0bf932f4447996be95b724150084851a4bb50ddbe203808f138dbf8a772907e7"},{"filename":"scripts/list-clusters.sh","content":"#!/usr/bin/env bash\n# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n#\n# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\nset -euo pipefail\n\n# list-clusters.sh - List all Aurora DSQL clusters\n#\n# Usage: ./list-clusters.sh [--region REGION]\n#\n# Examples:\n# ./list-clusters.sh\n# ./list-clusters.sh --region us-west-2\n\nREGION=\"${REGION:-${AWS_REGION:-us-east-1}}\"\n\n# Parse arguments\nwhile [[ $# -gt 0 ]]; do\n case $1 in\n --region)\n REGION=\"$2\"\n shift 2\n ;;\n -h|--help)\n echo \"Usage: $0 [--region REGION]\"\n echo \"\"\n echo \"List all Aurora DSQL clusters in the specified region.\"\n echo \"\"\n echo \"Options:\"\n echo \" --region REGION AWS region (default: \\$AWS_REGION or us-east-1)\"\n echo \" -h, --help Show this help message\"\n exit 0\n ;;\n *)\n echo \"Unknown option: $1\"\n exit 1\n ;;\n esac\ndone\n\necho \"Listing Aurora DSQL clusters in $REGION...\"\necho \"\"\n\n# List clusters\naws dsql list-clusters --region \"$REGION\" --output table\n\necho \"\"\necho \"To get details about a cluster:\"\necho \"./scripts/cluster-info.sh CLUSTER_IDENTIFIER\"\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":1631,"content_sha256":"2fde2a8467a40d719845c898ff7f8a66f001882ac87763f8b32cdea4d785eda4"},{"filename":"scripts/psql-connect.sh","content":"#!/usr/bin/env bash\n# Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n#\n# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\nset -euo pipefail\n\n# psql-connect.sh - Connect to Aurora DSQL using psql with IAM auth\n#\n# Usage: ./psql-connect.sh [CLUSTER_ID|--cluster CLUSTER_ID] [--region REGION] [--user USER] [--admin] [--ai-model MODEL_ID] [--command \"SQL\" | --script PATH]\n#\n# Examples:\n# ./psql-connect.sh --cluster abc123def456 --ai-model claude-opus-4-6\n# ./psql-connect.sh abc123def456 --ai-model claude-opus-4-6 --region us-west-2\n# ./psql-connect.sh --cluster abc123def456 --admin\n# ./psql-connect.sh --cluster abc123def456 --command \"SELECT * FROM entities LIMIT 5\"\n# ./psql-connect.sh --cluster abc123def456 --script ./migration.sql # multi-statement file\n\nCLUSTER_ID=\"${CLUSTER:-}\"\nREGION=\"${REGION:-${AWS_REGION:-us-east-1}}\"\n# Note: avoid using bare `USER` here — bash sets it automatically to the login\n# user, and overwriting it would clobber that for child processes.\nDB_USER_NAME=\"${DB_USER:-admin}\"\nADMIN=false\nCOMMAND=\"\"\nSCRIPT_FILE=\"\"\nAI_MODEL=\"\"\nSKIP_CERT_VERIFY=false\n\n# require_value FLAG NEXT — validate that a value-taking flag has a non-empty,\n# non-flag argument following it. Aborts with a clean error otherwise.\nrequire_value() {\n local flag=\"$1\"\n local next=\"${2:-}\"\n if [[ -z \"$next\" ]]; then\n echo \"Error: $flag requires a value.\" >&2\n exit 1\n fi\n if [[ \"$next\" == -* ]]; then\n echo \"Error: $flag requires a value, got '$next' (looks like another flag).\" >&2\n exit 1\n fi\n}\n\n# Track which source supplied CLUSTER_ID so positional+--cluster mismatch is\n# caught instead of silently letting the last writer win.\nCLUSTER_FROM_FLAG=\"\"\nCLUSTER_FROM_POSITIONAL=\"\"\n\n# set_cluster SOURCE VALUE — record the cluster ID from a specific source and\n# reject conflicting values from a different source.\nset_cluster() {\n local src=\"$1\"\n local val=\"$2\"\n case \"$src\" in\n flag)\n if [[ -n \"$CLUSTER_FROM_POSITIONAL\" && \"$CLUSTER_FROM_POSITIONAL\" != \"$val\" ]]; then\n echo \"Error: cluster id supplied by both --cluster ('$val') and positional ('$CLUSTER_FROM_POSITIONAL'); they disagree.\" >&2\n exit 1\n fi\n CLUSTER_FROM_FLAG=\"$val\"\n ;;\n positional)\n if [[ -n \"$CLUSTER_FROM_FLAG\" && \"$CLUSTER_FROM_FLAG\" != \"$val\" ]]; then\n echo \"Error: cluster id supplied by both positional ('$val') and --cluster ('$CLUSTER_FROM_FLAG'); they disagree.\" >&2\n exit 1\n fi\n CLUSTER_FROM_POSITIONAL=\"$val\"\n ;;\n esac\n CLUSTER_ID=\"$val\"\n}\n\n# Parse arguments\nwhile [[ $# -gt 0 ]]; do\n case $1 in\n --region)\n require_value \"$1\" \"${2:-}\"\n REGION=\"$2\"\n shift 2\n ;;\n --user)\n require_value \"$1\" \"${2:-}\"\n DB_USER_NAME=\"$2\"\n shift 2\n ;;\n --admin)\n ADMIN=true\n shift\n ;;\n --command|-c)\n require_value \"$1\" \"${2:-}\"\n COMMAND=\"$2\"\n shift 2\n ;;\n --script|-f)\n require_value \"$1\" \"${2:-}\"\n SCRIPT_FILE=\"$2\"\n shift 2\n ;;\n --cluster)\n require_value \"$1\" \"${2:-}\"\n set_cluster flag \"$2\"\n shift 2\n ;;\n --ai-model)\n require_value \"$1\" \"${2:-}\"\n AI_MODEL=\"$2\"\n shift 2\n ;;\n --skip-cert-verify)\n SKIP_CERT_VERIFY=true\n shift\n ;;\n --)\n # End-of-options sentinel — remaining args are positional.\n shift\n while [[ $# -gt 0 ]]; do\n set_cluster positional \"$1\"\n shift\n done\n break\n ;;\n -h|--help)\n echo \"Usage: $0 [CLUSTER_ID|--cluster CLUSTER_ID] [--region REGION] [--user USER] [--admin] [--command SQL | --script PATH]\"\n echo \"\"\n echo \"Connect to Aurora DSQL using psql with IAM authentication.\"\n echo \"\"\n echo \"Arguments:\"\n echo \" CLUSTER_ID Cluster identifier (positional, or via --cluster, or \\$CLUSTER env var)\"\n echo \"\"\n echo \"Options:\"\n echo \" --cluster ID Cluster identifier (alternative to positional argument)\"\n echo \" --region REGION AWS region (default: \\$REGION or \\$AWS_REGION or us-east-1)\"\n echo \" --user USER Database user (default: \\$DB_USER or 'admin')\"\n echo \" --admin Generate IAM admin auth token (uses generate-db-connect-admin-auth-token)\"\n echo \" --command SQL, -c Execute one SQL statement and exit (single-statement; chained semicolons rejected)\"\n echo \" --script PATH, -f Run a multi-statement SQL file via 'psql -f' (no semicolon guard)\"\n echo \" --ai-model ID AI model identifier appended to application_name (e.g. claude-opus-4-6)\"\n echo \" --skip-cert-verify Downgrade TLS to sslmode=require (encrypt only; vulnerable to MITM).\"\n echo \" Do NOT use in production.\"\n echo \" -h, --help Show this help message\"\n echo \"\"\n echo \"Environment Variables:\"\n echo \" CLUSTER Default cluster identifier\"\n echo \" REGION Default AWS region\"\n echo \" DB_USER Default database user\"\n exit 0\n ;;\n -*)\n echo \"Unknown option: $1\" >&2\n exit 1\n ;;\n *)\n set_cluster positional \"$1\"\n shift\n ;;\n esac\ndone\n\n# Validate cluster ID — trim surrounding whitespace and enforce DSQL's\n# alphanumeric format. Catches `--cluster \"\"`, `--cluster \" \"`, and accidental\n# slashes/dots in the ID before they reach the AWS CLI or psql.\nCLUSTER_ID=\"${CLUSTER_ID#\"${CLUSTER_ID%%[![:space:]]*}\"}\"\nCLUSTER_ID=\"${CLUSTER_ID%\"${CLUSTER_ID##*[![:space:]]}\"}\"\nif [[ -z \"$CLUSTER_ID\" ]]; then\n echo \"Error: CLUSTER_ID is required. Set \\$CLUSTER env var or pass as argument.\" >&2\n echo \"\" >&2\n echo \"Usage: $0 [CLUSTER_ID|--cluster CLUSTER_ID] [options]\" >&2\n echo \" or: export CLUSTER=abc123 && $0 [options]\" >&2\n exit 1\nfi\nif [[ ! \"$CLUSTER_ID\" =~ ^[a-z0-9]+$ ]]; then\n echo \"Error: CLUSTER_ID '$CLUSTER_ID' is invalid (DSQL cluster IDs are lowercase alphanumeric).\" >&2\n exit 1\nfi\n\n# Build endpoint\nENDPOINT=\"${CLUSTER_ID}.dsql.${REGION}.on.aws\"\n\n# Generate auth token. Capture stderr alongside stdout so an aws CLI failure\n# (expired creds, missing dsql:DbConnect, wrong region) surfaces a useful\n# message — under `set -e` the bare command-substitution would otherwise abort\n# the script before the empty-token guard below could fire.\necho \"Generating IAM auth token for $ENDPOINT...\" >&2\n\nif [[ \"$ADMIN\" == \"true\" ]]; then\n TOKEN_CMD=(aws dsql generate-db-connect-admin-auth-token --hostname \"$ENDPOINT\" --region \"$REGION\")\nelse\n TOKEN_CMD=(aws dsql generate-db-connect-auth-token --hostname \"$ENDPOINT\" --region \"$REGION\")\nfi\n\nif ! TOKEN=$(\"${TOKEN_CMD[@]}\" 2>&1); then\n echo \"Error: Failed to generate auth token (aws CLI exited non-zero).\" >&2\n echo \" Command: ${TOKEN_CMD[*]}\" >&2\n echo \" Output: $TOKEN\" >&2\n exit 1\nfi\n\n# Check if token generation was successful\nif [[ -z \"$TOKEN\" ]]; then\n echo \"Error: Failed to generate auth token (empty result). Check your AWS credentials.\" >&2\n exit 1\nfi\n\necho \"Connecting to $ENDPOINT as $DB_USER_NAME...\" >&2\necho \"\" >&2\n\n# DSQL requires TLS and rejects non-TLS connections. Default to verify-full\n# which validates the server certificate against DSQL's CA, preventing MITM\n# attacks. Point sslrootcert at the OS trust store so users don't need a\n# per-user ~/.postgresql/root.crt. Use --skip-cert-verify to downgrade to\n# require (encrypt only).\n# See https://docs.aws.amazon.com/aurora-dsql/latest/userguide/accessing-psql.html\nif [[ \"$SKIP_CERT_VERIFY\" == \"true\" ]]; then\n echo \"WARNING: Certificate verification disabled. Connection is vulnerable to MITM attacks. Do NOT use in production.\" >&2\n export PGSSLMODE=require\nelse\n export PGSSLMODE=verify-full\n # libpq defaults to ~/.postgresql/root.crt — fall back to the OS trust store\n # when the user has not provisioned a personal CA bundle. Honor any caller-\n # supplied PGSSLROOTCERT (e.g., a corporate bundle) by not overwriting it.\n : \"${PGSSLROOTCERT:=system}\"\n export PGSSLROOTCERT\nfi\n\n# Set application_name with AI model identifier if provided\nPGAPPNAME=\"dsql-skill\"\nif [[ -n \"$AI_MODEL\" ]]; then\n # Validate: allow only alphanumeric, hyphens, underscores, and dots\n if [[ ! \"$AI_MODEL\" =~ ^[a-zA-Z0-9._-]+$ ]]; then\n echo \"Error: --ai-model must contain only alphanumeric characters, hyphens, underscores, and dots.\" >&2\n exit 1\n fi\n PGAPPNAME=\"dsql-skill/${AI_MODEL}\"\nfi\nexport PGAPPNAME\n\n# Sanitize --command input: reject multi-statement chaining and comment injection.\n# psql -c runs a single command; allow at most ONE trailing semicolon.\n# This is a defense-in-depth measure — callers should also validate inputs.\n# Limitations: does not handle escaped quotes (\\' or ''), dollar-quoted strings\n# ($...$), or all edge cases. For complex queries, use --script PATH instead\n# to pipe a multi-statement file via stdin without the semicolon guard.\nif [[ -n \"$COMMAND\" && -n \"$SCRIPT_FILE\" ]]; then\n echo \"Error: --command and --script are mutually exclusive.\" >&2\n exit 1\nfi\n\nif [[ -n \"$COMMAND\" ]]; then\n # Reject whitespace-only --command early so the user gets a clear error\n # rather than psql's downstream syntax message.\n if [[ -z \"${COMMAND//[[:space:]]/}\" ]]; then\n echo \"Error: --command is whitespace-only.\" >&2\n exit 1\n fi\n # Reject newlines — sed processes the strip-quotes pipeline line by line, so a\n # newline-spanning literal would defeat the multi-statement detector. Use\n # --script for SQL that needs to span multiple lines.\n if [[ \"$COMMAND\" == *

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

\\n'* ]]; then\n echo \"Error: --command does not support newlines. Use --script PATH for multi-line SQL.\" >&2\n exit 1\n fi\n # Reject dollar-quoting which can interfere with single-quote stripping\n if echo \"$COMMAND\" | grep -qE '\\$\\$|\\$[a-zA-Z_][a-zA-Z0-9_]*\\

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

; then\n echo \"Error: Dollar-quoting is not supported in --command. Use --script PATH for SQL with dollar-quoted strings.\" >&2\n exit 1\n fi\n\n # Reject multi-statement chaining (semicolons outside string/identifier\n # literals, ignoring an optional trailing whitespace/semicolon at the end).\n # Strip in this order: (1) collapse SQL-standard doubled-quote escapes ('')\n # so the next pass treats them as empty literals; (2) strip single-quoted\n # string literals; (3) strip double-quoted identifiers; (4) trim a single\n # trailing semicolon. Any semicolon that survives is genuine statement\n # chaining.\n stripped=$(echo \"$COMMAND\" \\\n | sed \"s/''//g\" \\\n | sed \"s/'[^']*'//g\" \\\n | sed 's/\"[^\"]*\"//g' \\\n | sed -E 's/[[:space:]]*;[[:space:]]*$//')\n if echo \"$stripped\" | grep -q ';'; then\n echo \"Error: Multiple SQL statements are not allowed in --command. Use --script PATH for multi-statement input.\" >&2\n exit 1\n fi\n # Reject SQL comment sequences that could hide injected code\n if echo \"$stripped\" | grep -qE -- '--|/\\*'; then\n echo \"Error: SQL comments (-- or /*) are not allowed in --command. Use --script PATH if you need comments.\" >&2\n exit 1\n fi\n\n # Execute command and exit\n exec env PGPASSWORD=\"$TOKEN\" psql \\\n -h \"$ENDPOINT\" \\\n -U \"$DB_USER_NAME\" \\\n -d postgres \\\n -c \"$COMMAND\"\nelif [[ -n \"$SCRIPT_FILE\" ]]; then\n # Multi-statement file mode — no semicolon guard. Caller is responsible for\n # the contents of the file; build the SQL with safe_query.build() upstream\n # whenever values come from untrusted input.\n if [[ ! -f \"$SCRIPT_FILE\" ]]; then\n echo \"Error: --script path '$SCRIPT_FILE' is not a regular file.\" >&2\n exit 1\n fi\n if [[ ! -r \"$SCRIPT_FILE\" ]]; then\n echo \"Error: --script file '$SCRIPT_FILE' is not readable.\" >&2\n exit 1\n fi\n exec env PGPASSWORD=\"$TOKEN\" psql \\\n -P pager=off \\\n -v ON_ERROR_STOP=1 \\\n -h \"$ENDPOINT\" \\\n -U \"$DB_USER_NAME\" \\\n -d postgres \\\n -f \"$SCRIPT_FILE\"\nelse\n # Interactive session\n exec env PGPASSWORD=\"$TOKEN\" psql \\\n -h \"$ENDPOINT\" \\\n -U \"$DB_USER_NAME\" \\\n -d postgres\nfi\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":12425,"content_sha256":"9a25a8a8a1692773e3b40936555c97e84a8e7cdc7cf46dd0283b168c2a6f4bef"},{"filename":"scripts/safe_query.py","content":"\"\"\"Build SQL for Aurora DSQL when raw-SQL construction is unavoidable.\n\nUsed by ad-hoc shell pipelines (`psql -c \"...\"`) and any code path that does not\nuse the driver's native parameter binding. Every interpolated value MUST pass\nthrough a validator, and `build()` rejects raw strings by construction.\n\nUsage:\n from safe_query import build, allow, regex, ident, keyword, integer, literal\n from safe_query import TENANT_SLUG, UUID\n\n sql = build(\n \"SELECT * FROM {tbl} WHERE tenant_id = {tid} AND entity_id = {eid}\",\n tbl=ident(\"entities\"),\n tid=regex(user_tenant, TENANT_SLUG),\n eid=regex(user_eid, UUID),\n )\n # Pass `sql` to your driver: cur.execute(sql), conn.Query(ctx, sql), etc.\n # Or `psql -c \"$sql\"` after composing in a python3 subshell — see the\n # bash-deliverables block in references/input-validation.md.\n\n sql = build(\n \"INSERT INTO entities (entity_id, tenant_id, name) \"\n \"VALUES ({eid}, {tid}, {name})\",\n eid=regex(new_id, UUID),\n tid=regex(tenant, TENANT_SLUG),\n name=literal(user_supplied_name), # free text — dollar-quoted\n )\n\nDesign rules:\n - Raw strings passed to build() raise UnsafeSQLError. That is the point.\n - Format validation does NOT prove authorization; authorize separately.\n - When using a Postgres driver in application code, prefer the driver's\n native parameter binding. Reach for safe_query whenever you must build\n a raw SQL string (dynamic identifiers, shell-driven pipelines, etc.).\n\"\"\"\n\nimport re\nimport secrets\nimport string\nfrom typing import AbstractSet, Any, Callable, Dict, Pattern\n\nTENANT_SLUG: Pattern[str] = re.compile(r\"[a-z0-9-]{1,64}\")\nUUID: Pattern[str] = re.compile(\n r\"[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\",\n re.IGNORECASE,\n)\nINT: Pattern[str] = re.compile(r\"-?[0-9]{1,19}\")\nISO_DATE: Pattern[str] = re.compile(r\"\\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\\d|3[01])\")\n_IDENT: Pattern[str] = re.compile(r\"[a-z_][a-z0-9_]{0,62}\", re.IGNORECASE)\n\n\nclass UnsafeSQLError(ValueError):\n \"\"\"A value failed validation. Never catch and fall back — fix the caller.\"\"\"\n\n\nclass Safe:\n \"\"\"A value that has passed validation and is safe to interpolate.\n\n `build()` accepts only Safe instances. This is how the module prevents\n `build(\"... {x} ...\", x=user_input)` from ever working.\n \"\"\"\n\n __slots__ = (\"_sql\",)\n\n def __init__(self, sql: str) -> None:\n self._sql = sql\n\n def __str__(self) -> str:\n return self._sql\n\n\ndef allow(value: Any, allowed: AbstractSet[str], *, label: str = \"value\") -> Safe:\n \"\"\"Allowlist-validate and emit as a single-quoted string literal.\"\"\"\n if value not in allowed:\n raise UnsafeSQLError(f\"{label} not in allowlist: {value!r}\")\n # Allowlisted values originate from developer-controlled sets; the escape\n # is belt-and-braces in case someone puts a quote in the set.\n return Safe(\"'\" + str(value).replace(\"'\", \"''\") + \"'\")\n\n\ndef keyword(value: str, allowed: AbstractSet[str], *, label: str = \"keyword\") -> Safe:\n \"\"\"Allowlist-validate a SQL keyword and emit it unquoted.\n\n Use for ASC/DESC, AND/OR, or other places where a string literal would be\n syntactically wrong.\n \"\"\"\n if value not in allowed:\n raise UnsafeSQLError(f\"{label} not in allowlist: {value!r}\")\n return Safe(value)\n\n\ndef regex(value: Any, pattern: Pattern[str], *, label: str = \"value\") -> Safe:\n \"\"\"Regex-validate with re.fullmatch and emit as a single-quoted literal.\n\n Rejects values containing a single quote or backslash. `regex()` is for\n strict-format values (UUIDs, slugs, dates) that never legitimately need\n embedded quotes or backslashes; free text belongs in `literal()`, which\n dollar-quotes and sidesteps escaping entirely.\n \"\"\"\n if not isinstance(value, str) or not pattern.fullmatch(value):\n raise UnsafeSQLError(f\"{label} failed pattern {pattern.pattern!r}: {value!r}\")\n if \"'\" in value:\n raise UnsafeSQLError(\n f\"{label} contains a single quote; use literal() for free text: {value!r}\"\n )\n if \"\\\\\" in value:\n raise UnsafeSQLError(\n f\"{label} contains a backslash; use literal() for values \"\n f\"needing special characters: {value!r}\"\n )\n return Safe(\"'\" + value + \"'\")\n\n\ndef ident(name: str) -> Safe:\n \"\"\"Validate a SQL identifier (table or column) and emit it double-quoted.\"\"\"\n if not isinstance(name, str) or not _IDENT.fullmatch(name):\n raise UnsafeSQLError(f\"invalid identifier: {name!r}\")\n return Safe('\"' + name + '\"')\n\n\ndef integer(value: Any) -> Safe:\n \"\"\"Validate an integer. Accepts int or numeric string; rejects bool.\"\"\"\n if isinstance(value, bool):\n raise UnsafeSQLError(f\"expected int, got bool: {value!r}\")\n if isinstance(value, int):\n return Safe(str(value))\n if isinstance(value, str) and INT.fullmatch(value):\n return Safe(value)\n raise UnsafeSQLError(f\"invalid integer: {value!r}\")\n\n\ndef literal(value: str) -> Safe:\n \"\"\"Emit free text as a PostgreSQL dollar-quoted literal.\n\n Picks a random tag until it does not appear inside `value`, which sidesteps\n quote-escaping entirely. Use for descriptions, names, comments — values\n without a strict format.\n \"\"\"\n if not isinstance(value, str):\n raise UnsafeSQLError(f\"expected str, got {type(value).__name__}\")\n for _ in range(8):\n tag = \"dq_\" + secrets.token_hex(4)\n boundary = f\"${tag}$\"\n if boundary not in value:\n return Safe(f\"{boundary}{value}{boundary}\")\n # Eight 32-bit-random tag collisions implies adversarial input.\n raise UnsafeSQLError(\"could not generate a unique dollar-quote tag\")\n\n\ndef build(template: str, **parts: Safe) -> str:\n \"\"\"Substitute validated parts into a SQL template.\n\n Template uses `{name}` placeholders (str.format syntax). Every placeholder\n MUST map to a Safe value; raw strings raise UnsafeSQLError so the\n `build(\"... {t} ...\", t=user_input)` anti-pattern fails loudly.\n\n Also rejects template/kwargs mismatch: a missing key would otherwise raise\n `KeyError` (invisible to callers catching `UnsafeSQLError`), and an extra\n key would be silently ignored — dropping, for example, a tenant filter\n from the query.\n \"\"\"\n for key, value in parts.items():\n if not isinstance(value, Safe):\n raise UnsafeSQLError(\n f\"{key!r} must be a Safe value from allow/regex/ident/\"\n f\"keyword/integer/literal; got {type(value).__name__}\"\n )\n expected: set[str] = set()\n for _, fname, fspec, conv in string.Formatter().parse(template):\n if fname is None:\n continue\n if fname == \"\" or fname.isdigit():\n raise UnsafeSQLError(\n f\"template contains a positional placeholder {{{fname or ''}}}; \"\n f\"use named placeholders like {{name}}\"\n )\n if conv:\n raise UnsafeSQLError(\n f\"placeholder {{{fname}!{conv}}} uses a conversion flag; \"\n f\"Safe values must be interpolated without conversion\"\n )\n if fspec:\n raise UnsafeSQLError(\n f\"placeholder {{{fname}:{fspec}}} uses a format spec; \"\n f\"Safe values must be interpolated without formatting\"\n )\n expected.add(fname)\n provided = set(parts.keys())\n if expected != provided:\n missing = expected - provided\n extra = provided - expected\n raise UnsafeSQLError(\n f\"template/kwargs mismatch: missing {sorted(missing)}, \" f\"extra {sorted(extra)}\"\n )\n try:\n return template.format(**{k: str(v) for k, v in parts.items()})\n except (KeyError, IndexError) as exc:\n raise UnsafeSQLError(\n f\"template references a key not in kwargs \" f\"(possibly in a format spec): {exc}\"\n ) from exc\n\n\ndef _selftest() -> None:\n \"\"\"Smoke-test every validator and build().\"\"\"\n\n def _check(condition: bool, msg: str) -> None:\n if not condition:\n raise RuntimeError(msg)\n\n def _expect_unsafe(fn: str, *args: Any, **kwargs: Any) -> None:\n \"\"\"Call a validator/build by name and verify it raises UnsafeSQLError.\"\"\"\n registry: Dict[str, Callable[..., Any]] = {\n \"allow\": allow,\n \"keyword\": keyword,\n \"regex\": regex,\n \"ident\": ident,\n \"integer\": integer,\n \"literal\": literal,\n \"build\": build,\n }\n target = registry[fn]\n try:\n target(*args, **kwargs)\n raise RuntimeError(f\"expected UnsafeSQLError from {fn}\")\n except UnsafeSQLError:\n pass\n\n # Happy paths\n _check(str(allow(\"tenant-1\", {\"tenant-1\"})) == \"'tenant-1'\", \"allow\")\n _check(str(keyword(\"ASC\", {\"ASC\", \"DESC\"})) == \"ASC\", \"keyword\")\n _check(str(regex(\"a-1\", TENANT_SLUG)) == \"'a-1'\", \"regex\")\n _check(str(ident(\"entities\")) == '\"entities\"', \"ident\")\n _check(str(integer(42)) == \"42\", \"integer\")\n _check(str(integer(\"-7\")) == \"-7\", \"integer neg\")\n lit = str(literal(\"o'reilly\"))\n _check(lit.startswith(\"$dq_\") and \"o'reilly\" in lit, \"literal\")\n\n sql = build(\n \"SELECT * FROM {t} WHERE tenant_id = {tid}\",\n t=ident(\"entities\"),\n tid=regex(\"acme\", TENANT_SLUG),\n )\n _check(sql == \"SELECT * FROM \\\"entities\\\" WHERE tenant_id = 'acme'\", \"build\")\n _check(str(regex(\"abc\", TENANT_SLUG, label=\"tenant\")) == \"'abc'\", \"regex label\")\n\n # Rejections\n _permissive = re.compile(r\".+\")\n _expect_unsafe(\"allow\", \"evil\", {\"tenant-1\"})\n _expect_unsafe(\"keyword\", \"DROP\", {\"ASC\", \"DESC\"})\n _expect_unsafe(\"regex\", \"'; DROP TABLE t; --\", TENANT_SLUG)\n _expect_unsafe(\"ident\", 'x\" OR 1=1 --')\n _expect_unsafe(\"integer\", \"1; DROP\")\n _expect_unsafe(\"integer\", True)\n _expect_unsafe(\"literal\", 123)\n _expect_unsafe(\"build\", \"SELECT {x}\", x=\"raw string\")\n _expect_unsafe(\"regex\", \"x' OR 1=1 --\", _permissive)\n _expect_unsafe(\"regex\", \"it's\", _permissive)\n _expect_unsafe(\"regex\", \"'\", _permissive)\n _expect_unsafe(\"regex\", \"abc\\\\\", _permissive)\n _expect_unsafe(\"build\", \"SELECT {x}\", x=ident(\"col\"), y=ident(\"extra\"))\n _expect_unsafe(\"build\", \"SELECT {x} FROM {y}\", x=ident(\"col\"))\n _expect_unsafe(\"build\", \"SELECT {x!r}\", x=ident(\"col\"))\n _expect_unsafe(\"build\", \"SELECT {x:>30}\", x=ident(\"col\"))\n _expect_unsafe(\"build\", \"SELECT {}\", x=ident(\"col\"))\n _expect_unsafe(\"build\", \"SELECT {0}\", x=ident(\"col\"))\n\n print(\"safe_query self-test passed\")\n\n\nif __name__ == \"__main__\":\n _selftest()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":10687,"content_sha256":"cc51bbcd72d4e67e95c775140c365dd7527e68fe36898155450723b7424d5658"},{"filename":"scripts/tenant_query.py","content":"\"\"\"Fetch all rows from `entities` for a given tenant.\n\nreq.tenant is untrusted input — it MUST be validated before interpolation.\nThe execution path here builds a raw SQL string (for cases where a driver's\nnative parameter binding cannot be used — e.g., dynamic identifiers, shell\npipelines). safe_query.build() is the injection defense.\n\nAuthorization note: format validation (regex) confirms the value looks like a\nvalid tenant slug. It does NOT prove the caller is authorized to read that\ntenant's data. Authorize the caller against req.tenant before calling this\nfunction.\n\"\"\"\n\nfrom safe_query import build, regex, ident, TENANT_SLUG\n\n\ndef select_by_tenant(cursor, req) -> list[tuple]:\n \"\"\"Return all rows from `entities` where tenant_id matches req.tenant.\n\n Uses cursor-based execution so the same shape works across psycopg2,\n psycopg3, and pgx-style cursors (psycopg2's `cursor.execute` returns None,\n so the caller must use `cursor.fetchall()` separately rather than chain).\n\n Args:\n cursor: A driver cursor that exposes `.execute(sql)` and `.fetchall()`.\n For psycopg3 you can also pass `connection.cursor()` or use\n `connection.execute(sql).fetchall()` directly inline.\n req: An object with a `.tenant` attribute (untrusted string).\n\n Returns:\n A list of row tuples (or row dicts, depending on the cursor's\n configured row factory).\n\n Raises:\n UnsafeSQLError: If req.tenant fails TENANT_SLUG validation.\n ValueError: If req.tenant is missing or not a string.\n \"\"\"\n tenant = getattr(req, \"tenant\", None)\n if not isinstance(tenant, str):\n raise ValueError(f\"req.tenant must be a str, got {type(tenant).__name__}\")\n\n sql = build(\n \"SELECT * FROM {t} WHERE tenant_id = {tid}\",\n t=ident(\"entities\"),\n tid=regex(tenant, TENANT_SLUG, label=\"req.tenant\"),\n )\n\n cursor.execute(sql)\n return cursor.fetchall()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":1948,"content_sha256":"0ed36e5fad63bbe9dda043897c6c69ddcddcc192a1b96aec4885e464cc25ffcf"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Amazon Aurora DSQL","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"Aurora DSQL is a serverless, PostgreSQL-compatible distributed SQL database. This skill provides direct database interaction via ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" scripts and PostgreSQL drivers, schema management, migration support, multi-tenant patterns, and query-plan explainability.","type":"text"}]},{"type":"paragraph","content":[{"text":"Key capabilities:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Direct query execution via ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" with generated IAM auth tokens (see ","type":"text"},{"text":"scripts/psql-connect.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/psql-connect.sh","title":null}},{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema management with DSQL constraints (one DDL per transaction, async indexes)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Safe data migration (column-level, constraint-level, MySQL→DSQL)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Multi-tenant isolation via ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" + parameterized SQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"IAM-based authentication with a 15-minute token expiry","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query-plan diagnosis for slow queries (EXPLAIN ANALYZE + GUC experiments)","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"The recommended runtime is ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"aws dsql generate-db-connect-auth-token","type":"text","marks":[{"type":"code_inline"}]},{"text":" for IAM-authenticated sessions. Application code SHOULD use the language-specific ","type":"text"},{"text":"DSQL Connectors and SDKs","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html","title":null}}]},{"text":". For AWS knowledge lookups (service docs, AWS API calls), the ","type":"text"},{"text":"AWS MCP Server","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html","title":null}}]},{"text":" is the preferred MCP integration.","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Files","type":"text"}]},{"type":"paragraph","content":[{"text":"Load these files as needed for detailed guidance:","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"development-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/development-guide.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" ALWAYS load before implementing schema changes or database operations ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"Best Practices","type":"text","marks":[{"type":"link","attrs":{"href":"references/development-guide.md","title":null}}]},{"text":", DDL rules, connection patterns, transaction limits, data type serialization patterns, application-layer referential integrity instructions, security best practices","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Execution:","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"database-tools.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/database-tools.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when you need detailed syntax and examples for ad-hoc query execution against DSQL. PREFER ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" (via ","type":"text"},{"text":"scripts/psql-connect.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/psql-connect.sh","title":null}},{"type":"code_inline"}]},{"text":") for ad-hoc queries — execute directly rather than writing one-off scripts. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":"-based read-only and write patterns, transaction semantics, ","type":"text"},{"text":"input validation","type":"text","marks":[{"type":"link","attrs":{"href":"references/input-validation.md","title":null}}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MCP (AWS knowledge / API):","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"mcp-setup.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mcp-setup.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when configuring or recommending the AWS MCP Server for AWS knowledge lookups, AWS API access, or per-assistant install. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" When to use ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" vs the AWS MCP Server, pointer to the canonical AWS setup docs, credential reminders.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"mcp-tools.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mcp-tools.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when invoking AWS MCP Server tools to verify DSQL service limits, fetch docs, or drive AWS API calls. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Tool surface — knowledge (","type":"text"},{"text":"aws___search_documentation","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___read_documentation","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___recommend","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___retrieve_skill","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___list_regions","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___get_regional_availability","type":"text","marks":[{"type":"code_inline"}]},{"text":") and API (","type":"text"},{"text":"aws___call_aws","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___run_script","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___get_tasks","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"aws___get_presigned_url","type":"text","marks":[{"type":"code_inline"}]},{"text":"); pointers to documentation-tools.md.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"documentation-tools.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/documentation-tools.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when looking up DSQL service limits, fetching a specific AWS docs page, or polling long-running AWS API calls launched via the AWS MCP Server. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Detailed parameters and example calls for the AWS knowledge tools.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"platforms/","type":"text","marks":[{"type":"link","attrs":{"href":"references/platforms/","title":null}}]},{"text":" — per-assistant install notes","type":"text"}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when installing the AWS MCP Server inside a specific coding assistant. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Per-assistant entry-point details — ","type":"text"},{"text":"claude-code.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/platforms/claude-code.md","title":null}}]},{"text":", ","type":"text"},{"text":"codex.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/platforms/codex.md","title":null}}]},{"text":", ","type":"text"},{"text":"gemini.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/platforms/gemini.md","title":null}}]},{"text":", ","type":"text"},{"text":"kiro.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/platforms/kiro.md","title":null}}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"language.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/language.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" load before writing DSQL connection code. Mirror the linked ","type":"text"},{"text":"example_preferred.\u003cext>","type":"text","marks":[{"type":"code_inline"}]},{"text":" for the chosen driver — memory-authored connections drift from the canonical IAM-token-refresh pattern. Canonical entry-point examples (load ","type":"text"},{"text":"language.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for the full driver list + pool/TLS/token-refresh details):","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Python: ","type":"text"},{"text":"import aurora_dsql_psycopg as dsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" → ","type":"text"},{"text":"dsql.connect(host, region, user)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JS (node-postgres): ","type":"text"},{"text":"import { AuroraDSQLPool } from \"@aws/aurora-dsql-node-postgres-connector\"","type":"text","marks":[{"type":"code_inline"}]},{"text":" → ","type":"text"},{"text":"new AuroraDSQLPool({ host, user })","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JS (postgres.js): ","type":"text"},{"text":"import { auroraDSQLPostgres } from \"@aws/aurora-dsql-postgresjs-connector\"","type":"text","marks":[{"type":"code_inline"}]},{"text":" → ","type":"text"},{"text":"auroraDSQLPostgres({ host, user })","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Go (pgx): ","type":"text"},{"text":"import \"github.com/awslabs/aurora-dsql-connectors/go/pgx/dsql\"","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Java (JDBC): ","type":"text"},{"text":"software.amazon.dsql:aurora-dsql-jdbc-connector:1.4.0","type":"text","marks":[{"type":"code_inline"}]},{"text":" → ","type":"text"},{"text":"jdbc:aws-dsql:postgresql://...","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"paragraph","content":[{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Canonical DSQL connector packages per language, driver selection, framework patterns, IAM auth token rotation and TLS configuration, and connection code examples for Python / JavaScript / TypeScript / Go / Java / Rust.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"troubleshooting.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/troubleshooting.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when debugging errors or unexpected behavior. SHOULD always consult for OCC errors, connection failures, or unexpected query results. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Common pitfalls, error messages, solutions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"onboarding.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/onboarding.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" User explicitly requests to \"Get started with DSQL\" or similar phrase ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Interactive step-by-step guide for new users","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"access-control.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/access-control.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" MUST load when creating database roles, granting permissions, setting up schemas for applications, or handling sensitive data. ALWAYS use scoped roles for applications — create database roles with ","type":"text"},{"text":"dsql:DbConnect","type":"text","marks":[{"type":"code_inline"}]},{"text":". ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Scoped role setup, IAM-to-database role mapping, schema separation for sensitive data, role design patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Authentication & Operations:","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"auth/authentication-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/authentication-guide.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" MUST load when handling IAM auth tokens, secrets, SSL/TLS, connection pooling, or audit logging. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Token lifecycle, secret storage patterns, SSL/TLS settings, connection-pool guidance, audit-log integration.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"auth/connectivity-tools.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/connectivity-tools.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when picking a driver/ORM/adapter or planning bulk-data loading. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Pointer to the canonical AWS DSQL connectivity tools page (drivers, ORMs, adapters) and the bulk-loading docs page.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"auth/scaling-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/scaling-guide.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when designing for scale — connection pooling, batch optimization, hot-key avoidance, identifier choice. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Horizontal scaling strategy, pool sizing, batch-size guidance, IDENTITY/UUID trade-offs, sequence cache rules.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Implementation Examples:","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"workflow-patterns.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/workflow-patterns.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when looking for a worked example of a common multi-step DSQL workflow (schema explore, CREATE+INDEX, safe migration, batch insert, application-layer FK check). ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Five canonical patterns with ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" / driver code.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"dsql-examples.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/dsql-examples.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when looking for specific implementation examples. ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Index of ","type":"text"},{"text":"examples/*.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" (connection, schema, data-operations, migrations, patterns).","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DDL Migrations (modular):","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"ddl-migrations/overview.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/overview.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" MUST load when performing DROP COLUMN, RENAME COLUMN, ALTER COLUMN TYPE, or DROP CONSTRAINT ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Table recreation pattern overview, transaction rules, common verify & swap pattern","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"ddl-migrations/column-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/column-operations.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load for DROP COLUMN, ALTER COLUMN TYPE, SET/DROP NOT NULL, SET/DROP DEFAULT migrations ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Step-by-step migration patterns for column-level changes","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"ddl-migrations/constraint-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/constraint-operations.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load for ADD/DROP CONSTRAINT, MODIFY PRIMARY KEY, column split/merge migrations ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" Step-by-step migration patterns for constraint and structural changes","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"ddl-migrations/batched-migration.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/batched-migration.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when migrating tables exceeding 3,000 rows ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" OFFSET-based and cursor-based batching patterns, progress tracking, error handling","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MySQL Migrations (modular):","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"mysql-migrations/type-mapping.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/type-mapping.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" MUST load when migrating MySQL schemas to DSQL ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" MySQL data type mappings, feature alternatives, DDL operation mapping","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"mysql-migrations/ddl-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/ddl-operations.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when translating MySQL DDL operations to DSQL equivalents ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" ALTER COLUMN, DROP COLUMN, AUTO_INCREMENT, ENUM, SET, FOREIGN KEY migration patterns","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"mysql-migrations/full-example.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/full-example.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" Load when migrating a complete MySQL table to DSQL ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" End-to-end MySQL CREATE TABLE migration example with decision summary","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Plan Explainability (modular):","type":"text"}]},{"type":"paragraph","content":[{"text":"When:","type":"text","marks":[{"type":"strong"}]},{"text":" MUST load all four at Workflow 8 Phase 0 — ","type":"text"},{"text":"query-plan/plan-interpretation.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/plan-interpretation.md","title":null}}]},{"text":", ","type":"text"},{"text":"query-plan/catalog-queries.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/catalog-queries.md","title":null}}]},{"text":", ","type":"text"},{"text":"query-plan/guc-experiments.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/guc-experiments.md","title":null}}]},{"text":", ","type":"text"},{"text":"query-plan/report-format.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/report-format.md","title":null}}]},{"text":" ","type":"text"},{"text":"Contains:","type":"text","marks":[{"type":"strong"}]},{"text":" DSQL node types + Node Duration math + estimation-error bands, pg_class/pg_stats/pg_indexes SQL + correlated-predicate verification, GUC experiment procedures + 30-second skip protocol, required report structure + element checklist + support request template","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Query Execution","type":"text"}]},{"type":"paragraph","content":[{"text":"Run ad-hoc DSQL queries with ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" and a freshly-generated IAM auth token. The bundled ","type":"text"},{"text":"scripts/psql-connect.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/psql-connect.sh","title":null}},{"type":"code_inline"}]},{"text":" wraps token generation, TLS configuration, and single-statement guards — PREFER it over hand-rolled ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" invocations.","type":"text"}]},{"type":"paragraph","content":[{"text":"Read-only:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/psql-connect.sh --cluster \u003ccluster-id> --command \"SELECT * FROM entities LIMIT 10\"","type":"text"}]},{"type":"paragraph","content":[{"text":"Write/DDL (IAM admin auth token required):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/psql-connect.sh --cluster \u003ccluster-id> --admin --command \"CREATE INDEX ASYNC ...\"","type":"text"}]},{"type":"paragraph","content":[{"text":"Schema discovery:","type":"text","marks":[{"type":"strong"}]},{"text":" there is no special ","type":"text"},{"text":"list_tables","type":"text","marks":[{"type":"code_inline"}]},{"text":" helper — use information_schema:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';","type":"text"}]},{"type":"paragraph","content":[{"text":"See ","type":"text"},{"text":"database-tools.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/database-tools.md","title":null}}]},{"text":" for detailed usage and examples.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"AWS Knowledge via the AWS MCP Server (optional)","type":"text"}]},{"type":"paragraph","content":[{"text":"When connected to the ","type":"text"},{"text":"AWS MCP Server","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aws-mcp/latest/userguide/mcp-server.html","title":null}}]},{"text":", its ","type":"text"},{"text":"aws___search_documentation","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"aws___read_documentation","type":"text","marks":[{"type":"code_inline"}]},{"text":" tools can verify DSQL service limits before advising users. The numeric limits below are defaults that may change — when a user's decision depends on an exact limit, verify it first:","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Limit","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Default","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Verify query","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max rows mutated per transaction","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"3,000","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql transaction limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max data modified per write transaction","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"10 MiB","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql transaction limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max transaction duration","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"5 minutes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql transaction limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max connections per cluster","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"10,000","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql connection limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"IAM auth token expiry","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"15 minutes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql authentication token","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max connection duration","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"60 minutes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql connection limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max indexes per table","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"24","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql index limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Max columns per index","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"8","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql index limits","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"IDENTITY/SEQUENCE CACHE values","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1 or >= 65536","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"aurora dsql sequence cache","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"When to verify:","type":"text","marks":[{"type":"strong"}]},{"text":" Before recommending batch sizes, connection pool settings, or schema designs where hitting a limit would cause failures. No need to verify for general guidance or when the exact number doesn't affect the user's decision.","type":"text"}]},{"type":"paragraph","content":[{"text":"Fallback:","type":"text","marks":[{"type":"strong"}]},{"text":" If the AWS MCP Server is unavailable, use the defaults above and note to the user that limits should be verified against ","type":"text"},{"text":"DSQL documentation","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/","title":null}}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"CLI Scripts Available","type":"text"}]},{"type":"paragraph","content":[{"text":"Bash scripts in ","type":"text"},{"text":"scripts/","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/","title":null}}]},{"text":" for cluster management (create, delete, list, cluster info) and ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" connection. See ","type":"text"},{"text":"references/scripts-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/scripts-guide.md","title":null}}]},{"text":" for usage. For bulk data loading, see ","type":"text"},{"text":"Loading data into Aurora DSQL","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/loading-data.html","title":null}}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"ALWAYS","type":"text","marks":[{"type":"strong"}]},{"text":" prefer ","type":"text"},{"text":"scripts/create-cluster.sh","type":"text","marks":[{"type":"code_inline"}]},{"text":". The script issues a ","type":"text"},{"text":"single atomic","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"CreateCluster","type":"text","marks":[{"type":"code_inline"}]},{"text":" call with tags embedded — matching the AWS DSQL API shape with interpretable output.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Task","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Script","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Create cluster with tags","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"scripts/create-cluster.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/create-cluster.sh","title":null}},{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./scripts/create-cluster.sh --created-by \u003cmodel-id> --tags Environment=eval,Project=dsql-skill-eval","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"List clusters","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"scripts/list-clusters.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/list-clusters.sh","title":null}},{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./scripts/list-clusters.sh --region us-east-1","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Inspect cluster","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"scripts/cluster-info.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/cluster-info.sh","title":null}},{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./scripts/cluster-info.sh \u003ccluster-id>","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Connect via psql","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"scripts/psql-connect.sh","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/psql-connect.sh","title":null}},{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"./scripts/psql-connect.sh --cluster \u003cid> --command \"SELECT 1\"","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. List tables and explore schema","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"./scripts/psql-connect.sh --cluster \u003cid> --command \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\"\n./scripts/psql-connect.sh --cluster \u003cid> --command \"SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '\u003ctable>' ORDER BY ordinal_position\"","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Query data","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"Use psql-connect.sh (or the language connector in app code) for SELECT queries\nAlways include tenant_id in WHERE clause for multi-tenant apps\nMUST build SQL with safe_query.build() — see references/input-validation.md","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Execute schema changes","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"Use ./scripts/psql-connect.sh --admin (or the language connector with the IAM admin auth token) for DDL\nFollow one-DDL-per-transaction rule\nAlways use CREATE INDEX ASYNC in a separate statement\nALTER COLUMN TYPE, DROP COLUMN, DROP CONSTRAINT → Table Recreation Pattern (Workflow 6)","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Tasks","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 0: Verify Dependencies","type":"text"}]},{"type":"paragraph","content":[{"text":"Check for required tools and warn the user if any are missing.","type":"text"}]},{"type":"paragraph","content":[{"text":"Constraints:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You MUST verify the following tools are available before proceeding: ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" (>=14 for SNI support) and the AWS CLI v2 with ","type":"text"},{"text":"aws dsql generate-db-connect-auth-token","type":"text","marks":[{"type":"code_inline"}]},{"text":" (and ","type":"text"},{"text":"generate-db-connect-admin-auth-token","type":"text","marks":[{"type":"code_inline"}]},{"text":" for DDL/role setup)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You SHOULD also confirm the AWS MCP Server is available when the user's decision depends on a precise service limit; if absent, use the defaults in the table above and note that limits should be verified against DSQL documentation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You MUST inform the user about any missing tools with a clear message","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You MUST ask if the user wants to proceed despite missing tools","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You MUST use the scoped (non-admin) IAM auth token for read-only diagnostics whenever the user has a scoped role configured; reserve the IAM admin auth token for cluster setup, role grants, and DDL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"For cluster lifecycle (create / inspect / delete), see ","type":"text"},{"text":"Workflow 0a","type":"text","marks":[{"type":"link","attrs":{"href":"#workflow-0a-cluster-lifecycle","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Before writing application code, ALSO verify the language-specific DSQL Connector per ","type":"text"},{"text":"Workflow 0b","type":"text","marks":[{"type":"link","attrs":{"href":"#workflow-0b-verify-language-connector","title":null}}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 0a: Cluster Lifecycle","type":"text"}]},{"type":"paragraph","content":[{"text":"SHOULD","type":"text","marks":[{"type":"strong"}]},{"text":" use the bundled scripts for cluster create and delete — they issue atomic ","type":"text"},{"text":"aws dsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" CLI calls and process outputs.","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a cluster with tags and deletion protection:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/create-cluster.sh --created-by \u003cmodel-id> --tags Environment=eval,Project=dsql-skill-eval","type":"text"}]},{"type":"paragraph","content":[{"text":"Inspect a cluster (status, tags, endpoint, deletion protection):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/cluster-info.sh \u003ccluster-id>","type":"text"}]},{"type":"paragraph","content":[{"text":"Delete a cluster:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/delete-cluster.sh \u003ccluster-id> [--force] # --force skips the confirmation prompt in non-TTY","type":"text"}]},{"type":"paragraph","content":[{"text":"In MCP-only environments (no shell access), the equivalent calls go through the AWS MCP Server's ","type":"text"},{"text":"aws___call_aws","type":"text","marks":[{"type":"code_inline"}]},{"text":" tool. The tool takes a JSON payload — invoke it with arguments matching the AWS API operation:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"json"},"content":[{"text":"{\"service\": \"dsql\", \"operation\": \"CreateCluster\",\n \"parameters\": {\"tags\": {\"created_by\": \"\u003cmodel-id>\", \"Environment\": \"eval\", \"Project\": \"dsql-skill-eval\"}, \"deletionProtectionEnabled\": true}}","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"json"},"content":[{"text":"{\"service\": \"dsql\", \"operation\": \"GetCluster\", \"parameters\": {\"identifier\": \"\u003ccluster-id>\"}}","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"json"},"content":[{"text":"{\"service\": \"dsql\", \"operation\": \"DeleteCluster\", \"parameters\": {\"identifier\": \"\u003ccluster-id>\"}}","type":"text"}]},{"type":"paragraph","content":[{"text":"CreateCluster","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"DeleteCluster","type":"text","marks":[{"type":"code_inline"}]},{"text":" are asynchronous on the DSQL side — the API returns immediately with the cluster's current ","type":"text"},{"text":"status","type":"text","marks":[{"type":"code_inline"}]},{"text":" (","type":"text"},{"text":"CREATING","type":"text","marks":[{"type":"code_inline"}]},{"text":" / ","type":"text"},{"text":"DELETING","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Poll readiness by re-invoking ","type":"text"},{"text":"aws___call_aws","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"dsql:GetCluster","type":"text","marks":[{"type":"code_inline"}]},{"text":" until ","type":"text"},{"text":".status == \"ACTIVE\"","type":"text","marks":[{"type":"code_inline"}]},{"text":" (create) or the call returns a 404 (delete). ","type":"text"},{"text":"aws___get_tasks","type":"text","marks":[{"type":"code_inline"}]},{"text":" is for polling MCP-side long-running tool invocations — not the DSQL API.","type":"text"}]},{"type":"paragraph","content":[{"text":"See ","type":"text"},{"text":"AWS CLI ","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/cli/latest/reference/dsql/","title":null}}]},{"text":"aws dsql","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/cli/latest/reference/dsql/","title":null}},{"type":"code_inline"}]},{"text":" reference","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/cli/latest/reference/dsql/","title":null}}]},{"text":" for full parameter details and call context.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 0b: Verify Language Connector","type":"text"}]},{"type":"paragraph","content":[{"text":"Before writing application code, ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" verify the language-specific DSQL Connector is installed per ","type":"text"},{"text":"language.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/language.md","title":null}}]},{"text":". The Connectors are the canonical IAM-token-refresh path; bare drivers (","type":"text"},{"text":"pg","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"psycopg","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"pgx","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"tokio-postgres","type":"text","marks":[{"type":"code_inline"}]},{"text":") work until the first 15-minute token expiry and then start returning auth errors on every new connection — DSQL users who try the bare form report this as a DSQL bug. ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" install:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Python: ","type":"text"},{"text":"aurora-dsql-python-connector","type":"text","marks":[{"type":"code_inline"}]},{"text":" + the chosen driver wheel","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Node.js: ","type":"text"},{"text":"@aws/aurora-dsql-node-postgres-connector","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"@aws/aurora-dsql-postgresjs-connector","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Go: ","type":"text"},{"text":"github.com/awslabs/aurora-dsql-connectors/go/pgx","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Java: ","type":"text"},{"text":"software.amazon.dsql:aurora-dsql-jdbc-connector","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Rust: ","type":"text"},{"text":"aurora-dsql-sqlx-connector","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"paragraph","content":[{"text":"If a Connector is unavailable for the chosen runtime, document the manual token-refresh strategy and schedule with the user before writing code.","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 1: Create Multi-Tenant Schema","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"workflow-patterns.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/workflow-patterns.md","title":null}}]},{"text":" (Pattern 2: Create Table with Index) for step-by-step DDL sequencing, async index creation, and schema verification examples. Key rules: ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" in all tables, ","type":"text"},{"text":"CREATE INDEX ASYNC","type":"text","marks":[{"type":"code_inline"}]},{"text":" only, one DDL per transaction, arrays/JSON stored as TEXT.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 2: Safe Data Migration","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"workflow-patterns.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/workflow-patterns.md","title":null}}]},{"text":" (Pattern 3: Safe Data Migration) for the add-column → batch-populate → verify → index sequence. For tables exceeding 3,000 rows, also load ","type":"text"},{"text":"ddl-migrations/batched-migration.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/batched-migration.md","title":null}}]},{"text":". Key rules: add column first, apply DEFAULT via separate UPDATE, batch under 3,000 rows per transaction.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 3: Application-Layer Referential Integrity","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"workflow-patterns.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/workflow-patterns.md","title":null}}]},{"text":" (Pattern 5: Application-Layer Foreign Key Check) for the parent-existence SELECT → INSERT and dependent-count SELECT → DELETE patterns. Build all SQL with ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" — see Workflow 4a.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 4: Query with Tenant Isolation","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" authorize the caller against the tenant — format validation does not establish authorization","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" build SQL with ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/safe_query.py","title":null}},{"type":"code_inline"}]},{"text":" — use ","type":"text"},{"text":"allow()","type":"text","marks":[{"type":"code_inline"}]},{"text":"/","type":"text"},{"text":"regex()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for values (emits ","type":"text"},{"text":"'v'","type":"text","marks":[{"type":"code_inline"}]},{"text":"), ","type":"text"},{"text":"ident()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for table/column names (emits ","type":"text"},{"text":"\"v\"","type":"text","marks":[{"type":"code_inline"}]},{"text":"). See ","type":"text"},{"text":"input-validation.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/input-validation.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" include ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" in the WHERE clause; reject cross-tenant access at the application layer","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 4a: Rubric-Critical — Building SQL with User Input","type":"text"}]},{"type":"paragraph","content":[{"text":"Whenever constructing SQL for ","type":"text"},{"text":"psql -c \"...\"","type":"text","marks":[{"type":"code_inline"}]},{"text":" (or any equivalent ad-hoc query path) with any value that is not a developer-controlled literal (tenant IDs, entity IDs, sort columns, directions, status enums, free-text descriptions, request params — anything from untrusted sources), you MUST use ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"link","attrs":{"href":"scripts/safe_query.py","title":null}},{"type":"code_inline"}]},{"text":". The ","type":"text"},{"text":"psql -c","type":"text","marks":[{"type":"code_inline"}]},{"text":" flag takes raw SQL strings; it does NOT accept bound parameters. When using a Postgres driver (psycopg, pgx, etc.) in application code, prefer the driver's native parameter binding; ","type":"text"},{"text":"safe_query","type":"text","marks":[{"type":"code_inline"}]},{"text":" is the canonical fallback whenever you must build a raw SQL string. Validation via ","type":"text"},{"text":"safe_query","type":"text","marks":[{"type":"code_inline"}]},{"text":" is the primary defense for raw-SQL paths.","type":"text"}]},{"type":"paragraph","content":[{"text":"Validator selection table","type":"text","marks":[{"type":"strong"}]},{"text":" (canonical — mirrors ","type":"text"},{"text":"input-validation.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/input-validation.md","title":null}}]},{"text":"):","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Value kind","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Validator","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Emits","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Known set (tenant ID, status enum)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"allow(v, SET)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'value'","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Known set used as SQL keyword (ASC/DESC)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"keyword(v, SET)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"value","type":"text","marks":[{"type":"code_inline"}]},{"text":" (unquoted)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Strict format (UUID, slug, ISO date)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"regex(v, PATTERN)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'value'","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Table or column name","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ident(name)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"\"value\"","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Integer","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"integer(v)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"value","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Free text (description, comment, user name)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"literal(v)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"$dq_xxx$value$dq_xxx$","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Built-in patterns from ","type":"text"},{"text":"safe_query.py","type":"text","marks":[{"type":"code_inline"}]},{"text":": ","type":"text"},{"text":"TENANT_SLUG","type":"text","marks":[{"type":"code_inline"}]},{"text":" (","type":"text"},{"text":"[a-z0-9-]{1,64}","type":"text","marks":[{"type":"code_inline"}]},{"text":"), ","type":"text"},{"text":"UUID","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"INT","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"ISO_DATE","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Required imports","type":"text","marks":[{"type":"strong"}]},{"text":" at the top of every file that builds DSQL SQL:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"from safe_query import build, allow, regex, ident, keyword, integer, literal, UnsafeSQLError\nfrom safe_query import TENANT_SLUG, UUID, ISO_DATE","type":"text"}]},{"type":"paragraph","content":[{"text":"Rubric-Critical Scenario 1 — tenant_id from untrusted input.","type":"text","marks":[{"type":"strong"}]},{"text":" Validate with ","type":"text"},{"text":"regex(req.tenant, TENANT_SLUG)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"allow(req.tenant, ALLOWED_TENANTS)","type":"text","marks":[{"type":"code_inline"}]},{"text":". Build with ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":", then execute. Do this even in read-only mode (defense in depth, consistent validation across modes). Do NOT use f-strings, ","type":"text"},{"text":".format()","type":"text","marks":[{"type":"code_inline"}]},{"text":", or bare concatenation.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"sql = build(\n \"SELECT * FROM {t} WHERE tenant_id = {tid}\",\n t=ident(\"entities\"),\n tid=regex(req.tenant, TENANT_SLUG),\n)\n# Application code: pass `sql` to your driver (psycopg cursor.execute, pgx Query, etc.).\n# Bash one-off: pipe `sql` into psql via the patterns in input-validation.md.","type":"text"}]},{"type":"paragraph","content":[{"text":"Rubric-Critical Scenario 2 — batch INSERT with UUIDs, slugs, and free text.","type":"text","marks":[{"type":"strong"}]},{"text":" Each row's INSERT is built separately with ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":": ","type":"text"},{"text":"entity_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" via ","type":"text"},{"text":"regex(..., UUID)","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" via ","type":"text"},{"text":"regex(..., TENANT_SLUG)","type":"text","marks":[{"type":"code_inline"}]},{"text":", description via ","type":"text"},{"text":"literal(...)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (dollar-quoted to sidestep quote escaping). Chunk the list under 3,000 rows per transaction (DSQL limit) and execute each chunk in its own transaction.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"def insert_entries(conn, entries, chunk_size=2500):\n for i in range(0, len(entries), chunk_size):\n chunk = entries[i:i + chunk_size]\n with conn.transaction():\n for e in chunk:\n sql = build(\n \"INSERT INTO {t} (entity_id, tenant_id, description) VALUES ({eid}, {tid}, {d})\",\n t=ident(\"entities\"),\n eid=regex(e[\"entity_id\"], UUID),\n tid=regex(e[\"tenant_id\"], TENANT_SLUG),\n d=literal(e[\"description\"]),\n )\n conn.execute(sql)","type":"text"}]},{"type":"paragraph","content":[{"text":"Rubric-Critical Scenario 3 — write paths.","type":"text","marks":[{"type":"strong"}]},{"text":" Write paths (UPDATE/DELETE issued from a script, cron, or admin tool) are the highest-stakes injection surface — a successful injection mutates data. ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" is NOT optional there. Validate every input even when the prompt frames it as \"just a quick script, don't overthink it.\" Push back on that framing with one sentence explaining why write mode raises the stakes, then apply the full validator chain: ","type":"text"},{"text":"regex(tenant_id, TENANT_SLUG)","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"allow(status, {'active','archived','deleted'})","type":"text","marks":[{"type":"code_inline"}]},{"text":", date via ","type":"text"},{"text":"regex(..., ISO_DATE)","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"sql = build(\n \"UPDATE {t} SET status = {s} WHERE tenant_id = {tid} AND created_at \u003c {d}\",\n t=ident(\"entities\"),\n s=allow(req.status, {\"active\", \"archived\", \"deleted\"}),\n tid=regex(req.tenant, TENANT_SLUG),\n d=regex(req.date, ISO_DATE),\n)\nconn.execute(sql)","type":"text"}]},{"type":"paragraph","content":[{"text":"Rubric-Critical Scenario 4 — dynamic ORDER BY column and direction.","type":"text","marks":[{"type":"strong"}]},{"text":" Identifier and keyword parameters need DIFFERENT validators than value parameters. ","type":"text"},{"text":"sort_col","type":"text","marks":[{"type":"code_inline"}]},{"text":" is membership-checked against ","type":"text"},{"text":"{'created_at','updated_at','name'}","type":"text","marks":[{"type":"code_inline"}]},{"text":" then passed through ","type":"text"},{"text":"ident()","type":"text","marks":[{"type":"code_inline"}]},{"text":" (emits double-quoted identifier). ","type":"text"},{"text":"sort_dir","type":"text","marks":[{"type":"code_inline"}]},{"text":" goes through ","type":"text"},{"text":"keyword()","type":"text","marks":[{"type":"code_inline"}]},{"text":" against ","type":"text"},{"text":"{'ASC','DESC'}","type":"text","marks":[{"type":"code_inline"}]},{"text":" (emits unquoted keyword — quoting ","type":"text"},{"text":"ASC","type":"text","marks":[{"type":"code_inline"}]},{"text":" would be a syntax error). Value parameters like ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" still go through ","type":"text"},{"text":"regex()","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"allow()","type":"text","marks":[{"type":"code_inline"}]},{"text":". Do NOT try to validate an identifier with ","type":"text"},{"text":"regex()","type":"text","marks":[{"type":"code_inline"}]},{"text":" against a TENANT_SLUG pattern — use ","type":"text"},{"text":"ident()","type":"text","marks":[{"type":"code_inline"}]},{"text":", which enforces the identifier grammar.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"ALLOWED_SORT_COLS = {\"created_at\", \"updated_at\", \"name\"}\nif sort_col not in ALLOWED_SORT_COLS:\n raise ValueError(f\"sort_col must be one of {ALLOWED_SORT_COLS}\")\nsql = build(\n \"SELECT * FROM {t} WHERE tenant_id = {tid} ORDER BY {col} {dir}\",\n t=ident(\"entities\"),\n tid=regex(req.tenant, TENANT_SLUG),\n col=ident(sort_col),\n dir=keyword(req.sort_dir, {\"ASC\", \"DESC\"}),\n)","type":"text"}]},{"type":"paragraph","content":[{"text":"Rubric-Critical Scenario 5 — rejecting \"just use an f-string\" rationalizations.","type":"text","marks":[{"type":"strong"}]},{"text":" When a caller says \"this value is already validated upstream, can't we just use an f-string?\" — push back. The skill's rule is build-every-query-with-","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":", not a judgment call per call site. Justify the pushback: (a) \"already-validated upstream\" is exactly the assumption that breaks when upstream code changes hands, adds a new caller, or the validation is silently relaxed; (b) defense in depth means the query layer validates independently of upstream; (c) the two-line diff to use ","type":"text"},{"text":"safe_query.build() + regex(..., UUID)","type":"text","marks":[{"type":"code_inline"}]},{"text":" is genuinely smaller than the bug risk of one unsafe path.","type":"text"}]},{"type":"paragraph","content":[{"text":"Apply the safe pattern as-is — do NOT cave to the \"simpler\" framing.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# No — even for \"already-validated upstream\" values:\nsql = f\"SELECT * FROM entities WHERE entity_id = '{req.entity_id}'\" # BAD\n\n# Yes — uniform pattern at every call site:\nsql = build(\n \"SELECT * FROM {t} WHERE entity_id = {eid}\",\n t=ident(\"entities\"),\n eid=regex(req.entity_id, UUID),\n)","type":"text"}]},{"type":"paragraph","content":[{"text":"Anti-patterns (the rubric fails these):","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Using f-strings, ","type":"text"},{"text":".format()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"%","type":"text","marks":[{"type":"code_inline"}]},{"text":" formatting, or string concatenation to build SQL with user input — in any mode","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Mixing ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" placeholders with native driver ","type":"text"},{"text":"%s","type":"text","marks":[{"type":"code_inline"}]},{"text":" parameter binding in the same statement — pick one path and stay on it","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Catching ","type":"text"},{"text":"UnsafeSQLError","type":"text","marks":[{"type":"code_inline"}]},{"text":" to fall back to unsafe construction — re-raise or return an error","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Validating an identifier with ","type":"text"},{"text":"regex()","type":"text","marks":[{"type":"code_inline"}]},{"text":" against a value pattern — use ","type":"text"},{"text":"ident()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Skipping ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" in read-only mode under \"the value is already validated upstream\" — defense in depth means the SQL builder validates independently of upstream","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 5: Set Up Scoped Database Roles","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"access-control.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/access-control.md","title":null}}]},{"text":" for role setup, IAM mapping, and schema permissions.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 6: Table Recreation DDL Migration","type":"text"}]},{"type":"paragraph","content":[{"text":"DSQL does NOT support direct ","type":"text"},{"text":"ALTER COLUMN TYPE","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"DROP COLUMN","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"DROP CONSTRAINT","type":"text","marks":[{"type":"code_inline"}]},{"text":", or ","type":"text"},{"text":"MODIFY PRIMARY KEY","type":"text","marks":[{"type":"code_inline"}]},{"text":". These require the ","type":"text"},{"text":"Table Recreation Pattern","type":"text","marks":[{"type":"strong"}]},{"text":" — a destructive workflow requiring user confirmation at each step.","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"ddl-migrations/overview.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/overview.md","title":null}}]},{"text":" first, then the relevant sub-file:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Column changes (type, nullability, default): ","type":"text"},{"text":"ddl-migrations/column-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/column-operations.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Constraint/PK changes, column splits/merges: ","type":"text"},{"text":"ddl-migrations/constraint-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/constraint-operations.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Tables exceeding 3,000 rows: also load ","type":"text"},{"text":"ddl-migrations/batched-migration.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/batched-migration.md","title":null}}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 7: MySQL to DSQL Schema Migration","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load ","type":"text"},{"text":"mysql-migrations/type-mapping.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/type-mapping.md","title":null}}]},{"text":" for type mappings and feature alternatives. For DDL translation details load ","type":"text"},{"text":"mysql-migrations/ddl-operations.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/ddl-operations.md","title":null}}]},{"text":". For an end-to-end example load ","type":"text"},{"text":"mysql-migrations/full-example.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/mysql-migrations/full-example.md","title":null}}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Workflow 8: Query Plan Explainability","type":"text"}]},{"type":"paragraph","content":[{"text":"Triggered by slow queries, high DPU, unexpected Full Scans, or plans the user doesn't understand. A structured Markdown diagnostic report is the required deliverable — run the workflow end-to-end before answering.","type":"text"}]},{"type":"paragraph","content":[{"text":"MUST load all four reference files before starting:","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"query-plan/plan-interpretation.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/plan-interpretation.md","title":null}}]},{"text":" — node types, duration math, anomalous values","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"query-plan/catalog-queries.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/catalog-queries.md","title":null}}]},{"text":" — pg_class / pg_stats / pg_indexes SQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"query-plan/guc-experiments.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/guc-experiments.md","title":null}}]},{"text":" — GUC procedures and ","type":"text"},{"text":">30s","type":"text","marks":[{"type":"code_inline"}]},{"text":" skip protocol","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"query-plan/report-format.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/query-plan/report-format.md","title":null}}]},{"text":" — required report structure and elements checklist","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Phase 1 — Capture the plan.","type":"text","marks":[{"type":"strong"}]},{"text":" ALWAYS run ","type":"text"},{"text":"EXPLAIN ANALYZE VERBOSE","type":"text","marks":[{"type":"code_inline"}]},{"text":" on the user's query verbatim via ","type":"text"},{"text":"psql","type":"text","marks":[{"type":"code_inline"}]},{"text":" — even when the user describes or pastes the plan. SELECT runs as-is. UPDATE/DELETE: rewrite to the equivalent SELECT before running. INSERT, pl/pgsql, DO blocks, and functions MUST be rejected. MUST NOT run mutating DML during plan capture. When EXPLAIN errors, report verbatim — do not invent DSQL-specific semantics. Extract Query ID, Planning Time, Execution Time, and DPU Estimate.","type":"text"}]},{"type":"paragraph","content":[{"text":"Phase 2 — Gather evidence.","type":"text","marks":[{"type":"strong"}]},{"text":" Query ","type":"text"},{"text":"pg_class","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"pg_stats","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"pg_indexes","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"COUNT(*)","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"COUNT(DISTINCT)","type":"text","marks":[{"type":"code_inline"}]},{"text":" per ","type":"text"},{"text":"catalog-queries.md","type":"text","marks":[{"type":"code_inline"}]},{"text":". Classify estimation errors per ","type":"text"},{"text":"plan-interpretation.md","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Phase 3 — Experiment (conditional).","type":"text","marks":[{"type":"strong"}]},{"text":" ≤30s: run GUC experiments per ","type":"text"},{"text":"guc-experiments.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" plus redundant-predicate test. >30s: skip, include manual GUC SQL verbatim in the report. Anomalous row counts: confirm results are correct, flag as potential DSQL bug, produce Support Request Template.","type":"text"}]},{"type":"paragraph","content":[{"text":"Phase 4 — Report and invite reassessment.","type":"text","marks":[{"type":"strong"}]},{"text":" Produce the full diagnostic report per the Required Elements Checklist in ","type":"text"},{"text":"report-format.md","type":"text","marks":[{"type":"code_inline"}]},{"text":". End with the \"Next Steps\" block. When user says \"reassess\", re-run Phases 1–2 and append an \"Addendum: After-Change Performance\" to the original report.","type":"text"}]},{"type":"paragraph","content":[{"text":"psql invocation:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"./scripts/psql-connect.sh --cluster \u003cid> --command \"EXPLAIN ANALYZE VERBOSE \u003csql>\"\n./scripts/psql-connect.sh --cluster \u003cid> --script ./experiment-2.sql # GUC multi-statement","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Security Considerations","type":"text"}]},{"type":"paragraph","content":[{"text":"This section consolidates key security controls. For detailed guidance, see the linked reference files.","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"IAM auth token expiry:","type":"text","marks":[{"type":"strong"}]},{"text":" IAM auth tokens expire after 15 minutes. Always generate fresh tokens per connection or implement periodic refresh. ","type":"text"},{"text":"Never persist tokens to disk","type":"text","marks":[{"type":"strong"}]},{"text":" — keep them in memory only and discard after use. See ","type":"text"},{"text":"authentication-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/authentication-guide.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Scoped Roles Over Admin:","type":"text","marks":[{"type":"strong"}]},{"text":" Use scoped database roles with ","type":"text"},{"text":"dsql:DbConnect","type":"text","marks":[{"type":"code_inline"}]},{"text":" for all application connections. Reserve the ","type":"text"},{"text":"admin","type":"text","marks":[{"type":"code_inline"}]},{"text":" role strictly for initial cluster setup (creating roles, granting permissions). Revoke ","type":"text"},{"text":"dsql:DbConnectAdmin","type":"text","marks":[{"type":"code_inline"}]},{"text":" from setup IAM roles once scoped roles are established. See ","type":"text"},{"text":"access-control.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/access-control.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Encryption in Transit:","type":"text","marks":[{"type":"strong"}]},{"text":" SSL/TLS is enforced server-side. Use ","type":"text"},{"text":"sslmode=verify-full","type":"text","marks":[{"type":"code_inline"}]},{"text":" (default in DSQL connectors and ","type":"text"},{"text":"psql-connect.sh","type":"text","marks":[{"type":"code_inline"}]},{"text":") to validate the server certificate against DSQL's CA, preventing MITM attacks. Only downgrade to ","type":"text"},{"text":"require","type":"text","marks":[{"type":"code_inline"}]},{"text":" when the client lacks access to a trusted CA bundle.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Encryption at Rest:","type":"text","marks":[{"type":"strong"}]},{"text":" Aurora DSQL encrypts all data at rest using AWS-managed keys by default. No additional configuration is required; verify encryption status in cluster properties when compliance frameworks require attestation.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Audit Logging via CloudTrail:","type":"text","marks":[{"type":"strong"}]},{"text":" Enable CloudTrail logging for DSQL API calls to monitor token generation patterns, cluster configuration changes, and failed authentication attempts. Configure CloudWatch alarms for suspicious activity. Enable encryption on CloudWatch Log Groups used for DSQL monitoring using a KMS key to protect potentially sensitive query metadata. See ","type":"text"},{"text":"authentication-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/authentication-guide.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Write Paths Demand Strict Validation:","type":"text","marks":[{"type":"strong"}]},{"text":" Mutating SQL (UPDATE, DELETE, DDL) issued from scripts, cron jobs, or admin tools is the highest-stakes injection surface. Every write path ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" route through ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" (or the driver's native parameter binding when using a Postgres driver in application code).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Input Validation Is the Primary Defense:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"safe_query.build()","type":"text","marks":[{"type":"code_inline"}]},{"text":" is the primary defense against SQL injection on raw-SQL paths. Every value from untrusted input — tenant IDs, entity IDs, sort columns, free text — ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" pass through a validator (","type":"text"},{"text":"allow","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"regex","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"ident","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"keyword","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"integer","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"literal","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Do not use f-strings, ","type":"text"},{"text":".format()","type":"text","marks":[{"type":"code_inline"}]},{"text":", or concatenation. See ","type":"text"},{"text":"input-validation.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/input-validation.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Multi-Tenant Isolation as a Hard Contract:","type":"text","marks":[{"type":"strong"}]},{"text":" When the workload uses tenant scoping (Workflow 4), ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" appear in the WHERE clause of every read and write touching tenant-owned tables, and the application ","type":"text"},{"text":"MUST","type":"text","marks":[{"type":"strong"}]},{"text":" authorize the caller against that ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" before issuing the query — format validation alone does not establish authorization. Omitting ","type":"text"},{"text":"tenant_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" from a WHERE clause, or scoping to a tenant value the caller has not been authorized for, is a cross-tenant data exposure. This boundary is enforced by the skill, not by DSQL — verify every data-access path scopes to the authenticated tenant before deployment. See ","type":"text"},{"text":"access-control.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/access-control.md","title":null}}]},{"text":" and Workflow 4.","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Troubleshooting","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"AWS MCP Server returns no results:","type":"text","marks":[{"type":"strong"}]},{"text":" Use the default limits in the table above and note that limits should be verified against ","type":"text"},{"text":"DSQL documentation","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"OCC serialization error:","type":"text","marks":[{"type":"strong"}]},{"text":" Retry the transaction. If persistent, check for hot-key contention — see ","type":"text"},{"text":"troubleshooting.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/troubleshooting.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Transaction exceeds limits:","type":"text","marks":[{"type":"strong"}]},{"text":" Split into batches under 3,000 rows — see ","type":"text"},{"text":"batched-migration.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/ddl-migrations/batched-migration.md","title":null}}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"IAM auth token expiration mid-operation:","type":"text","marks":[{"type":"strong"}]},{"text":" Generate a fresh IAM auth token — see ","type":"text"},{"text":"authentication-guide.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/auth/authentication-guide.md","title":null}}]},{"text":". See ","type":"text"},{"text":"troubleshooting.md","type":"text","marks":[{"type":"link","attrs":{"href":"references/troubleshooting.md","title":null}}]},{"text":" for other issues.","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Additional Resources","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Aurora DSQL Documentation","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DSQL Connectors, Drivers, and ORM Samples (official)","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/aws-sdks.html","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PostgreSQL Compatibility","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CloudFormation Resource","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-dsql-cluster.html","title":null}}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Handoff from aws-database-selection","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill can be invoked directly, or it can be entered from the ","type":"text"},{"text":"aws-database-selection","type":"text","marks":[{"type":"code_inline"}]},{"text":" parent skill after that skill has run a requirements interview and produced a ","type":"text"},{"text":"requirements.json","type":"text","marks":[{"type":"code_inline"}]},{"text":" artifact. When you see a backtick-wrapped path matching ","type":"text"},{"text":"aws_dbs_requirements/*/requirements.json","type":"text","marks":[{"type":"code_inline"}]},{"text":" in recent conversation, follow the entry protocol in ","type":"text"},{"text":"aws-database-selection/references/handoff-contract.md","type":"text","marks":[{"type":"code_inline"}]},{"text":":","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Read the artifact using ","type":"text"},{"text":"file_read","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Validate it against ","type":"text"},{"text":"aws-database-selection/references/workload-primary-artifact.schema.json","type":"text","marks":[{"type":"code_inline"}]},{"text":". If malformed or unreadable, tell the user and proceed without it.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Acknowledge what's relevant in one or two ","type":"text"},{"text":"bold","type":"text","marks":[{"type":"strong"}]},{"text":" sentences, citing high-level facts from the artifact (dominant shapes, hard constraints, migration context) — do not parrot the entire artifact back.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Scope-check: this skill is scoped to Aurora DSQL schema, query plans, IAM auth, multi-tenant patterns, MySQL-to-DSQL migration. If the artifact's ","type":"text"},{"text":"workload_primaries.dominant_shapes","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"migration_context","type":"text","marks":[{"type":"code_inline"}]},{"text":" don't match that scope, emit weak backpressure per the handoff contract: suggest ","type":"text"},{"text":"amazon-aurora","type":"text","marks":[{"type":"code_inline"}]},{"text":" for Aurora PostgreSQL / MySQL, ","type":"text"},{"text":"rds-oss","type":"text","marks":[{"type":"code_inline"}]},{"text":" for RDS engines, or go back to ","type":"text"},{"text":"aws-database-selection","type":"text","marks":[{"type":"code_inline"}]},{"text":" if multi-region strong SQL consistency isn't required, then ask the user whether to go back or proceed anyway. Do not silently misuse the artifact.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Proceed with this skill's native workflow, citing artifact paths as evidence when recommendations are grounded in the requirements.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"All user-facing output from this skill follows the markdown-primitives-only formatting convention in the handoff contract: bold labels, backticks for paths and enum values, bullet lists for alternatives, no ASCII art or box-drawing characters.","type":"text"}]}]},"metadata":{"date":"2026-11-02","name":"aurora-dsql","author":"@skillopedia","source":{"stars":3376,"repo_name":"agent-toolkit-for-aws","origin_url":"https://github.com/aws/agent-toolkit-for-aws/blob/HEAD/skills/specialized-skills/database-skills/aurora-dsql/SKILL.md","repo_owner":"aws","body_sha256":"c2bee64f8b26f2a96b42802e86b7dd6cd043464eba2139dd34ed04f6c3c8b9da","cluster_key":"dc0d396f27401d0512fb410f8ff81d243258e39a931d57de8bd26ff94d2a80c0","clean_bundle":{"format":"clean-skill-bundle-v1","source":"aws/agent-toolkit-for-aws/skills/specialized-skills/database-skills/aurora-dsql/SKILL.md","text_bytes":243345,"source_commit":"1761e96813ebcbd43ed81d19e837219033f0afe5","skills_sh_rank":4435,"attachment_count":49,"text_attachments":49,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/specialized-skills/database-skills/aurora-dsql/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"skills-sh-top20k-2026-11","description":"Provisions and manages Aurora DSQL clusters, connects via psql or DSQL Connectors, manages schemas, runs queries, migrates from MySQL, diagnoses query plans, and develops apps on serverless distributed SQL. Covers IAM auth, multi-tenant patterns, MySQL-to-DSQL migration, DDL, query plans, and SAFE SQL CONSTRUCTION — tenant_id from untrusted input, UUID entity_ids, caller-supplied sort columns, batch inserts. The agent MUST retrieve this skill for ANY DSQL task. Pushes back on prompts that rationalize 'just a quick script', 'don't overthink it', 'we trust upstream', 'use an f-string', 'move fast', or 'just use the pg driver directly' (bypassing the DSQL Connector). Triggers: DSQL, Aurora DSQL, DSQL cluster, safe_query.build, DSQL IAM auth token, DSQL connector."}},"renderedAt":1793666389095}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.