How to Ingest Data from APIs and Databases for AI

Why API and Database Ingestion Is Different

Document and web ingestion starts with unstructured text and works to preserve its meaning through extraction and cleaning. API and database ingestion starts with structured data, records with typed fields, relationships between tables, and schema definitions, and works to convert that structure into natural language that an LLM can reason about. The core challenge is not extraction (the data is already cleanly accessible) but transformation: turning a JSON response or database row into text that embeds accurately and reads naturally when retrieved as context.

The other fundamental difference is that APIs and databases are live systems. A PDF, once ingested, does not change until someone uploads a new version. A database row can change every second. An API response reflects the current state of a system that is continuously updated by users, automated processes, and integrations. This means the ingestion pipeline must handle not just initial loading but ongoing synchronization, detecting changes, updating the knowledge base, and removing records that no longer exist in the source. The incremental ingestion guide covers the synchronization patterns in detail.

REST API Ingestion

REST APIs are the most common source of structured data for AI applications. A typical ingestion flow authenticates with the API, paginates through the available records, transforms each record into a text document, and writes the result to the ingestion pipeline for cleaning, validation, and storage.

Authentication varies by API. API key authentication is the simplest: include the key as a header (Authorization: Bearer {key}) or query parameter. OAuth 2.0 requires a token exchange before making data requests, with token refresh logic to handle expiration during long ingestion runs. Some APIs use HMAC signatures that must be computed for each request. The ingestion connector should handle authentication transparently, so the rest of the pipeline never sees authentication details.

Pagination is where most teams encounter their first real difficulty. APIs return data in pages, typically 20 to 100 records per response, and the connector must iterate through all pages to get the complete dataset. The three common pagination styles are offset based (page=2&limit=50), cursor based (after=eyJpZCI6MTIzfQ), and link based (following the "next" URL in the response headers or body). Cursor based pagination is the most reliable because it handles records being added or deleted during the crawl without skipping or duplicating records, which offset based pagination cannot guarantee.

Rate limiting is essential for responsible API consumption. Most APIs enforce request rate limits (typically 60 to 1000 requests per minute) and return HTTP 429 responses when the limit is exceeded. The connector should read rate limit headers (X-RateLimit-Remaining, X-RateLimit-Reset, Retry-After), track request counts, and proactively throttle before hitting the limit rather than waiting for 429 responses. Exponential backoff with jitter handles cases where the rate limit is exceeded despite proactive throttling.

Error handling must account for the realities of network communication and API reliability. Transient errors (timeouts, 502/503/504 responses) should be retried with exponential backoff. Permanent errors (400 Bad Request, 404 Not Found) should be logged and skipped. Partial failures (a paginated crawl that succeeds for 95 pages and fails on page 96) should save progress so the next run can resume from where it stopped rather than restarting from the beginning. Recording the last successfully processed cursor or page number in the sync state enables this resume capability.

GraphQL API Ingestion

GraphQL APIs allow the client to specify exactly which fields to retrieve, which is an advantage for ingestion because you can request only the fields relevant to your AI application rather than receiving the entire object. A GraphQL query for customer ingestion might request name, plan, contact history, and support tickets while excluding internal audit fields, system timestamps, and binary attachments that would add noise without value.

The main considerations specific to GraphQL are query complexity limits and nested pagination. Many GraphQL APIs enforce complexity budgets that limit how much data a single query can request. A query that fetches customers with their nested orders, each with nested line items, each with nested product details, might exceed the complexity budget even though it is a single query. The solution is to fetch data in layers: first the top level records, then the nested data for each record in separate queries, assembling the complete record in the connector before passing it to the transformation stage.

Nested pagination adds another layer of complexity. A customer might have hundreds of orders, each returned in pages. The connector must paginate through both the top level (customers) and each nested collection (orders per customer, line items per order), producing potentially thousands of API calls for a moderately sized dataset. Batching nested queries and parallelizing independent requests keeps the total ingestion time manageable.

SQL Database Ingestion

Direct database access eliminates the pagination, rate limiting, and authentication complexity of API ingestion but introduces different challenges: query design, connection management, and the risk of impacting production database performance.

The fundamental question is what to query. A naive approach queries each table independently and textualizes each row. This produces documents that lack context because the information about a single entity (a customer, a product, a support case) is spread across multiple tables connected by foreign keys. A better approach uses JOIN queries that assemble complete entity views: a customer query that joins the customer table with their orders, support tickets, and account history produces a single comprehensive document per customer that contains everything the AI might need to answer questions about that customer.

Connection management for ingestion differs from application database access. Application queries are short lived (milliseconds) and concurrent (many queries from many users). Ingestion queries are long lived (minutes to hours for a full table scan) and sequential (one query iterating through results). Using a server side cursor that fetches rows in batches (typically 1000 to 5000 rows) prevents the ingestion process from loading the entire result set into memory while keeping the database connection open for the duration of the scan.

Performance isolation is critical when ingesting from a production database. A full table scan or complex JOIN query can consume significant I/O and CPU resources, degrading performance for application queries running concurrently. The standard mitigation is to query a read replica rather than the primary database. If a read replica is not available, schedule ingestion during low traffic periods and add query hints (LIMIT, timeouts) that prevent runaway queries from monopolizing database resources. Monitoring the database's query performance during ingestion runs helps calibrate the right balance between ingestion throughput and production impact.

NoSQL Database Ingestion

NoSQL databases (MongoDB, DynamoDB, Elasticsearch, Redis) store data in formats that vary widely, from document stores with rich nested objects to key value stores with simple string values. The ingestion approach depends on the database type and the shape of the data.

Document stores like MongoDB are the easiest to ingest because each document is already a self contained unit with nested fields. The ingestion connector iterates through the collection, and each document maps naturally to a single ingestion document. The transformation step converts the nested JSON structure into readable text, preserving the hierarchical relationships. A MongoDB document with nested arrays (a customer with an array of addresses, each with street, city, state, zip) should be textualized with the nesting made explicit: "Customer John Smith has two addresses on file. The primary address is 123 Main Street, Springfield, IL 62701."

Key value stores like Redis typically contain cached or session data that is less useful for AI knowledge bases. When key value data is relevant (configuration settings, feature flags, reference data), the ingestion connector reads all keys matching a pattern, groups related keys, and produces a textualized summary of each group.

Wide column stores like DynamoDB require understanding the partition and sort key structure to query efficiently. A full table scan (Scan operation) reads every item but consumes significant read capacity and is expensive for large tables. If the ingestion use case can be satisfied by querying specific partitions (all records for a specific customer, all events in a specific time range), Query operations are dramatically more efficient. The ingestion connector should use the most selective query possible and fall back to a scan only when the full dataset is truly needed.

Textualization: The Critical Transformation

Textualization is the process of converting structured records into natural language descriptions. It is the most important step in API and database ingestion because it determines how well the data will embed, retrieve, and appear in LLM generated answers. The structured vs unstructured data guide introduces this concept; here we cover the implementation details.

A good textualization template produces text that reads naturally, includes all relevant fields with appropriate context, and omits internal or technical fields that would add noise. Consider a support ticket record:

Raw: {"id": 4821, "customer_id": 1234, "subject": "Login failure after password reset", "status": "resolved", "priority": "high", "created": "2026-08-20T14:30:00Z", "resolved": "2026-08-21T09:15:00Z", "agent": "Sarah Chen", "resolution": "Reset OAuth token cache"}

Textualized: "Support ticket 4821, reported by customer 1234 on August 20, 2026: Login failure after password reset. Priority was high. The ticket was resolved on August 21, 2026 by agent Sarah Chen. Resolution: Reset OAuth token cache. Time to resolution was approximately 19 hours."

The textualized version adds context that the raw record lacks (the time to resolution, readable dates, natural sentence structure) while omitting nothing that a human answering questions would need. It embeds well because the embedding model captures "login failure," "password reset," "OAuth token cache," and the resolution details as semantic concepts rather than as disconnected JSON values.

Template based textualization uses string templates with field placeholders. This is the simplest approach and works well when the record structure is consistent. The template for the support ticket example might be: "Support ticket {id}, reported by customer {customer_id} on {created_readable}: {subject}. Priority was {priority}..." Each record is rendered through the template, producing consistent, readable text.

For records with variable schemas (some fields present on some records but not others), the template must handle optional fields gracefully, omitting sentences about missing fields rather than producing "Priority was None" or similar artifacts. Conditional template sections that check for field presence before rendering handle this cleanly.

LLM based textualization uses a language model to generate natural language descriptions from structured records. This produces more fluent, contextually appropriate text than templates but adds cost and latency. It is most valuable for complex records where the relationships between fields are not easily captured by a template, such as financial transactions where the significance of a transaction depends on the account type, transaction history, and regulatory context. For most applications, template based textualization provides sufficient quality at a fraction of the cost.

Schema Context Documents

Individual record documents tell the AI about specific entities but not about the broader context: what fields mean, what value ranges are normal, how entities relate to each other. Schema context documents fill this gap by providing reference information that the retrieval system can pull alongside specific records.

A schema context document for a customer table might say: "The customer database contains 12,400 active customers across four plan tiers: free (6,200 customers), starter (3,800), professional (1,900), and enterprise (500). Monthly recurring revenue ranges from $0 for free accounts to $25,000 for the largest enterprise accounts, with a median of $89 across all paid accounts. Churn risk scores range from 0.0 (no risk) to 1.0 (certain churn), with the overall average at 0.15 and scores above 0.5 considered elevated risk."

This context document enables the AI to answer questions that require understanding the data distribution: "Is customer X's MRR above average?" or "How does this churn risk compare to typical?" Without the schema context, the AI can only report the raw value without interpretation.

Generate schema context documents during the initial ingestion run by computing aggregate statistics (counts, averages, ranges, distributions) for each field. Update them on a regular schedule (daily or weekly) as the underlying data changes. Store them with metadata that identifies them as context documents so the retrieval system can include them when relevant without returning them for every query.

Scheduling and Freshness

API and database data changes continuously, and the ingestion pipeline must run on a schedule that keeps the knowledge base reasonably current without overwhelming the source system with requests. The right schedule depends on how quickly the data changes and how stale the AI's answers can be before users notice.

For customer records, product catalogs, and reference data that changes infrequently (a few updates per day), daily ingestion runs are typically sufficient. For support tickets, order status, and operational data that changes throughout the day, hourly ingestion keeps the knowledge base current enough for most use cases. For real time systems like inventory levels, pricing, and monitoring alerts, the ingestion pipeline needs to process updates within minutes, which requires event driven ingestion rather than scheduled polling. The real time vs batch ingestion guide covers the architectural differences.

Change detection for APIs typically relies on modification timestamps. The connector records the timestamp of the most recently modified record during each run. On the next run, it queries only records modified after that timestamp. This reduces the volume of data transferred and processed on each run from the full dataset to just the changes. For APIs that do not support filtering by modification time, the connector must fetch all records and compare them against previously ingested versions using content hashing, which is less efficient but still better than reprocessing unchanged records through the full pipeline.

For databases, change data capture (CDC) provides the most efficient change detection. CDC tools (Debezium for most SQL databases, MongoDB change streams for MongoDB) monitor the database's transaction log and emit events for every insert, update, and delete. The ingestion pipeline subscribes to these events and processes only the changed records, achieving near real time freshness with minimal database load. CDC requires more infrastructure than timestamp based polling but scales better and provides lower latency for large, frequently changing datasets.

Building the Connector

The pipeline building guide describes the connector pattern that API and database connectors should follow. Each connector implements list_documents() (returning available record IDs with their modification timestamps) and extract_document(id) (returning a textualized Document object for a specific record). This interface lets the pipeline handle API data, database data, documents, and web content through the same cleaning, validation, and output stages.

For APIs with complex authentication, pagination, and rate limiting, the connector encapsulates all of that complexity behind the simple two method interface. The rest of the pipeline does not know or care that the data came from a paginated REST API with OAuth authentication and cursor based pagination. It receives a Document object with text and metadata, identical in format to what the file connector or web connector produces.

Testing API connectors requires handling the fact that the API is an external dependency that may be slow, rate limited, or temporarily unavailable. Record real API responses during development and replay them during testing to verify connector behavior without depending on API availability. This also enables testing edge cases (malformed responses, unexpected field types, empty pages) that are difficult to trigger reliably against a live API.