Metadata-Version: 2.4
Name: solunex-ssdo
Version: 0.1.0
Summary: SSDO, the Smart Data Organizer: understands a database's structure (never its data) and says what it knows, what it infers, and how sure it is.
Author: Solunex Technologies
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: fastapi>=0.110
Requires-Dist: uvicorn>=0.29
Requires-Dist: pydantic>=2.6
Requires-Dist: SQLAlchemy>=2.0
Requires-Dist: alembic>=1.13
Requires-Dist: PyMySQL>=1.1
Requires-Dist: psycopg[binary]>=3.1
Provides-Extra: dev
Requires-Dist: pytest; extra == "dev"
Requires-Dist: httpx; extra == "dev"

# SSDO — Smart Data Organizer (Solunex Intelligence)

SSDO profiles a MySQL or PostgreSQL database's **structure** (`information_schema`
only — no data rows are read) and returns an explainable understanding of it:
relationships (declared and inferred, each with confidence and evidence), table
roles, conventions, and what it honestly could not determine.

## Run SSDO on your own machine

Use this to profile databases on your laptop or office network (`localhost`) in
development mode, before anything is hosted. It is the same SSDO: structure only, never
your data. The console opens at http://127.0.0.1:8000 and only this machine can reach it.

**With Python (3.10+):**

```bash
pip install "git+https://github.com/saniabduljabbar619-create/solunex-intelligence.git"
ssdo                          # starts SSDO and opens the console in your browser
ssdo --port 9000 --no-browser # another port, no browser
ssdo --version
```

SSDO keeps its saved runs in your user data folder (Windows
`%LOCALAPPDATA%\Solunex\SSDO`, macOS `~/Library/Application Support/Solunex SSDO`,
Linux `~/.local/share/solunex-ssdo`); `SSDO_DB_URL` overrides it.

**With Docker:**

```bash
docker build -t solunex-ssdo .
docker run --rm -p 127.0.0.1:8000:8000 -v ssdo-data:/data solunex-ssdo
```

- Keep the `127.0.0.1:` in `-p` so only your machine can open the console.
- Saved runs live in the `ssdo-data` volume.
- To profile a database running on the same computer, use the host
  **`host.docker.internal`** instead of `localhost`. On Linux, add
  `--add-host=host.docker.internal:host-gateway` to `docker run`.
- Tagged releases (`vX.Y.Z`) also publish the image to
  `ghcr.io/saniabduljabbar619-create/solunex-ssdo`.

## Setup (to work on SSDO itself)

```bash
python -m venv .venv
.venv\Scripts\activate          # Windows  (macOS/Linux: source .venv/bin/activate)
pip install -r requirements-dev.txt
```

## Run the API

```bash
uvicorn ssdo.api.main:app --reload
```

Open http://127.0.0.1:8000 for the console, or http://127.0.0.1:8000/docs for Swagger.
The console has four views of every run: **Show technical detail**, **Explain simply**
(plain language), **Teach me** (a six-slide guided walkthrough, also available as
`GET /v1/understandings/{run_id}/walkthrough`) and **Changes** (drift since an earlier
run, also `GET /v1/compare` and `GET /v1/understandings/{run_id}/changes`).

Above the views, **Ask SSDO** answers questions about the run's structure in plain
words, such as *"what links to patients?"*, *"how are payments and tags connected?"* or
*"where is email?"*. It uses fixed templates, no language model and no data access. Every
answer says how it read the question and how sure SSDO is. Questions about the data
itself ("how many patients?") are declined with the reason. It is also available as
`POST /v1/understandings/{run_id}/ask`; see `docs/specs/tier2-ask.md`. It also answers
*"what changed since the last run?"* and *"what is a junction table?"*, suggests questions
suited to the portal mode, and in Study mode defines each technical word it uses.

The left panel holds the **mode** (Development opens runs in the technical view, Study in
Teach me, Enterprise in Explain simply; every view stays reachable from every mode), an
opt-in **Keep this session** (off by default; remembers the mode and the last 3
connections on this device, never the password, user or API key), and your **run
history** with a Compare button for any database profiled more than once.

Authenticate with the `X-API-Key` header. With no keys configured the server runs in
dev mode and accepts `dev-local-key`; for real use set
`SSDO_API_KEYS="key1:tenant-a,key2:tenant-b"`.

SSDO stores its own results in `ssdo_platform.db` (SQLite) by default; set
`SSDO_DB_URL` to use another database. This file is local and not committed.

## Production settings

| Variable | Purpose | Default |
|---|---|---|
| `SSDO_ENV` | `production` refuses to start without API keys and tightens the defaults below | `development` |
| `SSDO_API_KEYS` | `key:tenant` pairs, comma-separated | none (dev key in development) |
| `SSDO_ALLOWED_DB_HOSTS` | Private/loopback hosts, IPs or CIDRs `/v1/profile` may reach in production, e.g. `db.internal,10.20.0.0/16` | none |
| `SSDO_CORS_ORIGINS` | Allowed browser origins | `*` in development, none in production |
| `SSDO_PROFILE_RATE_LIMIT` | Profiling calls per API key, `calls/seconds`; `0` disables | `10/60` |
| `SSDO_DB_URL` | SSDO's own store | `sqlite:///ssdo_platform.db` |

Link-local addresses (including the cloud metadata address 169.254.169.254) are refused
in every mode. See `ssdo/api/security.py` for the reasoning.

## SSDO's own store and migrations

The store's schema is managed by Alembic (`ssdo/db/migrations`). **You normally run
nothing:** the API and scripts upgrade the store automatically on startup, including
stores created before migrations existed. Your runs are kept.

Everyday commands (safe any time):

```bash
python -m alembic current        # which version the store is at
python -m alembic upgrade head   # upgrade now instead of waiting for startup
```

**Only when you change a table in `ssdo/db/models.py`**, draft a migration for it:

```bash
python -m alembic revision --autogenerate -m "add xyz to runs"   # then review the file
```

If no model changed, this writes no file. `tests/db/test_migrations.py` fails if the
models and migrations ever disagree.

## Encrypted connections (TLS)

Every profile request chooses how the connection to the database is protected (console:
**Encryption (TLS)**; API: `tls` and `tls_ca`; command line: `--tls`, `--tls-ca`). The same
five modes apply to MySQL and PostgreSQL:

| `tls` | Encrypted | Server certificate checked | Use it for |
|---|---|---|---|
| `off` | never | — | a local database without TLS |
| `prefer` *(default)* | if the server offers it | no | everyday use; falls back to plain |
| `require` | always | no | stops eavesdropping, **not** impersonation |
| `verify-ca` | always | signed by *your* CA (`tls_ca` required) | provider CA, IP address as host |
| `verify-full` | always | signed by a trusted CA **and** names the host | hosted databases, production |

Hosted databases (Aiven, DigitalOcean, Azure, …) usually require TLS: download the
provider's **CA certificate** and paste it into the console with `verify-full`. Without a
certificate, `verify-full` trusts the public certificate authorities. The certificate is
used for one connection and never stored. Failures say what to change, e.g. *"The
server's TLS certificate does not name this host."*

## Command line

```bash
python -m scripts.profile_database --database mydb --user readonly --password ... --save
python -m scripts.profile_database --host db.example.com --database mydb --user readonly \
    --password ... --tls verify-full --tls-ca ca.pem
python -m scripts.understanding_history --tenant tenant-mydb
python -m scripts.compare_runs --tenant tenant-mydb
```

Reports are written to `reports/` (local only, not committed).

## Deploy on Render

`render.yaml` is a Render Blueprint. It creates the web service (API and console) and
the PostgreSQL database SSDO stores its runs in. A Render service's disk is wiped on
every deploy, so the store must be Postgres, not SQLite.

1. **Create the services.** In Render: **New → Blueprint**, pick this repository, and
   apply. Render builds with `pip install -r requirements.txt`, creates the database
   and wires `SSDO_DB_URL` to it.
2. **Let people in.** On the `solunex-ssdo` service → **Environment**, set
   `SSDO_ADMIN_EMAILS` (and/or `SSDO_API_KEYS`). The service will not start in
   production with neither.
   - **Accounts (recommended):** `SSDO_ADMIN_EMAILS=you@example.com` (comma-separated
     for more admins). An admin signs in → **Invites** → **Generate code**, once per
     person. A code works **once**, expires (1 to 30 days), can be revoked, and SSDO stores
     only its fingerprint. **Every new account gets its own private workspace**: nobody
     sees another person's runs. Only a **team** invite, chosen on purpose by an admin,
     lets someone into the admin's workspace and its runs. The console shows every
     person whether their workspace is private or shared, and with how many people.
   - **Legacy static codes:** `SSDO_INVITE_CODES=code:tenant,...` still works so nobody is
     locked out, but each sign-up now gets a private workspace (the tenant part is
     ignored). Remove it once admins hand out generated codes.
   - **Operator keys (optional):** `SSDO_API_KEYS=key:tenant,...` for servers or scripts
     you manage yourself. Every entry must have a `:tenant` part, or the service
     refuses to start and says which entry is wrong.
   Generate a strong operator key with:
   ```bash
   python -c "import secrets; print(secrets.token_urlsafe(24))"
   ```
   The tenant always comes from the credential, never from the request.
3. **Open it.** Open the service's `https://….onrender.com` URL, then **Create account**
   with a generated invite code (or paste an operator key). The store's tables are created
   and upgraded automatically on start.

**Which databases can be profiled from Render.** Only databases reachable from the
internet, such as cloud or hosted databases. A database on someone's laptop
(`localhost`) or office network cannot be reached from Render, and SSDO refuses
private addresses in production anyway. If the database has a firewall, allow
Render's outbound IP addresses (listed on the service's page in Render). Always use a
**read-only** database user.

**Free plan.** The free web service sleeps when idle, so the first request after a
break takes a while. Render's free PostgreSQL is time-limited. Check Render's current
terms, and move the database to a paid plan before relying on the history.

## The live workspace: watch your database while you code

In Development mode, the **Live** page follows a database while you work on it. Run this
next to your code (it asks for the database password once and keeps it on your machine):

```bash
SSDO_API_KEY=<your API key> ssdo watch --engine postgres --database myapp_dev --user dev \
    --server https://solunex-ssdo.onrender.com
```

Every few seconds it reads the database's **structure** (never a row) and sends it to SSDO
**only when it changed**. Each change appears on the Live page and in the terminal: a table
added, a column's type changed, a link removed. Each is marked Fact or Inferred. SSDO never
connects to your database, which is why this works for `localhost` databases. See
`docs/specs/live-workspace.md`.

**A watch is private to whoever starts it**, even in a team workspace. Teammates see neither
the watch nor the runs it saves. Runs started in the console stay shared with the workspace.
**Live rooms** share a watch with chosen teammates (`docs/specs/team-live-workspace.md`):
a room lives inside one team workspace; its owner adds a teammate, who must accept; only a
watch's owner shares it in, from the console or with `ssdo watch … --room "Payments sprint"`.
Members see one merged feed ("seen on aisha@…'s `clinic_dev`"), each watch's runs, and
"compare with a teammate"; never a watch's host and port. Unsharing, leaving or closing the
room ends access at once. In the console: Live → **Rooms** (invitations, the room's
feed, compare); Live → **My watches** to share or stop sharing a watch. API:
`/v1/live/rooms…` and `POST /v1/watches/{id}/share`.

**Apply a change from your own terminal** (`docs/specs/build-together.md` §7.2). On one of your
own watches, open **Change this database**, write a structure change (ALTER, CREATE, DROP,
RENAME: never rows), press **Explain first**, then **Approve for my terminal**. Nothing runs
on the server: start the watcher with `--allow-apply`,

```bash
SSDO_API_KEY=<your API key> ssdo watch --engine mysql --database shop_dev --user dev \
    --server https://solunex-ssdo.onrender.com --allow-apply
```

and it shows the approved change in your terminal, checks it again against the structure it
just read, and runs it with its own connection **only after you type `yes`**. Postgres runs
the whole change in one transaction; MySQL commits each statement, and says how many ran if
one fails. The console records what your terminal answered (applied, failed with the error's
code only, or declined), and the feed marks the change "Applied from a draft". Only a watch's
owner can approve, an approval waits 24 hours, and without a terminal nothing ever runs.

## Explain a migration: `ssdo snapshot` and `ssdo diff`

Before a change ships, see what it does to the database's structure. Take a snapshot,
apply your migrations with your own tool, take another, and compare:

```bash
export SSDO_DB_PASSWORD=...          # read once, never written to the snapshot
ssdo snapshot --engine postgres --database myapp_dev --user dev --out before.json --label main
alembic upgrade head                 # or: python manage.py migrate, npx prisma migrate deploy, ...
ssdo snapshot --engine postgres --database myapp_dev --user dev --out after.json --label my-branch
ssdo diff before.json after.json     # --format markdown | json, --out FILE
```

`ssdo diff` is fully offline (no server, no API key, no network). It lists every change as
Fact or Inferred, what pointed at whatever was removed or retyped, and **needs-care notes**:
values that a removed column discards, a narrower type, a NOT NULL that fails on existing
rows, a link that is no longer declared, a changed key, a removed index. Each note says
what SSDO cannot know, because it never reads data. SSDO never runs a migration.

It never fails by default. A team can choose to: `--fail-on can_lose_values,narrower_type`
(or `any`) exits 1 when the change has those kinds of notes. The kinds are
`can_lose_values`, `can_fail_on_rows`, `narrower_type`, `type_conversion`,
`breaks_declared_link`, `breaks_inferred_link`, `key_changed`, `index_removed`,
`possible_rename`. In CI, `--format markdown --context ci` gives the pull-request comment.
See `docs/specs/ci-schema-diff.md`.

### SSDO in CI: a comment on every pull request

Add this workflow to your repository. On each pull request it builds a throwaway database
with **your** migrations, first at the base and then with the pull request applied on top
(the path production takes), and posts one comment that it updates on every push:

```yaml
# .github/workflows/schema-diff.yml
name: schema diff
on: pull_request
permissions: { contents: read, pull-requests: write }
jobs:
  schema:
    runs-on: ubuntu-latest
    services:
      db:
        image: postgres:16
        env: { POSTGRES_PASSWORD: postgres, POSTGRES_DB: app }
        ports: ["5432:5432"]
        options: --health-cmd pg_isready --health-interval 2s --health-retries 30
    steps:
      - uses: saniabduljabbar619-create/solunex-intelligence/.github/actions/schema-diff@v1
        with:
          engine: postgres
          database: app
          user: postgres
          password: postgres            # a throwaway database, not a secret
          setup: pip install -r requirements.txt
          migrate: alembic upgrade head
          # fail-on: can_lose_values,narrower_type   (optional; off by default)
```

- **Your migrate command** sees `DATABASE_URL`, `SSDO_DB_HOST`/`_PORT`/`_NAME`/`_USER`/`_PASSWORD`,
  and `SSDO_SIDE` (`base` or `head`). Examples: `python manage.py migrate`,
  `npx prisma migrate deploy`, `flyway migrate`, or `psql "$DATABASE_URL" -f schema.sql`.
- **MySQL:** use a `mysql:8.4` service (`MYSQL_ROOT_PASSWORD`, `MYSQL_DATABASE`) and `engine: mysql`.
- **Pull requests from forks** get a read-only token, so GitHub refuses the comment. The
  result is always in the job summary too.
- **Outputs:** `has-change`, `care-count`, `markdown-file` and `json-file`, for your own steps.
- **Nothing leaves the job:** SSDO is installed from the action itself and runs offline.
- **Versions:** `@v1` follows every compatible update of the action. Pin a full commit SHA
  instead if your team prefers to review each update.

**Other CI systems** use the two commands directly. A GitLab example:

```yaml
schema-diff:
  image: python:3.12
  services: [{ name: postgres:16, alias: db }]
  variables: { POSTGRES_PASSWORD: postgres, POSTGRES_DB: app, SSDO_DB_PASSWORD: postgres,
               DATABASE_URL: "postgresql://postgres:postgres@db:5432/app" }
  rules: [{ if: $CI_PIPELINE_SOURCE == "merge_request_event" }]
  script:
    - pip install "git+https://github.com/saniabduljabbar619-create/solunex-intelligence" -r requirements.txt
    - git fetch origin $CI_MERGE_REQUEST_TARGET_BRANCH_NAME
    - git checkout FETCH_HEAD && alembic upgrade head
    - ssdo snapshot --engine postgres --host db --database app --user postgres --out base.json --label base
    - git checkout $CI_COMMIT_SHA && alembic upgrade head
    - ssdo snapshot --engine postgres --host db --database app --user postgres --out head.json --label head
    - ssdo diff base.json head.json --format markdown --context ci | tee schema-diff.md
  artifacts: { paths: [schema-diff.md] }
```

## Feedback and the Admin page

- **Message the Solunex team** (bottom of the left panel) sends an idea, a problem, a
  question or a pricing question to the **people** at Solunex. It is deliberately styled
  unlike Ask SSDO, and says so: a person reads it. Unlike an Ask question it is stored, so
  it can be answered; it is never written to the server log. Five messages an hour per
  workspace.
- **Admin** (admins only, `SSDO_ADMIN_EMAILS`): sign-ups and runs per day, totals, live
  watches, the latest sign-ups, and the message inbox (mark read, done, reply by email).
  **Counts only**: admins never see another workspace's databases, tables or runs.
- **Be told when a message arrives** (optional, set on Render → Environment):
  - Telegram: create a bot with @BotFather, then set `SSDO_NOTIFY_TELEGRAM_TOKEN` (the bot
    token) and `SSDO_NOTIFY_TELEGRAM_CHAT` (your chat id: message the bot once, then open
    `https://api.telegram.org/bot<token>/getUpdates` and copy `chat.id`).
  - Email: `SSDO_NOTIFY_EMAIL_TO`, `SSDO_SMTP_HOST`, `SSDO_SMTP_PORT` (587), `SSDO_SMTP_USER`,
    `SSDO_SMTP_PASSWORD` (for Gmail, an app password), optionally `SSDO_SMTP_FROM`.
  With neither set, messages still arrive on the Admin page.

## Accounts and API keys

- **Sign in** in the left panel. The console then authenticates with your session: no
  key to paste. The session lives in this browser tab only, unless **Keep this
  session** is on; it expires after 14 days, and **Sign out** ends it on the server.
- **API keys** (signed in → *API keys*) are for scripts and tools: send one as the
  `X-API-Key` header. A key is shown **once** when created. SSDO stores only its SHA-256
  fingerprint, so a lost key is revoked and replaced, never recovered. Keys cannot
  create or revoke other keys; only a signed-in person can.
- **Passwords** are stored only as salted scrypt hashes. Sign-in and sign-up are
  rate-limited, and an unknown email and a wrong password get the same answer.
- **Sign out** forgets this person in the browser: the remembered connections are cleared
  and the page reloads, so nothing of theirs is left for the next person. Remembered
  connections belong to one account; a different account signing in never sees them.
- **Local development** with no `SSDO_ADMIN_EMAILS` and no `SSDO_INVITE_CODES`: sign-up
  is open (each account private), and the dev key `dev-local-key` still works.

## Tests

```bash
python -m pytest
```

The Postgres profiler also has live tests against a real server (skipped by default).
They create a throwaway database, profile it as a read-only user, and drop it:

```bash
SSDO_TEST_PG_ADMIN="host=127.0.0.1 user=postgres password=..." python -m pytest tests/profiler
```

The TLS modes have live tests too, against TLS-enabled servers whose certificate names
`localhost` and is signed by the CA at `ca=` (see `tests/profiler/test_tls.py`):

```bash
SSDO_TEST_MYSQL_TLS="host=localhost port=3306 user=ro password=... database=shop ca=ca.pem" \
SSDO_TEST_PG_TLS="host=localhost port=5432 user=ro password=... database=postgres ca=ca.pem" \
SSDO_TEST_MYSQL_NOTLS="host=127.0.0.1 port=3308 user=ro password=... database=shop" \
python -m pytest tests/profiler/test_tls.py
```
