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+):

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:

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)

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

Run the API

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):

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:

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

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:

   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):

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,

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:

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:

# .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:

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

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:

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):

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