Database Schemas

Full SQL schema for core.storage's tables. All tables use soft delete (deleted_at) unless otherwise noted.

Design Principles

• Soft delete everywhere. Every mutable table has deleted_at DATETIME NULL. User-facing queries always filter WHERE deleted_at IS NULL. Hard deletion is reserved for background cleanup jobs.


• Soft revoke for access. object_ownership uses expired_at instead of delete. Access history is preserved.


• Denormalized counters. objects.shard_count and objects.total_size_bytes are denormalized from object_shards for read performance. A reconciliation job can fix drift.


• No provider credentials in DB. Providers are configured on the filesystem. The database only stores a provider_ref string that matches the config key.

Tables

pools

Storage containers. Each pool maps to one S3 bucket on one provider/region. Tied to a core.console project.

CREATE TABLE pools (

    pool_id              INT PRIMARY KEY AUTO_INCREMENT,

    project_id           VARCHAR(64) NOT NULL,        -- core.console project ID

    display_name         VARCHAR(255) NOT NULL,       -- user-chosen name ("Primary Europe Storage")

    provider_ref         VARCHAR(100) NOT NULL,       -- matches config key ("aws-paris")

    internal_bucket_name VARCHAR(255) NOT NULL UNIQUE, -- auto-generated ("unvrsl-p-abc123-pool-xyz789")

    region               VARCHAR(100) NOT NULL,       -- provider region ("eu-west-3")

    status               ENUM('provisioning', 'active', 'provisioning_failed', 'draining', 'disabled')

                         NOT NULL DEFAULT 'provisioning',

    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    deleted_at           DATETIME NULL,



    INDEX idx_project (project_id),

    INDEX idx_provider (provider_ref),

    INDEX idx_status (status)

);

Notes:

• display_name is what users see. internal_bucket_name is what S3 sees. They are completely independent.


• provisioning_failed allows retry without losing the record.


• draining means the pool is being emptied (migration or decommission). No new uploads allowed.


• disabled means the pool exists but is not accessible. Soft alternative to deletion.

objects

File metadata. Each object is a logical file (what the user uploaded). It may consist of one or more shards.

CREATE TABLE objects (

    object_id          INT PRIMARY KEY AUTO_INCREMENT,

    pool_id            INT NOT NULL,                 -- FK → pools

    original_filename  VARCHAR(500) NOT NULL,        -- what the user named the file

    mime_type          VARCHAR(255) NOT NULL,

    shard_count        INT NOT NULL DEFAULT 1,       -- denormalized from object_shards

    total_size_bytes   BIGINT NOT NULL DEFAULT 0,    -- denormalized sum of shard sizes

    checksum           VARCHAR(128),                 -- whole-object hash (computed from shards)

    upload_status      ENUM('pending', 'uploading', 'complete', 'failed')

                       NOT NULL DEFAULT 'pending',

    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    deleted_at         DATETIME NULL,



    INDEX idx_pool (pool_id),

    INDEX idx_status (upload_status),

    INDEX idx_deleted (deleted_at)

);

Notes:

• upload_status tracks the overall object. Individual shard statuses are in object_shards.


• shard_count and total_size_bytes are updated as shards are added/removed. Denormalized for fast reads.


• checksum is computed after all shards are complete (hash of concatenated shard checksums).

object_shards

Physical storage units. Each shard is one S3 object. A file smaller than the provider's single-PUT limit has exactly one shard. Larger files are split into multiple shards.

CREATE TABLE object_shards (

    shard_id        INT PRIMARY KEY AUTO_INCREMENT,

    object_id       INT NOT NULL,                    -- FK → objects

    shard_index     INT NOT NULL,                    -- 0-based ordering

    s3_key          VARCHAR(500) NOT NULL,            -- S3 object key for this shard

    size_bytes      BIGINT NOT NULL,

    checksum        VARCHAR(128),                    -- per-shard hash

    upload_status   ENUM('pending', 'uploading', 'complete', 'failed')

                    NOT NULL DEFAULT 'pending',

    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    deleted_at      DATETIME NULL,



    INDEX idx_object (object_id),

    INDEX idx_status (upload_status),

    UNIQUE KEY uk_object_shard (object_id, shard_index)

);

Notes:

• shard_index is 0-based. Ordering matters for download reassembly.


• s3_key is the key within the pool's internal_bucket_name. Format: shards/{object_id}/{shard_index}-{uuid}.


• Each shard has its own upload_status. An object is complete only when all its shards are complete.


• checksum is per-shard. The whole-object checksum on objects is derived from these.

object_ownership

Access control. Who has access to what, and at what level. Uses soft revoke (expired_at) — revoking access sets expired_at to now but preserves the record.

CREATE TABLE object_ownership (

    ownership_id    INT PRIMARY KEY AUTO_INCREMENT,

    object_id       INT NOT NULL,                    -- FK → objects

    owner_type      ENUM('user', 'app') NOT NULL,    -- who owns this

    owner_id        VARCHAR(64) NOT NULL,             -- user_id or app_id (from core.auth / core.console)

    access_level    ENUM('viewer', 'editor', 'owner') NOT NULL,

    is_creator      BOOLEAN NOT NULL DEFAULT FALSE,   -- true for the original uploader

    granted_by      VARCHAR(64),                     -- who granted this access (user_id or system)

    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    expired_at      DATETIME NULL,                    -- NULL = active, non-NULL = revoked



    INDEX idx_object (object_id),

    INDEX idx_owner (owner_type, owner_id),

    INDEX idx_active (expired_at)

);

Notes:

• expired_at IS NULL means the ownership is active. expired_at set means revoked.


• is_creator is true for the original uploader. Set once, never changed.


• granted_by tracks delegation — user A shares with user B, granted_by = user A's ID.


• Access level hierarchy: owner > editor > viewer.

object_comments

Comments on files. Supports threaded replies via parent_comment_id. Supports positional anchors (e.g. "comment on page 3 of a PDF").

CREATE TABLE object_comments (

    comment_id          INT PRIMARY KEY AUTO_INCREMENT,

    object_id           INT NOT NULL,                -- FK → objects

    user_id             VARCHAR(64) NOT NULL,         -- from core.auth

    comment_text        TEXT NOT NULL,

    parent_comment_id   INT NULL,                     -- FK → object_comments (NULL = top-level comment)

    anchor              JSON NULL,                    -- positional anchor (app-interpreted)

    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    deleted_at          DATETIME NULL,



    INDEX idx_object (object_id),

    INDEX idx_parent (parent_comment_id),

    INDEX idx_user (user_id)

);

Notes:

• parent_comment_id enables threaded replies. NULL = top-level comment, non-NULL = reply.


• anchor is a JSON field. Its structure depends on the file type. Examples:

- PDF: {"page": 3, "x": 120, "y": 450}
- Image: {"x": 0.5, "y": 0.3} (normalized coordinates)
- Video: {"time": 45.2} (seconds)
- NULL = no specific position (general comment)

• Apps interpret the anchor. core.storage stores it opaquely.

Entity Relationships

core.console project

        │

        │  (project_id)

        ▼

    ┌────────┐

    │ pools  │ ──── provider_ref ──→ (config file, not DB)

    └───┬────┘

        │

        │  (pool_id)

        ▼

    ┌────────┐

    │objects │

    └───┬────┘

        │

        ├─── (object_id) ──→ object_ownership

        ├─── (object_id) ──→ object_comments

        │

        │  (object_id)

        ▼

  ┌──────────────┐

  │object_shards │

  └──────────────┘

Soft Delete & Soft Revoke Summary

Table	Soft Column	Meaning when set

pools	deleted_at	Pool is logically removed. No new objects. Background job drains and cleans.
objects	deleted_at	File is hidden from users. S3 data persists until cleanup job runs.
object_shards	deleted_at	Shard is hidden. S3 object persists until cleanup job runs.
object_ownership	expired_at	Access revoked. Record preserved for audit trail.
object_comments	deleted_at	Comment hidden. Record preserved.

Related

• Provider Model — Provider config and pool provisioning


• Upload & Download — How these tables are used during transfers


• Delete & Lifecycle — Soft delete, hard cleanup, pool draining