SQLite Schema GitHub issue

create table records (
    record_pk text primary key default (
        lower(
            hex(randomblob(4)) || '-' ||
            hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)), 2) || '-' ||
            substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2) || '-' ||
            hex(randomblob(6))
        )
    )
);

create trigger records_no_update
before update on records
begin
    select raise(fail, 'records rows are immutable');
end;

create table records_history (
    instance_pk  text primary key default (
        lower(
            hex(randomblob(4)) || '-' ||
            hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)), 2) || '-' ||
            substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2) || '-' ||
            hex(randomblob(6))
        )
    ),
    record_pk  text not null references records(record_pk),
    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
    active     integer not null default 1 check(active in (0, 1)),
    bucket     text check(active = 0 or (bucket is not null and json_type(bucket) = 'object')),
    stack      text check(active = 0 or (stack is not null and json_type(stack) = 'object')),
    whole_hash integer not null default 0 check(whole_hash in (0, 1)),
    body       text check(whole_hash = 0 or (body is not null and json_type(body) = 'object')),
    unique(record_pk, updated_at)
);

-- An instance record uses the `bucket` and `stack` columns: `bucket` is the
-- object's data hash; `stack` is the ordered hash of platters (each platter
-- being {class, warning?, bucket?}). The platter at position 1 must be named
-- "shadow" — and when no "shadow" entry is present in the stack hash, an
-- empty shadow is understood to sit at position 1.
--
-- A class-definition record (an instance of puck.uno/class) uses the
-- whole-hash form instead: the row sets whole_hash = 1 and stores the
-- definition object (containing class='puck.uno/class' plus the definition's
-- name/inherits/fields/methods properties) in `body`. The `bucket` and
-- `stack` columns are null for whole-hash rows. See the worldlets reference
-- for both shapes.

create trigger records_history_no_update
before update on records_history
begin
    select raise(fail, 'records_history rows are immutable');
end;

-- Enforce unique class names among active class-definition records.
-- A class-definition record stores its definition in `body` with
-- whole_hash = 1 and body.class = 'puck.uno/class'; the class's name
-- is body.name.
create trigger records_history_unique_class_name
before insert on records_history
when new.active = 1
    and new.whole_hash = 1
    and json_extract(new.body, '$.class') = 'puck.uno/class'
begin
    select raise(fail, 'duplicate class name')
    where exists (
        select 1
        from current_records cr
        where
            cr.record_pk != new.record_pk
            and cr.whole_hash = 1
            and json_extract(cr.body, '$.class') = 'puck.uno/class'
            and json_extract(cr.body, '$.name') = json_extract(new.body, '$.name')
    );
end;

-- current_records: the latest active history row for each record.
-- Tie-breaking uses instance_pk desc in case two rows share the same updated_at.
create view current_records as
with ranked as (
    select
        rh.*,
        row_number() over (
            partition by rh.record_pk
            order by rh.updated_at desc, rh.instance_pk desc
        ) as row_num
    from records_history rh
)
select
    instance_pk,
    record_pk,
    updated_at,
    active,
    bucket,
    stack,
    whole_hash,
    body
from ranked
where row_num = 1
and active = 1;

create table files (
    file_pk text primary key default (
        lower(
            hex(randomblob(4)) || '-' ||
            hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)), 2) || '-' ||
            substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2) || '-' ||
            hex(randomblob(6))
        )
    ),
    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
    size       integer not null,
    sha256     text not null unique
);

create trigger files_no_update
before update on files
begin
    select raise(fail, 'files rows are immutable');
end;

create table file_chunks (
    file_chunk_pk integer primary key,
    file_pk       text not null references files(file_pk) on delete cascade,
    chunk_index   integer not null check(chunk_index >= 0),
    content       blob not null,
    last          integer not null default 0 check(last in (0, 1)),
    unique(file_pk, chunk_index)
);

create unique index file_chunks_one_last_per_file
    on file_chunks(file_pk)
    where last = 1;

create trigger file_chunks_no_update
before update on file_chunks
begin
    select raise(fail, 'file_chunks rows are immutable');
end;