Chapter 105 — Secure API & Data-Access Implementation

105.1 Introduction

A secure AI platform requires more than a secure database schema. The application layer that reads and modifies that database must enforce the same security assumptions consistently.

The data-access layer is where application requests become database operations. If this boundary is poorly designed, vulnerabilities can appear even when authentication, encryption, database constraints, and network security are correctly configured.

Common failures include:

• Insecure Direct Object Reference (IDOR)
• Broken Object-Level Authorization (BOLA)
• Cross-tenant data exposure
• Unauthorized updates or deletions
• Unsafe filtering and sorting
• Excessive database queries
• Unbounded result sets
• Race conditions
• Inconsistent transactions
• Accidental exposure of sensitive fields
• Unsafe bulk operations
• Cache/data-isolation failures
• Missing audit events

This chapter defines a secure implementation model for repositories, CRUD services, authorization-aware data access, tenant isolation, pagination, transactions, concurrency control, and data-access testing.

---

105.2 Data-Access Architecture

A recommended architecture is:

Client
   ↓
API Route / Controller
   ↓
Authentication
   ↓
Authorization / Policy Engine
   ↓
Application Service
   ↓
Repository Interface
   ↓
Repository Implementation
   ↓
ORM / Query Builder
   ↓
PostgreSQL

The important principle is that each layer has a specific responsibility.

Controller

Responsible for:

• HTTP input
• request validation
• authentication context
• response formatting
• HTTP status codes

The controller should not contain complicated database logic.

Authorization Layer

Responsible for:

• determining who the caller is
• determining what the caller may do
• determining which tenant they belong to
• checking resource ownership
• checking role and policy requirements

Service Layer

Responsible for:

• business rules
• workflows
• transaction boundaries
• orchestration
• authorization-aware operations
• audit-event generation

Repository Layer

Responsible for:

• database queries
• persistence
• query composition
• data mapping
• database-specific implementation details

Database

Responsible for:

• constraints
• referential integrity
• indexes
• transactions
• row-level security where applicable
• durable persistence

---

105.3 Why the Repository Pattern Matters

Direct database access from controllers creates several problems.

For example:

API endpoint
   ↓
SQL/ORM query
   ↓
database

This can cause:

• duplicated queries
• inconsistent authorization
• inconsistent tenant filtering
• difficult testing
• tightly coupled business logic
• difficult migrations
• accidental exposure of database models

A repository abstraction provides a controlled data-access boundary.

Conceptually:

UserService
    ↓
UserRepository
    ↓
Database

The service understands business operations.

The repository understands persistence.

---

105.4 Repository Interfaces

A repository should expose operations appropriate to the application rather than exposing unrestricted database access.

Example conceptual interface:

interface ProjectRepository {
  findById(context: TenantContext, id: string): Promise<Project | null>;

  create(
    context: TenantContext,
    input: CreateProjectInput
  ): Promise<Project>;

  update(
    context: TenantContext,
    id: string,
    input: UpdateProjectInput
  ): Promise<Project>;

  delete(
    context: TenantContext,
    id: string
  ): Promise<void>;
}

The important detail is the inclusion of TenantContext.

A repository should not have to guess which tenant the request belongs to.

---

105.5 Tenant Context

A secure request context can contain:

requestId
userId
tenantId
roles
permissions
authenticationMethod
sessionId

The repository receives the trusted tenant context from the authenticated application layer.

It should not accept arbitrary tenant IDs supplied by the browser as the source of truth.

Unsafe pattern:

GET /projects?tenantId=another-tenant

The server should never simply trust this value.

Instead:

authenticated user
        ↓
server-derived tenant context
        ↓
repository query

Client-provided tenant identifiers may sometimes be used as filters, but they must be validated against the authenticated context and authorization policy.

---

105.6 Preventing IDOR and BOLA

One of the most important API security principles is:

Knowing an object's identifier does not automatically grant access to that object.

Suppose a user owns:

project_123

and another user's project is:

project_999

A request such as:

GET /api/projects/project_999

must not automatically return the resource merely because the ID is valid.

The secure query concept is:

SELECT *
FROM projects
WHERE id = :projectId
  AND tenant_id = :authenticatedTenantId;

The object identifier and authorization boundary must be evaluated together.

---

105.7 Authorization-Aware Repository Methods

A secure repository should make unauthorized access difficult by design.

Instead of:

findProject(id)

prefer a context-aware operation:

findProject(context, id)

Conceptually:

findProject(
    authenticated tenant,
    authenticated user,
    project ID
)

The repository can then enforce tenant ownership and other constraints.

For highly sensitive resources, authorization may need to happen at multiple layers:

Policy Engine
      ↓
Service
      ↓
Repository query
      ↓
Database constraints / RLS

Defense in depth is valuable because a single missed authorization check should not automatically become a cross-tenant breach.

---

105.8 CRUD Operations

CRUD means:

Create
Read
Update
Delete

Each operation needs independent authorization analysis.

Create

Validate:

• authenticated identity
• tenant
• allowed resource type
• input schema
• quotas
• ownership rules

Do not allow users to arbitrarily assign:

tenantId
ownerId
role
subscriptionTier
securityLevel

unless the operation explicitly authorizes those fields.

---

105.9 Read Operations

Read operations should verify:

Authentication
      ↓
Tenant
      ↓
Resource authorization
      ↓
Field-level access
      ↓
Response filtering

Authentication alone is insufficient.

A user may be authenticated but still lack permission to view a particular resource.

---

105.10 Update Operations

Updates are especially dangerous because attackers may attempt parameter manipulation.

For example, a client may submit:

{
  "name": "Project",
  "role": "admin",
  "tenantId": "other-tenant"
}

The server must distinguish between:

user-editable fields

and:

server-controlled fields

Use explicit allowlists.

Conceptually:

const allowed = {
  name: input.name,
  description: input.description
};

Do not blindly spread an entire request object into a database update.

Avoid patterns equivalent to:

update(dataFromRequest)

when the request contains fields the user should not control.

---

105.11 Delete Operations

Deletion requires:

• authorization
• ownership checks
• dependency checks
• audit logging
• transaction handling
• retention policy enforcement

For some resources, soft deletion may be preferable.

Example:

deletedAt = timestamp

instead of immediately removing the record.

However, soft deletion is not a security boundary by itself. Queries must consistently exclude deleted records where appropriate.

---

105.12 Pagination

Never allow APIs to return unlimited database results.

Unsafe:

GET /projects

with no effective limit.

A malicious or accidental request could cause:

• excessive database load
• high memory consumption
• slow responses
• large network transfers
• denial-of-service conditions

A safer model is:

default page size = controlled value
maximum page size = controlled value

For example:

default: 25
maximum: 100

The exact values should be determined from workload testing.

---

105.13 Offset Pagination

Traditional pagination uses:

LIMIT
OFFSET

Example concept:

SELECT *
FROM projects
WHERE tenant_id = :tenantId
ORDER BY created_at DESC
LIMIT :limit
OFFSET :offset;

It is simple and useful for many administrative interfaces.

However, very large offsets can become increasingly expensive.

---

105.14 Cursor Pagination

Cursor pagination can be more efficient for large datasets.

Conceptually:

page 1
   ↓
last record cursor
   ↓
page 2
   ↓
next cursor

Example:

GET /projects?cursor=abc123&limit=25

The cursor should be:

• validated
• bounded
• tied to the correct query
• resistant to tampering
• safe to expire when appropriate

For sensitive systems, opaque signed cursors can prevent clients from manipulating internal pagination state.

---

105.15 Stable Ordering

Pagination requires deterministic ordering.

Bad:

ORDER BY created_at

if multiple rows can have the same timestamp.

A stronger ordering can use:

created_at DESC
id DESC

The secondary identifier provides deterministic ordering.

This reduces duplicate or missing records between pages.

---

105.16 Filtering

APIs often support filters:

status
type
createdAt
owner
category

Filters must be validated against an allowlist.

Do not allow arbitrary database column names from the client.

Unsafe conceptual pattern:

?sort=<arbitrary SQL expression>

Instead:

allowedSortFields = [
  createdAt,
  name,
  status
]

The server maps public API names to known database expressions.

---

105.17 Sorting

Sorting is another common injection boundary.

Instead of directly inserting:

sortBy=userInput

use a mapping:

"name"        → "name"
"createdAt"   → "created_at"
"updatedAt"   → "updated_at"

Then separately validate:

asc
desc

This converts an open-ended database expression into a controlled query option.

---

105.18 Search

Search functionality should also be bounded.

Potential controls include:

• minimum search length
• maximum search length
• rate limits
• indexed columns
• query timeouts
• result limits
• normalized input
• safe full-text search
• appropriate database indexes

Search endpoints should not become unrestricted database scanning mechanisms.

---

105.19 Transactions

A transaction groups related database operations into a single logical unit.

Example:

Create project
   ↓
Create project membership
   ↓
Create audit event
   ↓
Commit

If one critical operation fails, the transaction can roll back.

Conceptually:

BEGIN

operation A
operation B
operation C

COMMIT

If a critical failure occurs:

ROLLBACK

---

105.20 Transaction Boundaries

Transactions should be neither unnecessarily broad nor dangerously narrow.

A good transaction normally covers a single business operation requiring atomicity.

Avoid keeping transactions open while waiting for:

• AI model responses
• external HTTP APIs
• file uploads
• long computations
• user interaction

For example, avoid:

BEGIN
 ↓
call AI provider
 ↓
wait 30 seconds
 ↓
database update
 ↓
COMMIT

Instead:

request
 ↓
validate
 ↓
create job
 ↓
COMMIT
 ↓
worker calls AI provider
 ↓
transaction updates final state

This reduces database lock duration.

---

105.21 Transaction Isolation

Different workloads require different transaction-isolation characteristics.

The platform should explicitly understand:

• read phenomena
• concurrent writes
• locking behavior
• serialization conflicts
• retry requirements

Do not select the strongest isolation level everywhere without workload analysis.

Higher isolation can increase contention and reduce throughput.

---

105.22 Optimistic Concurrency Control

Two users may attempt to modify the same resource.

Example:

User A reads version 10
User B reads version 10

User A updates → version 11
User B updates → conflict

A version column can help:

version = 10

The update concept becomes:

UPDATE projects
SET name = :name,
    version = version + 1
WHERE id = :id
  AND version = :expectedVersion;

If zero rows are affected, the application can report a concurrency conflict.

This prevents silent overwrites.

---

105.23 Soft Deletion

A common pattern is:

deleted_at

Queries then include:

deleted_at IS NULL

However, developers must ensure that:

• unique constraints behave correctly
• indexes account for active records
• deleted objects cannot accidentally reappear
• authorization still applies
• retention policies remain enforced
• permanent deletion workflows exist when required

---

105.24 Audit Logging

Important data mutations should generate security-relevant audit events.

Examples:

project.created
project.updated
project.deleted
membership.created
membership.removed
permission.changed
export.created
sensitive_record.accessed

An audit record can contain:

eventId
timestamp
actorId
tenantId
action
resourceType
resourceId
requestId
result
metadata

Do not place secrets or unnecessary sensitive payloads into audit logs.

---

105.25 Bulk Operations

Bulk APIs can improve performance but increase risk.

Example:

DELETE 5,000 records

requires additional controls.

Possible safeguards:

• maximum batch size
• authorization per operation
• transaction boundaries
• rate limits
• audit records
• asynchronous processing
• confirmation for destructive actions
• job status tracking

Never assume that because a user can delete one object, they automatically have unlimited bulk-delete authority.

---

105.26 Secure Data Exports

Exports are high-risk because they aggregate information.

Examples:

CSV export
JSON export
PDF report
analytics dataset
database snapshot

Controls should include:

• authorization
• tenant validation
• field filtering
• maximum export size
• rate limits
• asynchronous generation
• secure temporary storage
• expiration
• download authorization
• audit logging

An export endpoint should never become a shortcut around normal object-level authorization.

---

105.27 Field-Level Data Protection

Not every user who can access a record should necessarily see every field.

For example:

User profile
├── displayName
├── email
├── billing metadata
├── security metadata
└── internal notes

The API should return only fields permitted by the caller's role and policy.

A useful design is to create explicit response DTOs:

PublicUserDTO
AdminUserDTO
InternalUserDTO

rather than returning raw ORM entities.

---

105.28 DTOs and Database Models

Database models should not automatically become API response models.

Database models may contain:

• internal IDs
• timestamps
• security metadata
• provider identifiers
• internal state
• soft-delete markers
• billing information

The API should expose a controlled representation.

Architecture:

Database Model
      ↓
Mapper
      ↓
Response DTO
      ↓
API Client

This reduces accidental data leakage.

---

105.29 Data-Access Errors

Repositories should distinguish between conditions such as:

NotFound
Unauthorized
Conflict
ValidationFailure
DatabaseFailure
Timeout
Unavailable

Do not expose raw database errors to clients.

Unsafe:

PostgreSQL error: duplicate key constraint users_email_key

A safer API response might be:

{
  "error": {
    "code": "RESOURCE_CONFLICT",
    "message": "The requested operation could not be completed."
  }
}

Detailed database diagnostics belong in protected server-side logs.

---

105.30 N+1 Query Prevention

An N+1 query problem occurs when an application performs:

1 query for projects
+
1 query per project

For 1,000 projects:

1 + 1,000 = 1,001 queries

This can significantly degrade performance.

Solutions include:

• joins
• controlled eager loading
• batching
• DataLoader-style patterns
• aggregate queries
• optimized repository methods

However, eager-loading everything is also dangerous because it can produce huge queries.

The goal is controlled data retrieval.

---

105.31 Query Complexity Controls

AI platforms often contain expensive operations involving:

• large documents
• vector search
• media metadata
• analytics
• usage history
• generation history

The API should impose resource limits.

Examples:

maximum rows
maximum page size
maximum search length
maximum filter complexity
maximum export size
maximum query duration

Resource controls protect both security and reliability.

---

105.32 Caching and Authorization

Caching can introduce serious data-isolation vulnerabilities.

Suppose a response is cached using only:

/project/123

but the resource is tenant-specific.

A second user might receive the first user's cached response.

Cache keys must account for security boundaries where necessary:

tenantId
userId
resourceId
authorization context

Sensitive responses should be cached cautiously.

---

105.33 Repository-Level Security Invariants

A strong repository design defines explicit invariants.

For example:

Every tenant-owned query must contain tenant scope.

Every resource mutation must verify authorization.

Every externally supplied ID must be validated.

Every list operation must have a bounded limit.

Every destructive operation must be auditable.

Every update must use an explicit field allowlist.

These invariants should become code-review requirements and automated tests.

---

105.34 Service-Level Authorization

Repository checks should not replace business authorization.

Example:

Repository:
    User belongs to tenant.

Service:
    User has permission to delete project.

Both answer different questions.

Repository:

Can this caller's context access this tenant-scoped object?

Service:

Is this particular operation allowed under the business policy?

---

105.35 Database-Level Defense in Depth

For highly sensitive multi-tenant systems, database-level controls such as PostgreSQL Row-Level Security can provide another boundary.

Conceptually:

Application authorization
        ↓
Repository tenant filter
        ↓
Database RLS
        ↓
PostgreSQL

This does not eliminate the need for application authorization.

Instead, it reduces the blast radius of application mistakes.

---

105.36 Secure Data-Access Flow

A complete request can follow:

1. Receive HTTP request
        ↓
2. Validate syntax
        ↓
3. Authenticate user
        ↓
4. Establish tenant context
        ↓
5. Check authorization
        ↓
6. Validate resource identifier
        ↓
7. Validate query parameters
        ↓
8. Call application service
        ↓
9. Begin transaction if required
        ↓
10. Execute repository operation
        ↓
11. Apply database constraints
        ↓
12. Commit
        ↓
13. Write security/audit event
        ↓
14. Map entity to DTO
        ↓
15. Return bounded response

---

105.37 Example Secure Project Update Flow

Consider:

PATCH /api/projects/123

The secure implementation conceptually becomes:

Request
 ↓
Authentication
 ↓
Tenant context
 ↓
Validate project ID
 ↓
Validate request body
 ↓
Check project access
 ↓
Check edit permission
 ↓
Load current version
 ↓
Validate editable fields
 ↓
Begin transaction
 ↓
Update with tenant + version conditions
 ↓
Create audit event
 ↓
Commit
 ↓
Return sanitized DTO

This is substantially safer than:

PATCH
 ↓
ORM.update(request.body)

---

105.38 Testing Strategy

Data-access security must be tested directly.

Unit Tests

Test:

• repository methods
• validation
• field allowlists
• pagination
• filtering
• authorization decisions
• error mapping

Integration Tests

Test:

• real PostgreSQL behavior
• transactions
• constraints
• tenant isolation
• RLS if enabled
• concurrency
• indexes
• rollback behavior

API Tests

Test:

authenticated user
unauthenticated user
same-tenant user
different-tenant user
administrator
resource owner
non-owner
expired session
insufficient permission

---

105.39 Cross-Tenant Security Tests

A particularly important test pattern is:

Tenant A creates resource A
Tenant B creates resource B

Then test:

Tenant A → resource A = allowed
Tenant A → resource B = denied
Tenant B → resource A = denied
Tenant B → resource B = allowed

Repeat this for:

• read
• update
• delete
• search
• export
• bulk operations
• attachments
• AI generations
• vector documents
• cached responses

---

105.40 Authorization Matrix

Maintain an explicit matrix.

Resource	Action	Owner	Member	Admin
Project	Read	✓	✓	✓
Project	Update	✓	Policy-based	✓
Project	Delete	✓	✗	✓
User	Read	Own	Policy-based	✓
User	Modify permissions	✗	✗	✓
Export	Create	Policy-based	Policy-based	✓

The actual matrix must be defined by the application's business requirements.

---

105.41 Security Verification Checklist

Before considering the data-access layer production-ready:

Repository

• [ ] Repository abstraction exists.
• [ ] Tenant context is explicit.
• [ ] Raw database access is restricted.
• [ ] Queries are parameterized.
• [ ] Field allowlists are used.
• [ ] Resource identifiers are validated.

Authorization

• [ ] Authentication is enforced.
• [ ] Object-level authorization is enforced.
• [ ] Tenant boundaries are enforced.
• [ ] Role/permission checks are centralized.
• [ ] Sensitive fields are protected.

Pagination

• [ ] Default limits exist.
• [ ] Maximum limits exist.
• [ ] Ordering is deterministic.
• [ ] Cursor validation exists where applicable.

Transactions

• [ ] Business transaction boundaries are defined.
• [ ] Long external operations occur outside DB transactions.
• [ ] Rollbacks are tested.
• [ ] Concurrency behavior is tested.

Data Protection

• [ ] DTOs prevent accidental field exposure.
• [ ] Exports are authorized.
• [ ] Caches respect tenant boundaries.
• [ ] Audit events are generated.
• [ ] Sensitive logs are minimized.

Testing

• [ ] IDOR tests exist.
• [ ] Cross-tenant tests exist.
• [ ] Unauthorized mutation tests exist.
• [ ] Bulk-operation tests exist.
• [ ] Concurrency tests exist.
• [ ] Database integration tests exist.

---

105.42 Reference Architecture

The resulting architecture is:

                    ┌─────────────────────┐
                    │       Client        │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │    API Controller   │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ Authentication      │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ Authorization       │
                    │ + Tenant Context   │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ Application Service │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ Repository Layer    │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ ORM / Query Builder │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ PostgreSQL + RLS    │
                    └─────────────────────┘

This structure creates clear security boundaries while keeping application logic maintainable.

---

105.43 Final Principles

The most important principles from this chapter are:

1. Never equate authentication with authorization.
2. Never trust client-provided tenant identity.
3. Never assume a valid object ID grants access.
4. Keep authorization and tenant isolation explicit.
5. Use repositories to centralize persistence behavior.
6. Use services for business rules and workflows.
7. Use explicit field allowlists for mutations.
8. Bound every list, search, and export operation.
9. Use transactions for operations requiring atomicity.
10. Keep external calls outside long-running database transactions.
11. Use concurrency controls where simultaneous edits are possible.
12. Treat caches as part of the security boundary.
13. Never return raw database entities blindly.
14. Audit important security-sensitive mutations.
15. Test cross-tenant access explicitly.
16. Use defense in depth with database controls where appropriate.

A secure API is therefore not merely an HTTP interface connected to PostgreSQL. It is an authorization-aware data boundary that continuously enforces identity, tenant scope, business permissions, data minimization, resource limits, transactional correctness, and auditability.

---

Chapter 105 Conclusion

The API and data-access layer is one of the most important enforcement points in a secure AI platform.

A strong implementation separates controllers, authorization, business services, repositories, ORM operations, and database protections. Tenant context and object-level authorization must remain explicit throughout the request lifecycle, while pagination, query validation, concurrency control, transactions, caching, exports, and auditing prevent common security and reliability failures.

With this foundation established, the platform can safely proceed to the next major backend security boundary: authentication and session implementation.

Next: Chapter 106 — Secure Authentication & Session Implementation: Passwords, MFA, Session Management, Token Security, Account Recovery & Authentication Hardening