CodeShare Private notes
Sign in Create account

Data model

CURRENT (MySQL pastes_db)

pastesv2_users

Account mirror of EE/Google/local login. Columns include username, email, display_name, password_hash, is_active, is_admin, ee_holdings_user_id, google_sub.

pastesv2_pastes

One blob per paste: public_id, access_key, title, content LONGTEXT, visibility enum public/unlisted/private, category, views, user_id, api_key_id, source (web|api|command), external_id, timestamps.

Unique (api_key_id, external_id) for idempotent notes. This is not a version chain. Updating content in place would break reproducibility; do not do that once experiments exist.

pastesv2_api_keys

Named keys: name, key_prefix, key_hash CHAR(64), is_active, last_used_at, request_count, revoked_at.

pastesv2_api_rate

Per-key per-minute counters.

Rejected (already): storing the raw pvsk_ in Command as plaintext. Command stores key_enc (AES-256-GCM) in commandprojects_db.cp_pastes_bindings.

PLANNED relational schema

PostgreSQL is the target for experiment OLTP if MySQL JSON and immutability become awkward. Equivalent MySQL is acceptable for Phase 2.

Core tables (proposed):

  • users, organizations, projects -- tenancy (EE already has companies; map, do not fork identity)
  • pastes -- logical artifact
  • paste_versions -- immutable body + content_hash + parent_version_id
  • experiments -- objective, weights JSON, project_id
  • experiment_runs -- experiment_id, version_id, model_version_id, prompt_version_id, params JSON, cost_cents, latency_ms
  • models, model_versions -- provider, name, vendor_version
  • prompts, prompt_versions -- system + user templates, hash
  • evaluations -- run_id, evaluator_type (human|llm|vision|rule|lighthouse|test)
  • evaluation_metrics -- name, value, weight_bps
  • ratings -- user_id, scale, winner_run_id, comment
  • assets -- run_id, kind, object_key, sha256, mime (bytes in S3)
  • deployments -- project_id, version_id, environment
  • datasets, dataset_items -- freeze inputs for benches
  • feedback -- production outcome after deploy
  • tags -- polymorphic
  • api_keys, webhooks, audit_logs

Relationships

Project 1-N pastes. Paste 1-N versions. Experiment N-N versions via runs. Run 1-N evaluations and assets. Deployment points at one version. Winner is a row on experiment (winner_run_id), not a deleted loser.

Immutability

Scored versions are frozen. Corrections create a new version. Audit log records promote, revoke, delete.

Object storage

Screenshots, video, large JSON go to S3-compatible storage. Postgres/MySQL hold keys and hashes only.

See diagrams/data-model.mmd.