psi.sqlite schema (Photos 8, Ventura, macOS 13.0)

CREATE TABLE word_embedding(word TEXT, extended_word TEXT, score DOUBLE);
CREATE INDEX word_embedding_index ON word_embedding(word);
CREATE VIRTUAL TABLE word_embedding_prefix USING fts5(extended_word)
/* word_embedding_prefix(extended_word) */
;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE groups(
    category INT2,
    owning_groupid INT,
    content_string TEXT,
    normalized_string TEXT,
    lookup_identifier TEXT,
    token_ranges_0 INT8,
    token_ranges_1 INT8,
    UNIQUE(
        category,
        owning_groupid,
        content_string,
        lookup_identifier,
        token_ranges_0,
        token_ranges_1
    )
);
CREATE TABLE assets(
    uuid_0 INT,
    uuid_1 INT,
    creationDate INT,
    UNIQUE(uuid_0, uuid_1)
);
CREATE TABLE ga(
    groupid INT,
    assetid INT,
    PRIMARY KEY(groupid, assetid)
);
CREATE TABLE collections(
    uuid_0 INT,
    uuid_1 INT,
    startDate INT,
    endDate INT,
    title TEXT,
    subtitle TEXT,
    type INT8,
    keyAssetUUIDPrivate TEXT,
    keyAssetUUIDShared TEXT,
    assetsCountPrivate INT32,
    assetsCountShared INT32,
    sortDate DOUBLE,
    UNIQUE(uuid_0, uuid_1)
);
CREATE TABLE gc(
    groupid INT,
    collectionid INT,
    PRIMARY KEY(groupid, collectionid)
);
CREATE TABLE tmp_used_groupids (groupid INTEGER PRIMARY KEY);
CREATE VIRTUAL TABLE prefix USING fts5(
    content = 'groups',
    normalized_string,
    category UNINDEXED,
    tokenize = 'PSITokenizer'
);
CREATE TABLE IF NOT EXISTS 'prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE lookup(identifier TEXT PRIMARY KEY, category INT2);
CREATE TRIGGER trigger_groups_insert
AFTER
INSERT ON groups BEGIN
INSERT INTO prefix(rowid, normalized_string, category)
VALUES (new.rowid, new.normalized_string, new.category);
END;
CREATE TRIGGER trigger_groups_delete
AFTER DELETE ON groups BEGIN
INSERT INTO prefix(prefix, rowid, normalized_string, category)
VALUES(
        'delete',
        old.rowid,
        old.normalized_string,
        old.category
    );
END;
CREATE INDEX groupid_by_contentstring_category_lookupid_owner ON groups(
    content_string,
    category,
    lookup_identifier,
    owning_groupid
);
CREATE INDEX asset_pk ON assets(uuid_0, uuid_1);
CREATE INDEX ga_assetid ON ga(assetid, groupid);
CREATE INDEX ga_groupid ON ga(groupid);
CREATE INDEX collection_pk ON collections(uuid_0, uuid_1);
CREATE INDEX gc_collectionid ON gc(collectionid);
CREATE INDEX gc_groupid ON gc(groupid);