Metadata-Version: 2.4
Name: irides-core
Version: 0.1.0
Summary: A Python library for extracting structured database metadata to provide reliable context to AI systems and data-driven applications.
Author-email: Gian Andrea Sechi <me@gianandreasechi.com>
License: Apache-2.0
Project-URL: Source Code, https://github.com/GianAndreaSechi/irides/core
Project-URL: Homepage, https://www.gianandreasechi.com
Keywords: database,introspection,metadata,irides,schema,redis
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: pydantic>=2.0
Requires-Dist: redis>=4.0
Requires-Dist: loguru>=0.7.0
Requires-Dist: python-dotenv>=1.0.0
Requires-Dist: PyYAML>=6.0
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9.0; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: mysql-connector-python>=8.0.0; extra == "mysql"
Provides-Extra: mongo
Requires-Dist: pymongo>=4.0.0; extra == "mongo"
Provides-Extra: duckdb
Requires-Dist: duckdb>=0.9.0; extra == "duckdb"
Provides-Extra: aws
Requires-Dist: boto3>=1.26.0; extra == "aws"
Provides-Extra: trino
Requires-Dist: trino>=0.320.0; extra == "trino"
Provides-Extra: ai
Requires-Dist: litellm>=1.0.0; extra == "ai"
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9.0; extra == "all"
Requires-Dist: mysql-connector-python>=8.0.0; extra == "all"
Requires-Dist: pymongo>=4.0.0; extra == "all"
Requires-Dist: duckdb>=0.9.0; extra == "all"
Requires-Dist: boto3>=1.26.0; extra == "all"
Requires-Dist: trino>=0.320.0; extra == "all"
Requires-Dist: litellm>=1.0.0; extra == "all"
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.20.0; extra == "dev"

# Core

Shared Python library used by both the **API** and the **Worker**. It provides database connector abstractions, Pydantic models, Redis cache management, configuration loading, metadata persistence, and the async job store.

---

## Package Structure

```
core/db_connector/
├── connectors/           # DB-specific connector implementations
│   ├── mysql.py
│   ├── postgres.py
│   ├── sqlite.py
│   ├── duckdb.py
│   ├── dynamodb.py
│   ├── mongodb.py
│   ├── athena.py
│   ├── trino.py
│   └── presto.py
├── exporting/            # Multi-format artifact exporting (Markdown, OKF)
│   ├── __init__.py
│   ├── models.py         # ExportFormat, ExportOptions
│   ├── preformatters.py  # essential_record deterministic view
│   ├── markdown.py       # Markdown table renderer
│   ├── okf.py            # Open Knowledge Format (OKF v0.2) renderer
│   └── artifact_store.py # FileArtifactStore (atomic write, 0644, bundle index)
├── models/               # Pydantic data models
│   ├── instance.py
│   ├── schema.py
│   ├── table.py
│   ├── column.py
│   ├── table_details.py  # TableDescription, PrimaryKey, ForeignKey, Index, Partition
│   └── scan_job.py       # ScanJob, ScanScope, ScanStatus
├── interface.py          # BaseConnector abstract class
├── manager.py            # ConnectorManager (auto-discovers BaseConnector implementations)
├── cache_manager.py      # Redis cache (get/set with prefix + TTL)
├── config_service.py     # ConfigService — resolves configs & instances to connectors
├── configurations.py     # DB configuration loading from environment variables
├── storage.py            # Metadata persistence (BaseMetadataStore + FileMetadataStore)
└── job_store.py          # JobStore — Redis Stream queue + job metadata & result storage
```

---

## Key Modules

### `exporting/`

Provides multi-format artifact exporting decoupled from raw JSON storage.

- **`ExportFormat`**: Enum supporting `markdown` and `okf` (Open Knowledge Format v0.2). Both are generated by default.
- **`ExportOptions`**: Controls derived artifact generation (`formats: list[ExportFormat]`, `preformat: bool = True`). Supports explicit opt-out.
- **`preformatters.py` (`essential_record`)**: Deterministic view preserving identity, summary, columns, keys, relations, unique non-primary indexes, partitions, owner, tags, and status. Excludes non-unique secondary indexes and verbose internal metadata to optimize token usage.
- **`markdown.py` (`render_markdown`)**: Generates structured Markdown tables, keys, relationships, indexes, and partitions. Used both for standalone Markdown output and the OKF document body.
- **`okf.py` (`render_okf`)**: Renders OKF v0.2 documents with YAML frontmatter (`type: Database Table`, title, description, tags, generator metadata, identifiers) followed by the Markdown body.
- **`artifact_store.py` (`FileArtifactStore`)**: Persists artifacts to `STORAGE_EXPORT_DIR` via atomic writes with readable `0644` file permissions, maintaining separate directory hierarchies for Markdown and OKF catalog bundles with an auto-updated `index.md`:

```text
storage/
  metadata/
    {config}/{instance}/{schema}/{table}.json
  exports/
    markdown/
      {config}/{instance}/{schema}/{table}.md
    okf/
      catalog/
        index.md
        {config}/{instance}/{schema}/{table}.md
```

### `storage.py`

Provides metadata persistence abstractions.

**`BaseMetadataStore`** — abstract interface with these methods:

| Method | Description |
|---|---|
| `save_table_metadata(...)` | Write or update a table metadata document and derived exports |
| `get_table_metadata(...)` | Read a stored document by config+instance+schema+table |
| `list_instances(page, page_size)` | Paginated list of all stored instance names |
| `list_databases(instance_name, page, page_size)` | Paginated list of databases for an instance |
| `list_tables_metadata(instance_name, database_name, page, page_size)` | Paginated list of table names |
| `find_table_metadata(instance_name, database_name, table_name)` | Look up a table across all configs |
| `update_table_metadata(instance_name, database_name, table_name, payload)` | Merge custom fields into a stored document and regenerate exports |

**`FileMetadataStore`** (default) — saves canonical JSON documents under `STORAGE_METADATA_DIR` and derived exports under `STORAGE_EXPORT_DIR`.

Key behaviours:

- **Decoupled export pipeline**: Derived artifacts (Markdown, OKF) are generated independently from JSON metadata persistence. Setting `save_metadata=False` still generates exports if requested.
- **Automatic export regeneration**: Calling `update_table_metadata` to merge human annotations automatically regenerates the corresponding Markdown and OKF documents.
- **Custom field carry-forward**: any key not in `_SYSTEM_KEYS` (`metadata_key`, `config_name`, `instance_name`, `schema_name`, `table_name`, `updated_at`, `schema_description`, `ai_documentation`) is preserved across re-describe calls. Human-added fields such as `owner`, `tags`, and `notes` survive schema refreshes.
- **`only_if_changed`**: when `True`, `save_table_metadata` skips the JSON write if `schema_description` is identical to the stored version, leaving `updated_at` and human annotations untouched, but exports the current state.
- **`ai_documentation` preservation**: if `ai_documentation=None` is passed, the existing stored AI doc is kept rather than overwritten.
- **Protected fields**: `update_table_metadata` silently ignores `metadata_key`, `config_name`, `instance_name`, `schema_name`, `table_name`, and `updated_at` in the payload — these are always managed by the system.

Paginated list responses follow this envelope:
```json
{
  "items": ["name_a", "name_b"],
  "total": 2,
  "page": 1,
  "page_size": 20,
  "pages": 1
}
```

Use `get_metadata_store()` (factory function) to obtain the configured store. Set `METADATA_STORE_TYPE=file` (default) or extend with future backends (`s3`, `athena`).

### `ai_service.py`
**`AIDocumentationService`**: Non-blocking integration with LiteLLM (`LITELLM_MODEL`, default `gpt-4o-mini`). Generates high-level domain summaries and column descriptions. When requested, generated docs are attached to `TableDescription.ai_documentation` with `ai_generation_status`; failures also include `ai_generation_error`. If `litellm` is uninstalled, API keys are missing, or network errors occur, it logs a warning and returns no documentation without throwing exceptions.

### `configurations.py`
Reads database connection parameters from environment variables. `DB_TARGETS` supports any number of named targets for any connector.
- Supports `DB_CONFIG_FILE` environment variable to explicitly specify the path to a container `.env` file (e.g. `/app/api/.env`), falling back to default `load_dotenv()` discovery when unset.
- Target names from `DB_TARGETS` become API/MCP `config_name` values. Example: `DB_TARGETS=sales_mysql,analytics_pg` creates `sales_mysql` and `analytics_pg` configurations.
- Each target uses `DB_TARGET_<TARGET_KEY>_*` variables, where `<TARGET_KEY>` is the uppercased target name with non-alphanumeric characters replaced by underscores.
- Exact required and optional keys for each connector type are documented in the root README under **DB Configuration & Activation**.

### `config_service.py`
Wraps `ConnectorManager` and `configurations`.
- **`list_instances(config_name, no_cache)`**: Uniformly lists instances for both multi-host configurations (MySQL/MariaDB) and flat configurations (Athena, DynamoDB, Trino, MongoDB, SQLite).
- **`resolve_instance_names(config_name, instance_name, no_cache)`**: Returns `[instance_name]` if specified, or all discovered instances if `instance_name` is `None`.
- **`_get_hosts(config_name)`**: Returns explicitly configured hosts, correctly recognizing both multi-host `hosts` collections and flat `host` parameters.
- **`configuration_matches_instance(config_name, instance_name, no_cache)`**: Checks whether an instance belongs to a given configuration.
- **`resolve_configurations_for_instance(instance_name, no_cache)`**: Centralized lookup returning all configuration names matching a target instance.
- **`_get_connector_for_host(config_name, host)`**: Returns the connector for a specific host, falling back to flat connection parameters when static host definitions are omitted.

### `cache_manager.py`
Redis-backed cache for introspection results. All keys are prefixed with `CACHE_KEY_PREFIX`. Cache can be bypassed per-call with `no_cache=True`.

### `job_store.py`
Manages async scan jobs via Redis:
- **Stream** (`{prefix}:scan:queue`) — job queue for workers (`scan-workers` consumer group). Stream messages serialize `export_formats` and `export_preformat` parameters.
- **Hash** (`{prefix}:scan:job:{job_id}`) — job metadata, scope, and status. Supports deserialization of new export options with backwards compatibility for legacy jobs with `save_markdown`.
- **List** (`{prefix}:scan:results:{job_id}`) — serialized `TableDescription` results with automatic TTL extensions on writes.
- **Sorted Set** (`{prefix}:scan:jobs`) — job index ordered by creation timestamp, automatically pruned of entries older than `RESULTS_TTL` via `zremrangebyscore` to prevent Redis memory leaks.

---

## Supported Databases

| Database | Connector Type | Configuration Style |
|---|---|---|
| MySQL / MariaDB | `mysql` | Named `DB_TARGETS` |
| PostgreSQL | `postgres` | Named `DB_TARGETS` |
| SQLite | `sqlite` | Named `DB_TARGETS` |
| DuckDB | `duckdb` | Named `DB_TARGETS` |
| Amazon DynamoDB | `dynamodb` | Named `DB_TARGETS` |
| Amazon Athena | `athena` | Named `DB_TARGETS` |
| MongoDB | `mongodb` | Named `DB_TARGETS` |
| Trino | `trino` | Named `DB_TARGETS` |
| Presto | `presto` | Named `DB_TARGETS` |

---

## Environment Variables

Copy `.env.example` to `.env` and configure as needed.

| Variable | Default | Description |
|---|---|---|
| `REDIS_HOST` | `localhost` | Redis host |
| `REDIS_PORT` | `6379` | Redis port |
| `REDIS_DB` | `0` | Redis database index |
| `REDIS_TTL_SECONDS` | `86400` | Introspection cache TTL (1 day) |
| `CACHE_KEY_PREFIX` | `irides` | Prefix for all Redis keys |
| `SCAN_RESULTS_TTL_SECONDS` | `604800` | Scan result retention in Redis (7 days) |
| `DB_CONFIG_FILE` | *(none)* | Explicit path to `.env` configuration file |
| `DB_TARGETS` | *(none)* | Comma-separated list of named DB targets |
| `STORAGE_METADATA_DIR` | `storage/metadata` | Metadata JSON output directory |
| `STORAGE_EXPORT_DIR` | `storage/exports` | Directory for generated Markdown and OKF exports |
| `METADATA_STORE_TYPE` | `file` | Metadata store backend (`file`; `s3`/`athena` planned) |
| `LITELLM_MODEL` | `gpt-4o-mini` | LiteLLM model for AI documentation |
| `LITELLM_API_KEY` | *(none)* | Optional provider API key override |
| `LITELLM_API_BASE` | *(none)* | Optional custom LiteLLM API base URL |

DB activation vars — see [root README](../README.md#db-configuration--activation).

---

## Installation

The core package is installed in editable mode by the API and Worker:

```bash
pip install -e /path/to/core
# or via requirements.txt:
pip install -r requirements.txt
```

---

## Adding a New Connector

1. Create `core/db_connector/connectors/mydb.py` implementing `BaseConnector`.
2. Export it from `core/db_connector/connectors/__init__.py`.
3. Add its activation env var and config block to `core/db_connector/configurations.py`.

The `ConnectorManager` automatically discovers all classes that extend `BaseConnector`.
