Skip to content

SQLiteStackStorageDesign.md

Latest commit

 

History

History
217 lines (174 loc) · 8.72 KB

SQLiteStackStorageDesign.md

File metadata and controls

217 lines (174 loc) · 8.72 KB
Raw

SQLite Stack Storage

Hype stores .hype documents as self-contained packages backed by SQLite.

Example.hype/
  manifest.json
  stack.sqlite

manifest.json identifies the package format, SQLite schema version, document model version, and SHA-256 checksum of stack.sqlite. stack.sqlite is the canonical store for stack content.

Goals

  • Keep stack files self-contained and portable.
  • Store layout, scripts, object content, SpriteKit scenes, assets, themes, paint layers, and AI context inside SQLite tables.
  • Keep runtime code working with value models (HypeDocument, Stack, Card, Part, SpriteAreaSpec, SceneSpec) instead of live database rows.
  • Provide fast indexed search through SQLite FTS5.
  • Make corrupted or suspicious stacks diagnosable with ordinary SQLite tools.

Architecture

HypeSQLiteStackStore owns package read/write, schema creation, round-trip mapping, FTS indexing, and diagnostics.

The current runtime boundary remains:

SQLite package <-> HypeSQLiteStackStore <-> HypeDocument value graph <-> StackRuntime/UI

This avoids leaking SQLite handles, managed objects, AppKit, SpriteKit, SceneKit, AVFoundation, AudioKit, or network objects into the persistent model.

Schema Strategy

The schema uses normalized, queryable tables for the core Hype taxonomy:

  • document_values
  • stacks
  • deployment_targets
  • runtime_ai_settings
  • backgrounds
  • cards
  • parts
  • scripts
  • assets
  • music_patterns
  • music_tracks
  • music_notes
  • apple_music_items
  • apple_music_queues
  • ai_context_sources
  • ai_context_items
  • themes
  • paint_layers
  • constraints
  • sprite_areas
  • scenes
  • scene_nodes
  • search_fts

Rows also carry payload_json for exact value-model reconstruction. This is an intentional bridge: high-value query fields are relational and indexed now, while sparse type-specific fields remain lossless without prematurely exploding the schema for every part subtype.

The assets table projects the primary asset payload into data, byte_count, sha256, dimensions, tags, and kind for quick repository queries. Compound asset details live in the payload_json value model: Asset.files stores related embedded media files such as textures, skeletons, animations, palettes, previews, and metadata files, while Asset.metadata stores JSON/text/scalar metadata records. Loading reconstructs assets from payload_json, so adding these optional fields does not require a schema version bump; normalized projections can be added later if compound-file search or validation needs first-class SQL tables.

Asset compilation links also live in assets.payload_json. Asset.compilation connects author/source assets to compiler-generated runtime assets through stable AssetRef values plus compiler identity, operation, fingerprints, timestamp, and diagnostics. The current normalized assets columns still index the primary payload only; future schema versions can project compilation links into SQL when validation needs to detect stale runtime outputs or missing source assets without decoding payload JSON.

AI context rows project each embedded source and item into ai_context_sources and ai_context_items, while the original value-model payload remains in payload_json. Text summaries and chunks are indexed into search_fts as ai_context_item rows so attached rules, examples, and project-memory notes are diagnosable through SQLite search. Current user-facing imports are embedded snapshots; referenced/bookmark-backed context is reserved for a future refresh workflow and should not be assumed by storage migrations. Import diagnostics and secret-risk findings are transient UI/tool feedback, not persisted stack data.

Schema version 2 projects embedded audio recorder content into parts.audio_data as a SQLite BLOB. The runtime Part.audioData field is restored from that column on load, and the JSON payload intentionally omits the audio bytes to avoid storing the same recording twice.

Schema version 3 projects stack-contained AudioKit music into music_patterns, music_tracks, and music_notes. HypeDocument.musicLibrary remains the source of truth for runtime code; the relational rows make patterns searchable, diagnosable, and portable without storing live AudioKit engine state.

Schema version 4 projects Apple Music references into apple_music_items and apple_music_queues. Those rows persist stable MusicKit/catalog identifiers and metadata snapshots only; licensed catalog/library audio is never embedded in the package.

Schema version 5 projects target-platform planning into deployment_targets. Stack.deploymentTargets remains the value-model source of truth, while the rows make target selection, primary target, profile metadata, and layout policy diagnosable without decoding the stack payload.

Schema version 6 projects deployed-runtime AI policy into runtime_ai_settings. Stack.runtimeAISettings remains the value-model source of truth; the table exists for diagnostics, target-runtime export checks, and search/reporting tools.

Schema version 7 keeps the v6 relational shape and persists imported multi-stack project metadata as document_values.stackLibrary when HypeDocument.stackLibrary is non-empty. Older packages without that document value decode to an empty HypeStackLibrary.

Schema version 8 is the current writer version. It projects Stack.userLevel into stacks.user_level for quick diagnostics while keeping stacks.payload_json as the source of truth. Older packages without the userLevel payload field decode to Scripting level (5), preserving full authoring behavior.

Document Version Migrations

SQLite schema version and document model version are separate:

  • PRAGMA user_version / HypeSQLiteStackStore.schemaVersion tracks table shape.
  • HypeDocument.currentDocumentVersion tracks breaking changes to persisted value-model payloads and Codable keys.
  • manifest.json.documentVersion and document_values.documentVersion record the document model version written by the saving build.
  • Non-breaking optional document metadata such as legacyImport and stackLibrary is stored in document_values with default-on-missing decode behavior and does not require a document-version bump. stackLibrary may include optional imported stack-level script text for used-stack pass-up dispatch, related .hype document paths, and legacy card references with converted card UUIDs for cross-stack navigation lookup.

Loads, searches, and validation first verify the manifest checksum against the source database, then copy stack.sqlite to a temporary location and run incremental migration hooks before decoding payload JSON. This keeps older user documents openable without mutating the source package; the package is only rewritten when the user saves it.

When making a breaking document change:

  1. Bump HypeDocument.currentDocumentVersion.
  2. Add a migration hook in HypeSQLiteStackStore from the previous version to the new version.
  3. Update HypeSQLiteManifest/document_values tests for the version.
  4. Add a regression fixture or synthetic downgraded package test proving older documents load to the new model.
  5. Document the migration in architecture.md, decisions.md, and this file.

Document version 2 renamed the stack-local repository model from spriteRepository to assetRepository; the v1->v2 hook rewrites persisted JSON keys before any model decoding occurs.

Search

search_fts indexes:

  • stack/card/background/part names and scripts
  • part text, help, menu, popup, URL, and search fields
  • SpriteKit scene/node names, label text, and scripts
  • asset names, tags, and provenance
  • music pattern names, instruments, notes, and tempo
  • Apple Music item titles/artists/albums and queue contents
  • AI context summaries and text chunks

Search is derived data. If it drifts, it can be rebuilt from the relational tables and payload rows.

Diagnostics

The store exposes validate(packageURL:), which runs:

  • PRAGMA integrity_check
  • PRAGMA foreign_key_check
  • key table counts
  • missing SpriteKit asset reference checks
  • FTS entry counts

The database also defines diagnostic views:

  • v_card_layout
  • v_object_scripts
  • v_missing_asset_refs
  • v_music_patterns
  • v_apple_music_items
  • v_deployment_targets
  • v_runtime_ai_settings

Save And Recovery

Normal document saves and recovery snapshots both use SQLite packages. The in-memory undo/coalescing path still uses deterministic JSON snapshots for equality only; those snapshots are not the .hype file format.

SQLite WAL is enabled while writing, checkpointed, and then reset to DELETE journal mode before the package is finalized so the package remains self-contained. Read, search, and validation open the database read-only and do not create WAL/SHM sidecars.