# Option 4 — Direct Read from art_relations Database

## Overview

PDP is granted read-only credentials to the `art_relations` MySQL database (owned by ows-account)
and queries the `vendor_restricted_features` and `features` tables directly at request time.

No new ows-account endpoint, no Lambda, no CDC infrastructure.

> **Primary concern**: This introduces a direct dependency on the art_relations monolith database,
> coupling PDP's availability and correctness to another team's schema and operational decisions.
> PDP is currently backed entirely by DynamoDB. This would be the first MySQL dependency in the
> service.

---

## How It Works

1. At startup, PDP opens a MySQL connection pool to the art_relations read replica.
2. When a resource requires `account_feature_controls`, PDP queries the replica directly.
3. For subaccount tenants, the same query joins through the `vendor` table to resolve
   `vendor_uuid → vendor_id` — no separate ows-account call needed.

### Query

```sql
SELECT v.vendor_uuid, f.feature_id
FROM vendor v
CROSS JOIN features f
WHERE v.vendor_uuid IN (:uuids)
AND NOT EXISTS (
    SELECT 1
    FROM vendor_restricted_features vrf
    WHERE vrf.vendor_id = v.vendor_id
      AND vrf.feature_id = f.feature_id
)
ORDER BY v.vendor_uuid, f.feature_id
```

This is the same query proposed for the new ows-account endpoint in Option 2 — we'd just be
running it ourselves instead.

---

## PDP Changes

### New connector: `pdp/connectors/art_relations_db.py`

```python
class ArtRelationsDB:
    """Read-only client for the art_relations MySQL database."""

    def __init__(self, engine):  # SQLAlchemy async engine (asyncmy driver)
        self._engine = engine

    async def get_enabled_feature_ids_for_vendor_uuids(
            self, vendor_uuids: list[str]
    ) -> dict[str, list[int]]:
        async with self._engine.connect() as conn:
            result = await conn.execute(
                text("""
                    SELECT v.vendor_uuid, f.feature_id
                    FROM vendor v
                    CROSS JOIN features f
                    WHERE v.vendor_uuid IN :uuids
                    AND NOT EXISTS (
                        SELECT 1 FROM vendor_restricted_features vrf
                        WHERE vrf.vendor_id = v.vendor_id
                          AND vrf.feature_id = f.feature_id
                    )
                """),
                {"uuids": tuple(vendor_uuids)},
            )
        features: dict[str, list[int]] = {}
        for vendor_uuid, feature_id in result:
            features.setdefault(vendor_uuid, []).append(feature_id)
        return features
```

### New dependency: `asyncmy` (or `aiomysql`)

PDP currently has no MySQL driver. This is a new transitive dependency with its own
connection pool, timeout, and TLS configuration surface.

### Datasources initialization

Add the MySQL engine to lifespan startup in `pdp/fastapi/datasources.py`, similar to the
Redis connector today. Requires DB hostname, port, credentials (from Secrets Manager or
environment), and connection pool sizing.

### Hydration integration

`_hydrate_resources_with_feature_controls_as_needed` accepts `ArtRelationsDB` instead of
`OwsAccountClient`. Subaccount→vendor resolution is handled by the query itself (the JOIN
through the `vendor` table), so no separate `lookup_subaccounts_by_uuids` call is needed.

---

## ows-account Changes

None.

---

## Pros

- **Simplest implementation** — no new endpoints, no Lambda, no RDS cluster to own.
- **Real-time data** — no eventual consistency lag; reads reflect the current DB state.
- **No ows-account changes** — entire change is contained within ows-pdp.
- **Subaccount resolution in one query** — JOIN through `vendor` table handles UUID↔ID mapping
  without a separate HTTP call.

---

## Cons

### Coupling to a monolith database

This is the primary concern. Direct DB access bypasses the abstraction layer that ows-account
provides:

- **Schema coupling**: Any rename, column type change, or table restructure in art_relations
  (including `vendor`, `features`, `vendor_restricted_features`) silently breaks PDP. There is
  no API contract, no versioning, no deprecation notice — just a runtime error.
- **Team boundary violation**: The ows-account team owns art_relations. Granting another
  service direct read access makes PDP an undocumented consumer of their schema, complicating
  future migrations (e.g., if ows-account wants to move to Postgres or rename a column).
- **No enforcement of business logic**: ows-account may have logic around which features are
  "visible" or "enabled" beyond the raw DB rows (e.g., soft-deletes, active flags). Direct DB
  reads bypass any such logic.

### New technology dependency

- **First MySQL client in PDP**: PDP is a DynamoDB-backed service. Adding a MySQL connection
  pool introduces a new infrastructure class with its own operational concerns: connection
  pool exhaustion, TLS certificate rotation, read-replica lag, failover behavior.
- **Connection pool to a production monolith**: Even with a read replica, PDP's query load
  would be visible to the art_relations DB. A traffic spike in PDP (e.g., load test, event
  storm) directly impacts the monolith's read capacity.

### Availability coupling

- If the art_relations read replica is unavailable (failover, maintenance, lag too high),
  PDP's feature control lookups fail. PDP's SLA becomes a function of art_relations' SLA,
  despite them being separate services with separate owners.
- Currently, if ows-account has an outage, PDP degrades gracefully for non-feature-control
  resources. With a direct DB dependency, an art_relations incident affects all requests that
  require feature controls, with no fallback.

### Access control / governance

- Requires a read-only DB user and corresponding credentials management (Secrets Manager,
  rotation policy).
- Needs network access from PDP's VPC to the art_relations RDS security group — a new
  firewall rule that must be approved and documented.
- The ows-account team must consent to granting another service direct schema access.

---

## Comparison with Options 1, 2, and 3

| | Option 1 (Fetch flag) | Option 2 (Policy-driven) | Option 3 (CDC replica) | **Option 4 (Direct DB)** |
|---|---|---|---|---|
| New ows-account endpoint | No | Yes | No (steady state) | **No** |
| Extra HTTP call at request time | No (bundled) | Yes | No (local query) | **No (direct query)** |
| ows-account changes | Low-medium | Medium | None | **None** |
| ows-pdp changes | Medium | High | High + infra | **Medium** |
| Data freshness | Real-time | Real-time | Eventually consistent | **Real-time** |
| Resilience to ows-account outage | Low | Low | High | **Low (DB instead)** |
| Schema coupling to monolith | None | None | None | **High** |
| New DB technology in PDP | No | No | Yes (RDS, owned) | **Yes (MySQL, not owned)** |
| Operational complexity | Low | Medium | High | **Medium** |
| Upfront implementation cost | Low-medium | Medium | High | **Low** |

---

## Recommendation

Option 4 has the lowest implementation cost and real-time consistency, but the schema coupling
and monolith dependency are significant long-term risks. It trades short-term simplicity for
long-term fragility.

If the team is comfortable with the coupling, it could work as a short-term solution while
Option 3 (CDC replica) is stood up — but migrating away later requires changing PDP's data
access layer, which is non-trivial.

The key question for the team: **Is a direct DB dependency on art_relations acceptable under
our service ownership model?** If the ows-account team rotates schema without notice, or if
art_relations has a planned migration, PDP becomes a blocker.