Metadata-Version: 2.3
Name: nc_db
Version: 0.1.4
Summary: A PostgresSql Database project with a fully generic infrastructure and design.
License: Proprietary
Author: Colt Hale
Author-email: colt@pixelthin.com
Requires-Python: >=3.12,<4.0
Classifier: License :: Other/Proprietary License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Dist: psycopg-pool (>=3.2.6,<4.0.0)
Requires-Dist: psycopg[binary] (>=3.2.9,<4.0.0)
Requires-Dist: pydantic (>=2.11.7,<3.0.0)
Description-Content-Type: text/markdown

# nc_db
PostgreSQL schema, lightweight Python DB client, tests, and Kubernetes manifests for the Neural Context project.

This repository provides:
- A thin, typed Python client for JSONB-first tables (insert/update/select/delete) under a configurable schema.
- Test suite that spins up isolated schemas to validate behavior and edge cases.
- Kubernetes manifests (Kustomize) to run Postgres locally or in-cluster, plus a safe reset script.

## Contents

```
src/db/           # Python client: Db, DbConfig, SQL helpers
src/tests/        # Pytest suite and fixtures (isolated schemas)
k8s/              # K8s StatefulSet, init SQL, and ops scripts
pyproject.toml    # Poetry + pytest configuration
.github/          # Issue and PR templates
README.md         # This file (schema diagram below)
```

## Prerequisites

- Python 3.12+
- PostgreSQL 15+ available locally, or run via the provided K8s manifests

Default development credentials used in examples/tests:
`host=localhost, port=5432, db=nc-data, user=nc_admin, password=bob`.

## Quick Start

1) Install dependencies with Poetry:

```bash
poetry install
```

2) Ensure Postgres is reachable using the defaults above. You can run it via Kubernetes (see below) or your local Postgres.

3) Smoke test the Python client:

```bash
poetry run python -c "from src.db.db import Db; print(Db().get('scg', ids=[]))"
```

## Running Tests

```bash
kubectl apply -k k8s/postgres/overlays/ephemeral
kubectl -n nc_db wait --for=condition=ready pod -l app=nc-data --timeout=90s
kubectl -n nc_db port-forward svc/postgres 5432:5432
```

```bash
poetry run pytest -q
```

- To run a single test: `poetry run pytest -k test_update_merge -q`
- Tests create per-test schemas and tables; they do not require pre-existing app tables.

## Python Client Overview

The client favors tables shaped as `(id BIGSERIAL PRIMARY KEY, body JSONB NOT NULL)` and enforces `body.id == id` on writes/reads.

Basic usage:

```python
from nc_db.db.db import Db
from nc_db.db.DbConfig import DbConfig

db = Db(schema="nc", config=DbConfig(
    user="my_user", password="my_password", host="localhost", port=5432, dbname="my_database"
))

# Insert with or without explicit id
id1 = db.insert("scg", {"body": {"name": "alpha"}})
id2 = db.insert("scg", {"body": {"id": 42, "name": "explicit"}})

# Update (merge JSON into existing body or replace entirely)
db.update("scg", ids=[id1], items=[{"body": {"name": "alpha-2"}}], mode="merge")

# Read (optionally map rows to models)
rows = db.get("scg")  # [{"id": 1, "body": {...}}, ...]

# Delete
db.delete("scg", [id2])
```

Key features:
- Insert trusts a provided id if present; otherwise uses the table sequence and normalizes `body.id`.
- Update supports `mode="merge"` (default) and `mode="replace"`, always ensuring `body.id` matches the row id.
- Get returns rows as dictionaries or via a mapper function to your own model types.
- Delete single or all rows, plus a helper to `TRUNCATE ... RESTART IDENTITY`.

## Configuration

Use `DbConfig` or environment variables to control connection pooling and DSN; defaults are suitable for local development.

```python
from nc_db.db.DbConfig import DbConfig

config = DbConfig(
    user="your_username",
    password="your_password",
    host="localhost",
    port=5432,
    dbname="your_database",
)
```
OR

```bash
export PGUSER="your_username"
export PGPASSWORD="your_password"
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE="youd_database"

```

## Kubernetes (Optional)

Run a local Postgres via Kustomize overlays.

Ephemeral (emptyDir) instance:

```bash
kubectl apply -k k8s/postgres/overlays/ephemeral
kubectl -n nc_db wait --for=condition=ready pod -l app=nc-data --timeout=90s
kubectl -n nc_db port-forward svc/postgres 5432:5432
```

Persistent storage overlay is also available under `k8s/postgres/overlays/persistent`.

To safely reset a StatefulSet (DANGEROUS — wipes data), use:

```bash
python k8s/scripts/full_pod_reset.py --yes
```

Review the script before use; it scales down, deletes the PVC (and optionally PV), and scales back up.

## Development Conventions

- Python 3.12, type hints, PEP 8; keep imports sorted and code minimal.
- Modules: snake_case; Classes: CamelCase; variables/functions: snake_case.
- Tests live in `src/tests`; name files/functions `test_*` and focus on behavior.
- See `.github/PULL_REQUEST_TEMPLATE.md` for PR expectations.

## Schema Diagram

Note: the init SQL currently provisions `scg` and `healthcheck`

## Security & License

- Do not commit real secrets. The defaults in code and K8s manifests are for local development only.
- Use `DbConfig` or environment variables to specify connection details.
- License: Proprietary (see `pyproject.toml`).

