Mission Vishwakarma Vishwakarma Download Roadmap Pricing

Storage of Data

  1. Design goal

The system is a Desktop C++ CAD/CAM application for very large refinery and petrochemical complexes. Currently support Windows/DirectX12 only. In future, we will have Ubuntu/Vulkan, Android/Vulkan, Mac/Metal & iOS/Metal also. The storage model must handle:
plant → unit → area → structure / vessel / piping set / equipment → assemblies → parts → nut/bolt-level objects

The system must support:

  • large object count
  • multi-tab desktop editing
  • server-hosted collaboration
  • local .yyy virtual-server collaboration
  • read-only references
  • catalog files
  • vendor data integration
  • AutoSave
  • transaction-based sync
  • no per-object permission
  • compact RAM layout
  • stable disk layout
  1. Fundamental storage principles
    RAM layout is not disk layout, Runtime memory layout and persistent disk layout are separate. The existing code already follows this direction:

META_DATA separates temporary memoryID / memoryIDContainer from saved persistedId / persistedParentId. GeometryData.id uses the runtime memoryID, which is correct for rendering/selection, not durable engineering identity. Objects are allocated through the custom memory arena using a memoryGroupNo per tab. Optional64 is explicitly a compact RAM optimization: optional values are packed only when present, fixed and dynamic optionals are separated, and dynamic values use arena-managed byte references. This is excellent for runtime memory density, but it must not be persisted raw.

  • RAM object layout = C++ classes + Optional64 + custom arena
  • Persistent object data = Protobuf BLOB inside SQLite
  • Sync transport = FlatBuffers messages
  • GPU/render geometry = derived runtime/cache data, not engineering truth
  1. File types .yyy file: engineering truth, .yyy file is an engineering data file. It stores:
  • engineering objects
  • engineering hierarchy inside the file
  • object payloads
  • object versions
  • object lifecycle state
  • object tombstones
  • object-level sync metadata
  • external .yyy reference alias table
  • file identity
  • file integrity metadata
  • schema/catalog compatibility metadata
  • short-term transaction/change logs
  • compact permanent audit summaries

A .yyy file can be opened directly by the desktop application. A .yyy file has single access level: If a user/process has write access to the .yyy file/session, it can modify anything inside that .yyy file. No folder-level permission exists inside a .yyy. No per-object permission exists inside a .yyy. A .yyy file may reference other .yyy files as read-only references. A .yyy file can store project engineering data, reference/catalog/template data etc. No separate catalog only and data only files. It can also have reference read-only model data such as IFC, STEP etc. Some .yyy files are shipped with the application and serve as standard catalogs. All .yyy and .zzz file will have permanent / unremovable reference to these files. This is to ensure compact data representation for standardized items (ex: ISMB300). A .yyy file may contain internal engineering containers such as: These are engineering objects, not filesystem folders.

  • FOLDER
  • STRUCTURE
  • VESSEL
  • PIPING_SET
  • EQUIPMENT
  • PIPE_RACK
  • ASSEMBLY
  • PART
  • BOLT
  • NUT
  • PLATE
  • MEMBER

A .zzz file is a project/server administrative file. A .zzz file is opened only by the server version or server-hosting mode of the application. A .zzz file does not store engineering object payloads directly. Engineering data remains in .yyy files. A .zzz file does not store engineering object payloads directly. A .zzz may mount child .zzz projects, but the project graph must remain a tree/DAG without cycles. No circular project mounting is allowed. Engineering data remains in .yyy files. A .zzz server does not require graphics rendering capability. It stores:

  • project tree
  • folder hierarchy
  • mounted .yyy files
  • mounted child .zzz projects
  • folder-level permissions
  • file-level permissions
  • vendor package integration
  • effective permission rules
  • server collaboration policy
  • project-level audit metadata
  • project-level schema/catalog requirements
  1. Hosting and authority modes
    There are two collaboration modes.

3.1 Server-hosted .zzz mode. In this mode: central model server opens .zzz. .zzz references/mounts many .yyy files. clients connect to server. clients do not write .yyy files directly. server is the only persistent authority. Rules:

  • AutoSave is enabled.
  • All accepted changes are committed by the server.
  • Client RAM state is transient.
  • No local saved engineering copy exists.
  • No child/nested peer connection from client is allowed.

That last rule is important. If client opened model through server-hosted .zzz, that client may not act as a secondary server for other peers. This prevents authority ambiguity, permission bypass, and sync loops. So:

  • .zzz server → client Allowed.
  • .zzz server → client → peer client Not allowed.

3.2 Local .yyy virtual-server mode. In this mode: desktop client opens local .yyy file from disk. that desktop process becomes the virtual server/host. other network peers may connect/subscribe. the host process owns persistent writes to that local .yyy. Rules:

  • AutoSave is enabled.
  • Host process is the authority.
  • Peers do not write the .yyy file directly.
  • Peers send proposed transactions to host.
  • Host validates, commits, and publishes deltas.

This gives one collaboration code path: client proposes transaction, authority commits transaction, authority publishes accepted result. The authority may be: central .zzz server OR local .yyy virtual server. But there is always exactly one persistent writer.

  1. AutoSave behavior
    AutoSave is always enabled during connected editable sessions. There is no user-facing “save” operation for normal collaborative editing. A change becomes engineering-truth only after authority commit. User edits become:
  • local interactive change
  • proposed transaction
  • server/host validation
  • server/host commit
  • published accepted delta
  • client RAM confirmation/remap

Recommended UI states:

  • CONNECTED / SAVED
  • EDITING / AUTOSAVING
  • COMMIT IN FLIGHT
  • RECONNECTING
  • STALE - RELOAD REQUIRED
  • READ ONLY
  • PERMISSION DENIED

  1. Network interruption behavior For server-connected clients: RAM model remains open during short network interruption. Rendering, selection, viewing, and measurement may continue. Persistent engineering commits require reconnection. When disconnected: editing continues, with local collection of change logs (non-authoritative draft transactions) in CPU RAM only. When reconnected, changes are pushed to host server/peer. Host may accept all/partial commits. Editing is blocked after 24 Hours grace period. Since different clients may be subscribed to different sub folder, both may make independent changes to different engineering hierarchy even when disconnected, and successfully syncing latter.

  2. Permission model 6.1 .yyy permission rule: A .yyy has no internal permission hierarchy. No per-object permission. No internal folder permission. Only accidental modification lock flags may exist inside .yyy. These locks are implemented in application part of the code, not database layer.

6.2 .zzz permission rule: All real authorization belongs to .zzz / model server. Permissions may be inherited and overridden. Effective permissions are calculated by the server. Child .zzz permissions may be overridden by parent .zzz, subject to project policy. No per-object permissions. Permissions exist at: project root / folder / mounted .yyy file / mounted child .zzz

  1. Object identity system 7.1 Runtime ID: Properties:
  • 64-bit
  • monotonically increasing in process/session
  • unique across logically independent tabs
  • never persisted as durable identity
  • used for rendering, selection, RAM lookup, transient UI state

The code already follows this pattern by assigning memoryID at object construction and using it in geometry data.

7.2 Persistent local object ID: Persistent engineering identity inside one .yyy: local_object_id. Properties:

  • stored as INTEGER / uint64_t
  • 0 is invalid/null, and everything below 2^32 is invalid — the truncation guard
  • unique inside one .yyy file
  • assigned only by authority: server or local .yyy virtual-server host
  • monotonic within its band
  • not reused during active project life

id.md is the authority on the id space; this section defers to it. Amended 2026-08-26: the earlier “only lower 40 bits used” rule is withdrawn. Persistent ids use the full usable range and are banded:

Range Band Assigned by
[0, 2^32) permanently invalid nobody
[2^32, 2^40) application catalogue — items shipped with the binary only Mission Vishwakarma developers, random draw
[2^40, 2^41) local / provisional, pre-commit and offline the client, sequentially
[2^41, 2^42) reserved gap nobody
[2^42, 2^48) permanent the authority, sequentially
≥ 2^48 reserved, top 16 bits zero nobody

Two consequences for this document. A 40-bit CHECK constraint cannot hold a permanent id — see §14. And a company’s own catalog is not the catalogue band: company catalogue items are ordinary engineering objects in an ordinary .yyy, drawing ordinary local and permanent ids. The catalogue band is application-shipped data only.

Local-band ids are provisional. When the work reaches the authority it assigns permanent ids and the client updates its memory; references inside that file are rewritten in the same transaction. This is file-internal only, because nothing outside a file may name a provisional id (§7.3 rule below).

An id outside the band is silently dropped when the file is written. The writer zeroes it and assigns a fresh in-band one in the same pass; the object is kept, only the offending number is discarded. There is deliberately no migration path for a file written under an older id range — such a file keeps its own stored CHECK (CREATE TABLE IF NOT EXISTS does not replace it) and is discarded rather than repaired. id.md §4.1 carries both rules.

Do not recycle object IDs during normal operation. Reason: Recycling IDs can make old external references resolve to a wrong new object. Wrong object resolution is worse than missing/deleted resolution.

7.3 Object references — NOT packed. Amended 2026-08-26. The earlier packed 64-bit reference (8 reserved bits, 16-bit file_alias, 40-bit local_object_id) is withdrawn. Three reasons, any one sufficient: a .zzz project may reference more than 2^32 files, so any fixed alias width is a ceiling that will be hit; permanent ids run to 2^48 and do not fit in 40 bits (§7.2); and packing forces every object schema to understand aliasing.

What replaces it:

  • Same-file reference: the field holds a plain local_object_id. Nothing else.
  • Foreign reference: the field holds the local_object_id of a ForeignReference proxy object in this same file. The proxy is an ordinary object with an ordinary object_store row, and its Protobuf payload names the target as two separate uint64 varint fields — fileAlias and targetObjectId — plus an expectedType. No packing, so neither field has a width ceiling; varint keeps a small alias one or two bytes on disc.

So every reference field in every payload is one plain same-file id, and only the proxy payload understands aliasing. Ten objects referring to the same foreign target share one proxy.

Alias values keep only their two reserved meanings: file_alias = 0 :same .yyy file. On a proxy this means a repair proxy — a RAM-only stand-in the loader built for a same-file reference that failed to resolve, never written to disc (§14.4) file_alias = 1 :temporary same-file reference, RAM/transaction only, forbidden in committed persistent payload file_alias >= 2 : external file alias, resolved through external_file table. No upper bound. These are the authored proxies, and the only kind that is persisted

Rules: a foreign reference may target a mounted catalog .yyy, never a sibling project. A foreign reference may only target a permanent id — pointing a proxy at a provisional local-band id or an alias-1 temporary fossilizes a number that is about to change. One proxy per (fileAlias, targetObjectId) per file.

Important distinction: object_store.object_id stores only local object ID. The alias belongs to the referencing file, not the target object. Full design: id.md §5.

  1. External file references A .yyy may refer to other .yyy files. External file aliases are local to each .yyy. Example: In file A, catalog file C may be alias 7. In file B, the same catalog file C may be alias 12. Therefore aliases are not global. External reference resolution result must support:
  • resolved
  • missing
  • deleted
  • moved
  • replaced
  • permission_denied
  • file_not_loaded
  • file_alias_unknown
  • schema_unsupported
  • corrupt_payload
  1. Object lifecycle states Use enum-style lifecycle states, not a boolean tombstone.
  • 0 = live : Object exists normally.
  • 1 = soft_deleted : Object is hidden from normal model view but still has payload available. Useful for: undo, short-term recovery, review
  • 2 = tombstone_retained : Engineering payload may be removed or reduced. Identity and deletion metadata remain. Used to resolve external references to: deleted / replaced / moved
  • 3 = purged_stub_or_archive_boundary : Allowed only after explicit compaction/archive/reference-scan policy. Does not permit ID reuse unless the entire file is rewritten as a new identity lineage.
  1. Transaction, sync, and AutoSave model THIS SECTION IS NOT STREAMLINED TO SUPPORT DIFFERENT CLIENTS EDITING DIFFERENT INDEPENDENT FOLDERS. TO BE IMPLEMENTED LATTER. 10.1 Server/host is the commit authority. Clients never directly modify authoritative SQLite files in collaboration mode. They submit proposed transactions. Authority assigns: persistent object IDs, transaction_id, transaction_seq, change_seq, object_version, server_version

10.2 Sync ordering Use: change_seq, transaction_seq, object_version. Do not use wall-clock time for sync correctness. modified_time_utc is audit/display metadata only.

10.3 Conflict detection: Each client update should include: object_id, base_object_version, new_payload, operation_type. Authority checks: current_object_version == base_object_version.

  • If true: accept, increment object_version, assign change_seq, commit, publish delta
  • If false: reject / require reload / explicit merge

Do not initially implement automatic property-level merge for engineering objects unless the object type is known safe.

10.4 Subscriptions. Clients may subscribe to: entire .yyy file / folder subtree/ object set / object type / viewport/spatial region, later

Authority publishes:

  • transaction_id
  • transaction_seq
  • change_seq
  • changed object IDs
  • new object versions
  • deleted/tombstone IDs
  • affected parent IDs
  • reference/permission errors
  1. Transaction log retention THIS SECTION IS NOT STREAMLINED TO SUPPORT DIFFERENT CLIENTS EDITING DIFFERENT INDEPENDENT FOLDERS. TO BE IMPLEMENTED LATTER. Full transaction/change logs do not need to live forever. Recommended defaults:
  • local .yyy virtual-server mode: object_change_log retained 30 days
  • server-hosted .zzz mode: object_change_log retained 30-90 days, configurable
  • object_undo_log: 7-30 days, configurable
  • audit_summary: retained for project life or forever
  • tombstones: retained much longer than transaction logs

If reconnect base is older than retained change_seq window: FULL_RELOAD_REQUIRED Use checkpointing:

object_store = current truth sync_checkpoint = known full-state boundary object_change_log = recent delta path audit_summary = compact permanent trace

  1. Serialization policy 12.1 Persistence: Canonical engineering object payload: Protocol Buffer BLOB

Reason: stable schema evolution optional fields backward/forward compatibility unknown field behavior migration support long-term persistence 12.2 Transport

Low-latency sync packets:

FlatBuffers

Used for:

object changed packets transaction commit results subscription updates viewport updates temporary-to-persistent ID remap 12.3 RAM

Runtime:

C++ object layout Optional64 custom arena allocator in-memory indexes DirectX geometry cache

Optional64 is RAM-only; it supports fixed/dynamic optional properties with compact storage and generated schema/type accessors. It is not responsible for persistence and should not define the disk format.

  1. SQLite naming convention
  • Use: lower_snake_case Examples: object_id / parent_id / schema_version / payload_crc32c / last_writer_transaction_id
  • Avoid: ALL_CAPS table names / camelCase column names / cryptic abbreviations

Use object_store, not DATA.

  1. .yyy SQLite schema Below is the proposed complete .yyy schema. This is schema specification, not implementation code.

14.1 Pragmas: Recommended when creating/opening .yyy:

  • PRAGMA foreign_keys = ON;
  • PRAGMA journal_mode = WAL;
  • PRAGMA synchronous = NORMAL;
  • PRAGMA temp_store = MEMORY;

For server-authoritative writes requiring stronger durability, use:

  • PRAGMA synchronous = FULL;

14.2 file_info Stores file-level metadata. CREATE TABLE file_info ( key TEXT PRIMARY KEY, value BLOB NOT NULL );

Required keys:

  • file_uuid : file_uuid is random immutable file identity.
  • file_public_key : file_public_key is separate from file_uuid.
  • file_public_key_type
  • file_fingerprint
  • file_format_version
  • file_kind
  • created_by_application
  • created_time_utc
  • last_saved_by_application
  • last_saved_time_utc
  • schema_catalog_hash
  • schema_catalog_version
  • object_counter_next
  • last_transaction_seq
  • last_change_seq
  • last_server_version
  • content_hash
  • signature_hash
  • compression_mode
  • retention_policy

14.3 external_file: Maps local 16-bit file aliases to external .yyy files.

Amended 2026-08-26: the alias upper bound is lifted (§7.3). A .zzz project may reference any number of files.

CREATE TABLE external_file ( external_file_alias INTEGER PRIMARY KEY CHECK (external_file_alias >= 2),

file_uuid           BLOB NOT NULL,
file_public_key     BLOB,
file_fingerprint    BLOB,

canonical_uri       TEXT,
last_known_path     TEXT,
content_hash        BLOB,

expected_schema_catalog_hash BLOB,

load_policy         INTEGER NOT NULL DEFAULT 0,
mount_state         INTEGER NOT NULL DEFAULT 0,

permission_hint     INTEGER,
display_name        TEXT,

UNIQUE(file_uuid)

);

Suggested load_policy values: 0 = load_on_demand 1 = load_immediately 2 = never_auto_load 3 = required

14.4 object_store

Core engineering object table.

Amended 2026-08-26: the 2^40 ceiling is withdrawn (§7.2). The CHECK now enforces the invalid floor and the top-16-reserved rule instead — 4294967296 is 2^32, 281474976710656 is 2^48.

CREATE TABLE object_store ( object_id INTEGER PRIMARY KEY CHECK (object_id >= 4294967296 AND object_id < 281474976710656),

parent_id       INTEGER,

object_type     INTEGER NOT NULL,
schema_version  INTEGER NOT NULL,
schema_hash     BLOB,

object_version  INTEGER NOT NULL DEFAULT 1
    CHECK (object_version > 0),

create_seq      INTEGER NOT NULL,
change_seq      INTEGER NOT NULL,

server_version  INTEGER,

lifecycle_state INTEGER NOT NULL DEFAULT 0
    CHECK (lifecycle_state BETWEEN 0 AND 3),

lock_flags      INTEGER NOT NULL DEFAULT 0,

created_by      BLOB,
created_time_utc INTEGER,

last_modified_by BLOB,
modified_time_utc INTEGER NOT NULL,

payload_size    INTEGER NOT NULL
    CHECK (payload_size >= 0),

payload_crc32c  INTEGER NOT NULL,
payload_hash    BLOB,

last_writer_transaction_id BLOB NOT NULL,

data            BLOB NOT NULL,

FOREIGN KEY(parent_id)
    REFERENCES object_store(object_id)

);

Rules: object_id is a local ID. parent_id is same-file parent only. External parent ownership not allowed — a ForeignReference proxy is a reference, never a parent. object_version is used for conflict detection. change_seq is used for sync ordering. modified_time_utc is not sync truth. data is canonical Protobuf payload.

Note: authored ForeignReference proxies (§7.3) are ordinary rows in this table, with their own object_type and their own object_id from this file’s space. They need no separate table and ride transactions, sync and tombstones like any other object.

Repair proxies are NOT rows and must never be written here. A repair proxy (file_alias = 0) is created in RAM by the loader when a same-file reference fails to resolve, and exists only to keep the referring field valid for the session and to carry the original id back to the save path. Persisting one would let a transient condition — an unmounted catalog, an unrepaired corrupt row — permanently add objects to the user’s file. The save path asserts file_alias != 0 before writing a proxy row. See id.md §5.3.

14.5 object_tombstone: Durable deletion/reference-resolution table.

Amended 2026-08-26: same id range as object_store above (§7.2).

CREATE TABLE object_tombstone ( object_id INTEGER PRIMARY KEY CHECK (object_id >= 4294967296 AND object_id < 281474976710656),

object_type            INTEGER NOT NULL,

deleted_seq            INTEGER NOT NULL,
deletion_time_utc      INTEGER NOT NULL,
deleted_by             BLOB,

last_known_parent_id   INTEGER,
last_known_name        TEXT,
last_known_tag         TEXT,

replacement_object_id  INTEGER,
replacement_file_alias INTEGER DEFAULT 0,

deletion_reason        INTEGER,
last_payload_hash      BLOB,
last_schema_hash       BLOB,

tombstone_payload      BLOB,

FOREIGN KEY(last_known_parent_id)
    REFERENCES object_store(object_id)

);

Rules: Tombstones are retained longer than transaction logs. Tombstone purge does not allow object ID reuse. replacement_file_alias = 0 means same file. replacement_file_alias >= 2 means external file alias, with no upper bound (§7.3).

This table is what lets a reference to a deleted object resolve to deleted or replaced rather than merely missing, which is the whole reason lifecycle state 2 exists (§9). A replacement chain must be followed with a depth cap and a cycle guard — a chain can dangle or loop, and neither may hang a load.

14.6 object_relation: Non-tree engineering relationships. THIS TABLE IS TO BE DISCARDED. THIS IS PART OF ENGINEERING DATA, NOT SUPPOSED TO BE EXPOSED TO DATABASE LAYER. Settled 2026-08-26: what replaces it is the ForeignReference proxy (§7.3) — engineering data in an ordinary object_store row, which is exactly what this note asked for. Same-file relationships are plain id fields in the referring object’s own payload. CREATE TABLE object_relation ( relation_id INTEGER PRIMARY KEY AUTOINCREMENT,

source_object_id  INTEGER NOT NULL,
target_file_alias INTEGER NOT NULL DEFAULT 0,
target_object_id  INTEGER NOT NULL,

relation_type     INTEGER NOT NULL,
relation_version  INTEGER NOT NULL DEFAULT 1,

lifecycle_state   INTEGER NOT NULL DEFAULT 0
    CHECK (lifecycle_state BETWEEN 0 AND 3),

change_seq        INTEGER NOT NULL,
payload_size      INTEGER NOT NULL DEFAULT 0,
payload_crc32c    INTEGER,
payload_hash      BLOB,
payload           BLOB,

FOREIGN KEY(source_object_id)
    REFERENCES object_store(object_id)

);

Use this for relationships that are not ownership hierarchy: pipe supported by support. equipment connected to nozzle. member connected to plate. instrument mounted on pipe. object references standard catalog item

14.7 transaction_log: Groups changes into engineering transactions.
CREATE TABLE transaction_log ( transaction_id BLOB PRIMARY KEY,

transaction_seq     INTEGER NOT NULL UNIQUE,

author_id           BLOB,
author_device_id    BLOB,
author_session_id   BLOB,

begin_time_utc      INTEGER,
commit_time_utc     INTEGER NOT NULL,

transaction_kind    INTEGER NOT NULL,

base_server_version INTEGER,
server_version      INTEGER,

changed_object_count INTEGER NOT NULL DEFAULT 0,
inserted_count       INTEGER NOT NULL DEFAULT 0,
updated_count        INTEGER NOT NULL DEFAULT 0,
deleted_count        INTEGER NOT NULL DEFAULT 0,
relation_change_count INTEGER NOT NULL DEFAULT 0,

operation_summary   TEXT,

transaction_crc32c  INTEGER,
transaction_hash    BLOB

);

Suggested transaction_kind:

1 = user_edit 2 = autosave_commit 3 = import 4 = vendor_merge 5 = delete 6 = replace 7 = schema_migration 8 = catalog_update 9 = server_maintenance 14.8 object_change_log

Recent sync/change metadata.

CREATE TABLE object_change_log ( change_seq INTEGER PRIMARY KEY AUTOINCREMENT,

transaction_id      BLOB NOT NULL,

object_id           INTEGER NOT NULL,
operation_type      INTEGER NOT NULL,

object_version      INTEGER NOT NULL,

parent_id_before    INTEGER,
parent_id_after     INTEGER,

old_payload_hash    BLOB,
new_payload_hash    BLOB,

modified_by         BLOB,
modified_time_utc   INTEGER NOT NULL,

FOREIGN KEY(transaction_id)
    REFERENCES transaction_log(transaction_id)

);

Suggested operation_type:

1 = insert 2 = update 3 = move 4 = soft_delete 5 = tombstone 6 = replace 7 = purge_stub 8 = restore 14.9 relation_change_log

Recent relation sync metadata.

CREATE TABLE relation_change_log ( relation_change_seq INTEGER PRIMARY KEY AUTOINCREMENT,

transaction_id      BLOB NOT NULL,

relation_id         INTEGER NOT NULL,
operation_type      INTEGER NOT NULL,

source_object_id    INTEGER,
target_file_alias   INTEGER,
target_object_id    INTEGER,
relation_type       INTEGER,

old_payload_hash    BLOB,
new_payload_hash    BLOB,

modified_by         BLOB,
modified_time_utc   INTEGER NOT NULL,

FOREIGN KEY(transaction_id)
    REFERENCES transaction_log(transaction_id)

);

14.10 object_undo_log: Optional short-term undo payload storage.
CREATE TABLE object_undo_log ( undo_id INTEGER PRIMARY KEY AUTOINCREMENT,

transaction_id      BLOB NOT NULL,
object_id           INTEGER NOT NULL,

object_version_before INTEGER,
object_version_after  INTEGER,

operation_type      INTEGER NOT NULL,

old_payload_size    INTEGER,
old_payload_crc32c  INTEGER,
old_payload_hash    BLOB,
old_payload         BLOB,

new_payload_size    INTEGER,
new_payload_crc32c  INTEGER,
new_payload_hash    BLOB,

created_time_utc    INTEGER NOT NULL,

FOREIGN KEY(transaction_id)
    REFERENCES transaction_log(transaction_id)

);

Retention: 7-30 days, configurable, not required for long-term audit

14.11 audit_summary: Compact long-term audit record.
CREATE TABLE audit_summary ( audit_id INTEGER PRIMARY KEY AUTOINCREMENT,

transaction_id       BLOB NOT NULL,
transaction_seq      INTEGER NOT NULL,

author_id            BLOB,
author_device_id     BLOB,

commit_time_utc      INTEGER NOT NULL,

changed_object_count INTEGER NOT NULL,
inserted_count       INTEGER NOT NULL,
updated_count        INTEGER NOT NULL,
deleted_count        INTEGER NOT NULL,
relation_change_count INTEGER NOT NULL DEFAULT 0,

affected_root_id     INTEGER,
affected_discipline  INTEGER,
affected_file_uuid   BLOB,

operation_summary    TEXT,

transaction_hash     BLOB

);

Retention: project life / forever

14.12 sync_checkpoint: Defines safe boundaries for pruning logs.
CREATE TABLE sync_checkpoint ( checkpoint_id INTEGER PRIMARY KEY AUTOINCREMENT,

checkpoint_change_seq  INTEGER NOT NULL,
checkpoint_transaction_seq INTEGER NOT NULL,

checkpoint_time_utc    INTEGER NOT NULL,

object_count           INTEGER NOT NULL,
live_object_count      INTEGER NOT NULL,
tombstone_count        INTEGER NOT NULL,
relation_count         INTEGER NOT NULL DEFAULT 0,

object_store_hash      BLOB,
relation_store_hash    BLOB,
schema_catalog_hash    BLOB,

created_by             BLOB

);

Pruning rule:

Never prune object_change_log below a checkpoint unless current object_store has been verified against that checkpoint policy. 14.13 schema_catalog

Stores known object schema/catalog metadata.

CREATE TABLE schema_catalog ( schema_catalog_hash BLOB PRIMARY KEY,

schema_catalog_version INTEGER NOT NULL,

created_time_utc INTEGER,
source_name      TEXT,

descriptor_blob  BLOB,
descriptor_hash  BLOB,

notes            TEXT

);

14.14 object_type_registry: Maps object type IDs to schema identity.
CREATE TABLE object_type_registry ( object_type INTEGER PRIMARY KEY,

type_name          TEXT NOT NULL,
discipline         INTEGER,
protobuf_type_name TEXT NOT NULL,

current_schema_version INTEGER NOT NULL,
current_schema_hash    BLOB NOT NULL,

flags             INTEGER NOT NULL DEFAULT 0

);

14.15 object_schema_version: Tracks schema versions per object type.
CREATE TABLE object_schema_version ( object_type INTEGER NOT NULL, schema_version INTEGER NOT NULL,

schema_hash       BLOB NOT NULL,
protobuf_type_name TEXT NOT NULL,

migration_from_previous_available INTEGER NOT NULL DEFAULT 0,

descriptor_blob   BLOB,
created_time_utc  INTEGER,

PRIMARY KEY(object_type, schema_version)

);

14.16 spatial_bounds: Optional, for viewport/region subscription and fast loading.
CREATE TABLE spatial_bounds ( object_id INTEGER PRIMARY KEY,

min_x REAL NOT NULL,
min_y REAL NOT NULL,
min_z REAL NOT NULL,

max_x REAL NOT NULL,
max_y REAL NOT NULL,
max_z REAL NOT NULL,

bounds_version INTEGER NOT NULL,

FOREIGN KEY(object_id)
    REFERENCES object_store(object_id)

);

Later, use SQLite R-Tree:
CREATE VIRTUAL TABLE spatial_bounds_rtree USING rtree( object_id, min_x, max_x, min_y, max_y, min_z, max_z );

14.17 reference_index: Optional extracted reference table for diagnostics and reference scan.
CREATE TABLE reference_index ( source_object_id INTEGER NOT NULL,

source_field_id      INTEGER,
source_field_name    TEXT,

target_file_alias    INTEGER NOT NULL,
target_object_id     INTEGER NOT NULL,

reference_kind       INTEGER NOT NULL,

last_verified_seq    INTEGER,
last_resolution_status INTEGER,

PRIMARY KEY(
    source_object_id,
    source_field_id,
    target_file_alias,
    target_object_id,
    reference_kind
),

FOREIGN KEY(source_object_id)
    REFERENCES object_store(object_id)

);

This table is derived from Protobuf payloads. It is not the canonical source of references. target_file_alias has no upper bound (§7.3); for a reference that goes through a ForeignReference proxy, the extractor records the proxy’s target, not the proxy’s own id, so a where-used query answers about the real object.

Use it for: dangling reference scan, safe tombstone purge, impact analysis, where-used queries

Note this table is the ONLY reverse-lookup mechanism, and it is optional and derived. In RAM there is none: id.md §3.6 decides that referrer queries are a linear scan, with no back-references and no mirror index.

14.18 integrity_issue: Persistent diagnostics table.
CREATE TABLE integrity_issue ( issue_id INTEGER PRIMARY KEY AUTOINCREMENT,

detected_time_utc INTEGER NOT NULL,
detected_by       BLOB,

severity        INTEGER NOT NULL,
issue_type      INTEGER NOT NULL,

object_id       INTEGER,
relation_id     INTEGER,
transaction_id  BLOB,

message         TEXT,
details         BLOB,

resolved_time_utc INTEGER,
resolved_by       BLOB,
resolution_note   TEXT

);

14.19 .yyy indexes CREATE INDEX idx_object_parent_live ON object_store(parent_id, object_id) WHERE lifecycle_state = 0;

CREATE INDEX idx_object_type_live ON object_store(object_type, object_id) WHERE lifecycle_state = 0;

CREATE INDEX idx_object_change_seq ON object_store(change_seq);

CREATE INDEX idx_object_server_version ON object_store(server_version) WHERE server_version IS NOT NULL;

CREATE INDEX idx_object_modified_by ON object_store(last_modified_by, change_seq);

CREATE INDEX idx_object_lifecycle ON object_store(lifecycle_state, object_id);

CREATE INDEX idx_transaction_seq ON transaction_log(transaction_seq);

CREATE INDEX idx_transaction_commit_time ON transaction_log(commit_time_utc);

CREATE INDEX idx_change_log_transaction ON object_change_log(transaction_id);

CREATE INDEX idx_change_log_object ON object_change_log(object_id, change_seq);

CREATE INDEX idx_change_log_time ON object_change_log(modified_time_utc);

CREATE INDEX idx_tombstone_deleted_seq ON object_tombstone(deleted_seq);

CREATE INDEX idx_relation_source ON object_relation(source_object_id, relation_type);

CREATE INDEX idx_relation_target ON object_relation(target_file_alias, target_object_id, relation_type);

CREATE INDEX idx_reference_target ON reference_index(target_file_alias, target_object_id);

CREATE INDEX idx_reference_source ON reference_index(source_object_id);

  1. .zzz SQLite schema A .zzz file stores administrative/project truth.

15.1 .zzz project_info CREATE TABLE project_info ( key TEXT PRIMARY KEY, value BLOB NOT NULL);

Required keys:

project_uuid project_public_key project_public_key_type project_format_version project_name created_by_application created_time_utc last_saved_time_utc schema_catalog_hash permission_model_version root_folder_id last_admin_transaction_seq 15.2 project_folder

Administrative folder tree.

CREATE TABLE project_folder ( folder_id INTEGER PRIMARY KEY AUTOINCREMENT,

parent_folder_id INTEGER,

folder_name      TEXT NOT NULL,
folder_code      TEXT,

display_order    INTEGER NOT NULL DEFAULT 0,

lifecycle_state  INTEGER NOT NULL DEFAULT 0
    CHECK (lifecycle_state BETWEEN 0 AND 3),

created_by       BLOB,
created_time_utc INTEGER,

modified_by      BLOB,
modified_time_utc INTEGER,

FOREIGN KEY(parent_folder_id)
    REFERENCES project_folder(folder_id)

);

Rules: Folders in .zzz are administrative containers. They are not engineering objects. They hold mounted .yyy files and child .zzz projects.

15.3 mounted_yyy: Files mounted into the project.
CREATE TABLE mounted_yyy ( mounted_file_id INTEGER PRIMARY KEY AUTOINCREMENT,

parent_folder_id INTEGER NOT NULL,

file_uuid        BLOB NOT NULL,
file_public_key  BLOB,
file_fingerprint BLOB,

canonical_uri    TEXT,
last_known_path  TEXT,

display_name     TEXT NOT NULL,
role             INTEGER NOT NULL,

mount_state      INTEGER NOT NULL DEFAULT 0,
load_policy      INTEGER NOT NULL DEFAULT 0,

schema_catalog_hash BLOB,

permission_anchor_id INTEGER,

created_by       BLOB,
created_time_utc INTEGER,

modified_by      BLOB,
modified_time_utc INTEGER,

UNIQUE(file_uuid),

FOREIGN KEY(parent_folder_id)
    REFERENCES project_folder(folder_id)

);

Suggested role:

1 = engineering_model 2 = catalog 3 = vendor_model 4 = reference_model 5 = template 6 = archive 15.4 mounted_zzz

Child project/vendor package mount table.

CREATE TABLE mounted_zzz ( mounted_project_id INTEGER PRIMARY KEY AUTOINCREMENT,

parent_folder_id   INTEGER NOT NULL,

child_project_uuid BLOB NOT NULL,
child_project_public_key BLOB,
child_project_fingerprint BLOB,

canonical_uri      TEXT,
last_known_path    TEXT,

display_name       TEXT NOT NULL,

mount_state        INTEGER NOT NULL DEFAULT 0,
override_policy    INTEGER NOT NULL DEFAULT 0,

permission_anchor_id INTEGER,

created_by         BLOB,
created_time_utc   INTEGER,

modified_by        BLOB,
modified_time_utc  INTEGER,

UNIQUE(child_project_uuid),

FOREIGN KEY(parent_folder_id)
    REFERENCES project_folder(folder_id)

);

Rules: No circular mounting. Parent .zzz may inherit or override child permissions by policy. Child .zzz remains separately identifiable.

15.5 principal: Users, groups, services.
CREATE TABLE principal ( principal_id BLOB PRIMARY KEY,

principal_type    INTEGER NOT NULL,
display_name      TEXT NOT NULL,

email             TEXT,
external_auth_id  TEXT,

lifecycle_state   INTEGER NOT NULL DEFAULT 0,

created_time_utc  INTEGER,
modified_time_utc INTEGER

);

Suggested principal_type:

1 = user 2 = group 3 = service_account 4 = vendor_org 5 = client_org

15.6 principal_group_member: Group membership.
CREATE TABLE principal_group_member ( group_principal_id BLOB NOT NULL, member_principal_id BLOB NOT NULL,

created_time_utc INTEGER,

PRIMARY KEY(group_principal_id, member_principal_id),

FOREIGN KEY(group_principal_id)
    REFERENCES principal(principal_id),

FOREIGN KEY(member_principal_id)
    REFERENCES principal(principal_id)

);

15.7 permission_rule: Folder/file/project permissions.
CREATE TABLE permission_rule ( permission_rule_id INTEGER PRIMARY KEY AUTOINCREMENT,

principal_id       BLOB NOT NULL,

target_kind        INTEGER NOT NULL,
target_id          INTEGER NOT NULL,

permission_mask    INTEGER NOT NULL,

inheritance_mode   INTEGER NOT NULL DEFAULT 0,
rule_effect        INTEGER NOT NULL DEFAULT 1,

priority           INTEGER NOT NULL DEFAULT 0,

created_by         BLOB,
created_time_utc   INTEGER,

modified_by        BLOB,
modified_time_utc  INTEGER,

FOREIGN KEY(principal_id)
    REFERENCES principal(principal_id)

);

Suggested target_kind:

1 = project_root 2 = folder 3 = mounted_yyy 4 = mounted_zzz

Suggested rule_effect:

1 = allow 2 = deny

Suggested permission_mask bits:

1 = view 2 = edit 4 = create 8 = delete 16 = manage_permissions 32 = approve 64 = import 128 = export 256 = mount_child_project 512 = administer

No object-level target kind.

15.8 effective_permission_cache: Optional server-generated cache.
CREATE TABLE effective_permission_cache ( principal_id BLOB NOT NULL,

target_kind       INTEGER NOT NULL,
target_id         INTEGER NOT NULL,

effective_mask    INTEGER NOT NULL,

computed_time_utc INTEGER NOT NULL,
permission_version INTEGER NOT NULL,

PRIMARY KEY(principal_id, target_kind, target_id)

);

This is disposable/recomputable.

15.9 project_admin_transaction_log: Administrative transaction log.
CREATE TABLE project_admin_transaction_log ( admin_transaction_id BLOB PRIMARY KEY,

admin_transaction_seq INTEGER NOT NULL UNIQUE,

author_id          BLOB,
commit_time_utc    INTEGER NOT NULL,

operation_kind     INTEGER NOT NULL,
operation_summary  TEXT,

transaction_hash   BLOB

);

Used for: folder changes, file mounting, permission changes, vendor package mounting, project metadata changes

15.10 project_audit_summary CREATE TABLE project_audit_summary ( audit_id INTEGER PRIMARY KEY AUTOINCREMENT,

admin_transaction_id  BLOB NOT NULL,
admin_transaction_seq INTEGER NOT NULL,

author_id             BLOB,
commit_time_utc       INTEGER NOT NULL,

operation_kind        INTEGER NOT NULL,
operation_summary     TEXT,

transaction_hash      BLOB

);

15.11 .zzz indexes CREATE INDEX idx_project_folder_parent ON project_folder(parent_folder_id, display_order);

CREATE INDEX idx_mounted_yyy_folder ON mounted_yyy(parent_folder_id, display_name);

CREATE INDEX idx_mounted_yyy_uuid ON mounted_yyy(file_uuid);

CREATE INDEX idx_mounted_zzz_folder ON mounted_zzz(parent_folder_id, display_name);

CREATE INDEX idx_mounted_zzz_uuid ON mounted_zzz(child_project_uuid);

CREATE INDEX idx_permission_principal ON permission_rule(principal_id);

CREATE INDEX idx_permission_target ON permission_rule(target_kind, target_id);

CREATE INDEX idx_effective_permission_principal ON effective_permission_cache(principal_id);

CREATE INDEX idx_admin_transaction_seq ON project_admin_transaction_log(admin_transaction_seq);

  1. Integrity fields 16.1 payload_crc32c: Fast accidental corruption detection.
    Used for: BLOB truncation, wrong decompression, bad read, memory corruption before write, partial payload write, Not a security mechanism.

16.2 payload_size: Stores expected serialized Protobuf byte size.
Used for: preallocation, truncation detection, sanity checking, payload limits,

16.3 schema_hash: Identifies persistence schema, not RAM layout.
Should represent: object type, protobuf message identity, field numbers, field types, semantic constraints, migration version, unit metadata if schema-bound

16.4 last_writer_transaction_id: Links each object to the transaction that last wrote it.
Used for: audit, debugging, rollback tooling, sync diagnosis

16.5 file_uuid: Immutable random file identity.
Used for: external reference resolution, file move/rename detection, vendor package identity, duplicate detection, catalog identity. Do not overload it as an ED448 public key.
Use: file_uuid, file_public_key, file_fingerprint as separate fields.

16.6 schema_catalog_hash: Whole catalog/schema compatibility fingerprint. Used to detect whether the executable understands the file’s object types and persistence schemas.

  1. Object reference resolution Amended 2026-08-26 for §7.3: there is no packed reference to unpack. A reference field holds a plain local_object_id, and resolution branches on what that id turns out to name.

Given a reference field holding local_object_id:

    1. object_id = 0 means null reference.
    1. object_id below 2^32 means corrupt — something truncated a 64-bit id (§7.2).
    1. Look it up in this file’s object_store. If it is NOT a ForeignReference proxy, it is a same-file reference and resolution ends here — check object_tombstone if the row is absent.
    1. If it IS a proxy, read fileAlias and targetObjectId from its payload and continue.
    1. fileAlias = 0 means a same-file target that failed to resolve at load — the proxy carries the original id and the recorded outcome (§6.2 of id.md). Do not treat it as external.
    1. fileAlias = 1 is temporary; invalid in committed storage.
    1. fileAlias >= 2 resolves through external_file.
    1. Check whether target file is loaded. load_policy may legitimately mean it is not — file_not_loaded is an ordinary outcome, not an error.
    1. Check permission if server-hosted.
    1. Check object_store, then object_tombstone; follow replacement_object_id with a depth cap.
    1. Verify the target’s object_type against the proxy’s expectedType. A mismatch is replaced/corrupt, never a valid target.
    1. Return resolution status.

Two rules the loader must honour, whatever the outcome: a reference whose target was absent this session is never written back as 0 — the original id round-trips, because the catalog may be mounted or the corruption repaired tomorrow. And a bad reference never fails a load; it degrades to a reported status. id.md §6 carries both.

Possible status:

resolved null_reference temporary_reference file_alias_unknown file_not_loaded permission_denied missing deleted moved replaced schema_unsupported corrupt_payload

  1. Server/client restriction summary 18.1 Server-hosted .zzz Allowed:
  • server opens .zzz
  • server opens/mounts .yyy files
  • client connects to server
  • client edits through AutoSave transactions
  • server persists
  • server publishes deltas

Forbidden:

  • client saving local authoritative copy
  • client acting as child server
  • peer connecting through client
  • client writing .yyy directly
  • client bypassing .zzz permissions

18.2 Local .yyy virtual server Allowed:

  • desktop opens local .yyy
  • desktop becomes authority
  • network peers connect to it
  • AutoSave commits to local .yyy through host
  • host publishes deltas

Forbidden:

  • peers writing .yyy file directly
  • multiple independent writers to same .yyy
  • per-object permission
  • nested server chain beyond local host policy
  1. Current finalized rules These are the strongest finalized rules so far:
  • .yyy is engineering truth.
  • .zzz is administrative/project truth.
  • Protobuf is persistent object payload.
  • FlatBuffers is sync/transport.
  • Optional64 is RAM-only.
  • AutoSave is always enabled in editable connected sessions.
  • Server-hosted .zzz clients cannot host child peer sessions.
  • Local .yyy opened from disk may act as virtual server.
  • No per-object permission.
  • No folder permission inside .yyy.
  • Permissions live in .zzz only.
  • Persistent object IDs are server/host assigned.
  • Client temporary IDs are RAM/transaction-only.
  • Object IDs are not recycled during active project life.
  • object_store.object_id is local object ID only.
  • References are plain local object IDs. Foreign targets go through a ForeignReference proxy; nothing is packed (§7.3).
  • modified_time_utc is audit/display only. Not for sync.

FOLLOWING NEED FURTHER DELIBERATION:

  • change_seq is sync truth.
  • object_version is conflict truth.
  • Tombstones live longer than transaction logs.
  • Full transaction logs may be pruned after checkpoints.
  • Audit summary remains long-term.
  1. One recommendation before implementation Before coding the SQLite layer, freeze these enum registries in one design document:

object_type relation_type operation_type transaction_kind lifecycle_state reference_resolution_status permission_mask bits mounted file role load_policy mount_state

That enum registry is as important as the table schema. It will become the common language between:

C++ runtime Protobuf schema FlatBuffers sync packets SQLite storage server validation debug tools future migration tools