011. Application data-access contract: RDS Data API as the sole network path

• Date: 2026-06-03
• Status: Accepted
• Amended: 2026-07-18. Deployed ingestion migrated onto this contract and both open questions resolved; see the Amendment section at the end.
• Accepted: 2026-07-18

Decision

• In the context of defining how application components in rag-sample reach the Aurora pgvector database, given a need that holds independently of network topology, and a stack that today includes the API Lambda and the bearer-token Authorizer Lambda but will plausibly grow to include workers, scheduled jobs, or alternative runtimes,
• facing the choice of a data-access contract that all current and future application components conform to, that survives Aurora Serverless v2 auto-pause and resume, that has a coherent IAM model, and that does not silently pressure new components toward inconsistent posture,
• we decided for the RDS Data API as the sole network-facing data-access path for application code, with psycopg reserved for the local Docker Postgres environment only: development and any operator-run command-line tooling that targets local Docker, including ingestion (RAG003) and plain-SQL migrations (RAG002); migrations and ingestion remain plain SQL, executed via the Data API when deployed (the earlier reservation of operator-run ingestion/migration tooling against deployed Aurora is superseded by the 2026-07-18 amendment, since RAG010 exposes no such path into the private subnets); the contract applies to every deployed application component, today the API Lambda (#23) and the Authorizer Lambda (#60), and tomorrow any worker, scheduled job, or alternative runtime that needs database access,
• and neglected
  • psycopg from an in-VPC Lambda, with NAT Gateway for Bedrock and Secrets Manager egress. Solves the connection model in the obvious way, at the cost of pulling the application Lambdas into a VPC, paying the ENI cold-start tax, holding pooled connections that may break across Aurora auto-pause, and adding the cost lines documented in RAG010 for NAT. Re-evaluable if RAG010 is reversed; on its own merits as a contract choice, also weaker than the chosen option on the connection-lifecycle and IAM-model axes.
  • psycopg from an in-VPC Lambda, with PrivateLink endpoints for Bedrock, Secrets Manager, and the AWS services it transitively requires. Cleaner network posture than NAT, same ENI-and-pool consequences for the application, and the cost line in RAG010 grows linearly with the service list. Re-evaluable if RAG010 is reversed.
  • psycopg with RDS Proxy in front of Aurora. Solves the auto-pause connection-break problem cleanly, since Proxy holds the pool and serves application connections through it. Still pulls the application into a VPC (Proxy is in-VPC), still needs NAT or PrivateLink for Bedrock and Secrets Manager, and adds Proxy itself as a fixed cost line. Stronger than vanilla in-VPC psycopg on connection lifecycle; the rest of the trade-off is unchanged.
  • Aurora with a public endpoint plus psycopg from a Lambda outside the VPC. Avoids both the in-VPC posture and the network-topology cost stack, and is the only path that picks psycopg without requiring RAG010 to be reversed. Rejected because it requires Aurora to accept inbound traffic from arbitrary internet origins (or from a maintained Lambda-IP allowlist, which is operationally hostile), and because we get no operational benefit at this scale that the chosen contract does not also give us.
  • a mixed contract where some components use the Data API and others use psycopg. Forces every reviewer to relearn which path applies to which component, and silently re-introduces VPC-attachment pressure on the first component that picks psycopg.

to achieve

• a single, stable data-access contract that every current and future application component conforms to, so architectural reasoning stays coherent as components are added,
• an IAM-native authorization model (rds-data:* actions on the cluster ARN scoped per execution role) that aligns with the rest of the AWS IAM surface the stack already uses,
• tolerance for Aurora auto-pause and resume, since Data API calls are stateless and do not hold connections across pauses, with no connection-pool layer to design and maintain,
• clean separation between application data access (network, stateless, IAM-authed) and local-Docker tooling data access (psycopg, transactional, credential-authed, operator-run against local Docker only), each fit-for-purpose,

accepting

• no cross-request transactions in application code, since each Data API call is its own transaction; multi-statement units of work require either a single ExecuteStatement batch or an explicit BeginTransaction / CommitTransaction pair that lives inside one request,
• Data API request and result-set size limits (~64KB SQL, ~1MB result), which constrain bulk reads and writes from application code,
• no LISTEN / NOTIFY, no server-side cursors, and no streaming results in application paths,
• a different IAM model than connection-string auth (statements gated by rds-data:* actions on the cluster ARN, not by Postgres roles), which the migration and ingestion tooling must reconcile,
• a divergence between the production data-access path (Data API) and the local-development path (psycopg over Docker), partially mitigated by keeping all production SQL in plain .sql files runnable through both paths.

Relationship to network topology

This ADR and RAG010 are independent decisions that happen to reinforce each other. The contract chosen here is viable under the current “no IGW, no NAT” topology and is the contract that lets that topology hold for application components specifically. The contract would still be the right choice if RAG010 were reversed tomorrow, because the in-VPC alternatives are weaker on connection lifecycle, IAM model, and component-growth coherence even when the network-cost objection is removed.

If RAG010 is reversed, the reversal does not by itself invalidate this contract. It only reopens the in-VPC psycopg options for explicit re-evaluation under that ADR’s reversal procedure.

Consequences

The API Lambda and the Authorizer Lambda run outside any VPC. Aurora’s security group carries no rule permitting access from a Lambda ENI, because no application Lambda has one. Aurora Serverless v2 is configured with enable_http_endpoint = true and min_capacity = 0.

Application SQL is executed via rds-data:ExecuteStatement and BatchExecuteStatement. Multi-statement units of work are wrapped in BeginTransaction / CommitTransaction within the lifetime of one request. The application provider abstraction (rag_sample.providers) exposes a Data-API-shaped repository interface, and no application code holds a database connection.

The ingestion CLI and the migration runner use psycopg against local Docker Postgres only. Against deployed Aurora there is no psycopg path, because RAG010 provides no route into the private subnets; deployed ingestion and deployed migrations therefore execute their plain SQL (RAG002) through the Data API, the same as application code. The 2026-07-18 amendment records this and corrects the earlier claim of an operator-authenticated psycopg path to deployed Aurora, which RAG010 does not make reachable. Migrations remain plain SQL, runnable against both engines without translation. Settings (pydantic-settings) carry both a Data-API connection descriptor (cluster ARN + secret ARN + database name) and a psycopg DSN; the psycopg DSN targets local Docker only.

If a future workload genuinely cannot satisfy its requirement under this contract (a streaming workload, a high-throughput batch path that exceeds Data API quotas, a path that requires LISTEN / NOTIFY), the workload introduces its requirement as an explicit exception in its own ADR, scoped to that component. The default contract remains the Data API.

Open questions

Both resolved by the 2026-07-18 amendment; Sprint 5 supplied the operational reason and the second consumer.

• Whether ingestion should also migrate to the Data API contract. Resolved: yes. Deployed ingestion runs on the Data API; psycopg is local-Docker-only. See the Amendment.
• Whether to formalize the Data-API-shaped repository interface across components. Resolved: yes. One repository port with psycopg and Data-API implementations, consumed by both the API query path and ingestion. See the Amendment.

Amendment 2026-07-18: deployed ingestion runtime and repository port (Sprint 5)

Sprint 5 makes the QA environment usable end-to-end, which requires deploying the UI and embedding and storing the Hugging Face corpus in deployed Aurora. Two blockers forced the questions this ADR had deferred: the API Lambda still used psycopg against database_url (unreachable in QA), and corpus ingestion was psycopg-only and local-only. Both are resolved here without touching RAG010 (no IGW, no NAT).

Decisions

1. The Data API is the sole network path to deployed Aurora for every runtime, application and ingestion alike. psycopg is reserved exclusively for local Docker Postgres. No psycopg path to deployed Aurora exists, because RAG010 provides no route into the private subnets; the Data API public endpoint is the only reachable path, and it is IAM-authed (rds-data:*), stateless, and tolerant of min_capacity = 0 auto-pause. This resolves the first open question.

2. One repository port, two implementations. The data access in rag_sample.db.repository is formalized behind a single port consumed by both the API query path and ingestion. PsycopgRepository backs local Docker; DataApiRepository (via ExecuteStatement and BatchExecuteStatement, vector(1024) text literals, batches bounded under the ~64KB SQL and ~1MB result limits) backs every deployed runtime. The DI container (RAG017) wires the implementation per environment. This resolves the second open question; two consumers now exist.

3. Ingestion runs as an out-of-VPC Lambda, not operator tooling. The Ingestion Lambda already modeled in architecture/_deployments.c4 (region scope, no VPC attachment) reaches Hugging Face and Bedrock over the public internet and Aurora over the Data API, with no NAT. A Lambda without VPC attachment has internet egress by AWS-managed networking, so RAG010’s “no NAT” does not block it. Ingestion is not application request-path code, but it conforms to the same deployed-Aurora contract as application code.

4. Bulk ingestion is time-boxed and parallelizable via a lease claim, staying well under the 15-minute Lambda ceiling with a ~7-minute per-invocation target. Work is claimed, not partitioned up front.

   • Embed phase: the chunks table is the work queue. A worker claims a batch in one atomic Data-API statement, UPDATE chunks SET lease_expires_at = now() + interval '10 min', leased_by = :w WHERE id IN (SELECT id FROM chunks WHERE embedding IS NULL AND (lease_expires_at IS NULL OR lease_expires_at < now()) ORDER BY id LIMIT :n FOR UPDATE SKIP LOCKED) RETURNING id, content, embeds outside any held lock, then writes each embedding back. FOR UPDATE SKIP LOCKED hands disjoint batches to concurrent workers; the lease, not a held row lock, is what recovers crashed workers. The claim is a single statement, satisfying the one-transaction-per-Data-API-call constraint.
   • Load phase: an ingest_pages control table holds page descriptors over the deterministically ordered Parquet rows (RAG003); workers claim pages with the same SKIP-LOCKED plus lease idiom.
   • Each invocation loops until a wall-clock budget is spent (leaving ~90s headroom) then exits; unclaimed and lease-expired work is picked up by the next or a parallel invocation.

Consequences and accepted trade-offs

• At-least-once, not exactly-once. upsert_document (ON CONFLICT (source, external_id)) and set_chunk_embedding (overwrite) are idempotent, and external_id = filename:i is a stable natural key over static Parquet files, so reprocessing a lease-expired shard is harmless.
• Bedrock quota, not lock contention, caps real parallelism. Concurrent Titan-embed workers can hit account TPS limits; the existing bedrock_max_attempts retry plus idempotent re-embed absorb throttling. Ingest-function reserved concurrency bounds worker width to stay within quota.
• Lease timeout must exceed maximum per-batch wall time (embed plus retries plus write) so a slow-but-live worker is not stolen from; a 10-minute lease against a ~7-minute invocation with small batches is safe.
• Aurora min_capacity = 0: the first Data-API call after idle pays a one-time resume latency.
• Embedder dimension not yet governed by an ADR. The vector(1024) column type derives from the Amazon Titan Text Embeddings V2 config (embedding_model_id / embedding_dim in config.py); RAG007 governs only the LLM. Flag to pin the embedder and dimension in a future ADR so the schema’s vector(1024) has a governing source.
• RAG011 is now Status: Accepted (promoted 2026-07-18), reflecting that the contract is binding for Sprint 5 delivery. RAG010 remains Proposed and is unaffected by this promotion.