chadtmiller.com/pds.js

Switch repository

Loading…

// @pdsjs/core/space-schema - permissioned space tables (proposal 0016), and the
// one query neither adapter can express as a fixed prepared statement.
//
// Declared here, beside the SpaceStoragePort typedef in ports.js, because both
// SQLite adapters need it and neither may depend on @pdsjs/spaces without making
// the spaces package non-optional. Ported from the reference implementation's
// packages/pds/src/actor-store/db/migrations/002-space.ts.
//
// Idempotent: safe to run on every construction. Nothing here executes unless
// an adapter calls it, so a deployment that never enables spaces never creates
// these tables.

export const SPACE_SCHEMA_SQL = `
  CREATE TABLE IF NOT EXISTS space (
    uri TEXT PRIMARY KEY,
    space_did TEXT NOT NULL,
    space_type TEXT NOT NULL,
    is_owner INTEGER NOT NULL,
    read_policy TEXT NOT NULL DEFAULT 'member-list',
    read_managing_app TEXT,
    write_policy TEXT NOT NULL DEFAULT 'member-list',
    write_managing_app TEXT,
    app_access_type TEXT NOT NULL DEFAULT 'open',
    app_allowed TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL,
    deleted_at TEXT,
    policy_json TEXT
  );
  -- space_did and space_type are the URI's authority and type components,
  -- supplied by the domain rather than re-derived here: parsing space URIs is
  -- @pdsjs/spaces's job, and storage packages must not depend on it. Indexed so
  -- listSpaces filters in the WHERE clause instead of scanning and filtering.
  CREATE INDEX IF NOT EXISTS space_filter_idx
    ON space(space_did, space_type, uri);

  CREATE TABLE IF NOT EXISTS space_member (
    space TEXT NOT NULL,
    did TEXT NOT NULL,
    can_read INTEGER NOT NULL DEFAULT 1,
    can_write INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (space, did)
  );

  CREATE TABLE IF NOT EXISTS space_record (
    space TEXT NOT NULL,
    collection TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT NOT NULL,
    value BLOB NOT NULL,
    repo_rev TEXT NOT NULL,
    indexed_at TEXT NOT NULL,
    PRIMARY KEY (space, collection, rkey)
  );
  CREATE INDEX IF NOT EXISTS space_record_rev_idx
    ON space_record(space, repo_rev);

  CREATE TABLE IF NOT EXISTS space_repo (
    space TEXT PRIMARY KEY,
    set_hash BLOB,
    rev TEXT
  );

  CREATE TABLE IF NOT EXISTS space_record_oplog (
    space TEXT NOT NULL,
    rev TEXT NOT NULL,
    idx INTEGER NOT NULL,
    action TEXT NOT NULL,
    collection TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT,
    prev TEXT,
    PRIMARY KEY (space, rev, idx)
  );

  -- rev is the writer's repo rev. space_rev is where the authority sequenced
  -- the writer's latest update across the whole space, and is what listRepos
  -- pages by.
  CREATE TABLE IF NOT EXISTS space_writer (
    space TEXT NOT NULL,
    did TEXT NOT NULL,
    rev TEXT NOT NULL,
    data BLOB NOT NULL,
    space_rev TEXT NOT NULL,
    PRIMARY KEY (space, did)
  );

  CREATE TABLE IF NOT EXISTS space_credential_recipient (
    space TEXT NOT NULL,
    service_did TEXT NOT NULL,
    service_endpoint TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    PRIMARY KEY (space, service_did)
  );

  -- Credentials an authority revoked early, by jti. A row outlives every
  -- credential it can match, since none lives longer than an hour.
  CREATE TABLE IF NOT EXISTS space_credential_revocation (
    space TEXT NOT NULL,
    jti TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    PRIMARY KEY (space, jti)
  );

  -- The latest write notice this repo host still owes a space's authority.
  -- One row per space: a newer write replaces an undelivered older one.
  CREATE TABLE IF NOT EXISTS space_notify_outbox (
    space TEXT PRIMARY KEY,
    repo_rev TEXT NOT NULL,
    data BLOB NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT NOT NULL,
    deadline_at TEXT NOT NULL
  );
  CREATE INDEX IF NOT EXISTS space_notify_outbox_due_idx
    ON space_notify_outbox(next_attempt_at);

  -- The notifySpaceDeleted this authority still owes each syncer registered
  -- for a space it deleted, until the registration expires.
  CREATE TABLE IF NOT EXISTS space_deleted_notify_outbox (
    space TEXT NOT NULL,
    service TEXT NOT NULL,
    service_endpoint TEXT NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT NOT NULL,
    deadline_at TEXT NOT NULL,
    PRIMARY KEY (space, service)
  );
  CREATE INDEX IF NOT EXISTS space_deleted_notify_outbox_due_idx
    ON space_deleted_notify_outbox(next_attempt_at);
`;

// Statements that bring a table created by an earlier version up to the shape
// above. Each one runs on its own and fails when its column is already there,
// so an adapter runs each inside a try/catch rather than the batch above.
//
// A space created under one policy keeps it for both reads and writes, and an
// existing member keeps both rights. That is the state each row described when
// one policy governed the space, and it is what the reference migration
// preserves.
export const SPACE_MIGRATIONS = [
  `ALTER TABLE space ADD COLUMN read_policy TEXT NOT NULL DEFAULT 'member-list'`,
  'ALTER TABLE space ADD COLUMN read_managing_app TEXT',
  `ALTER TABLE space ADD COLUMN write_policy TEXT NOT NULL DEFAULT 'member-list'`,
  'ALTER TABLE space ADD COLUMN write_managing_app TEXT',
  'UPDATE space SET read_policy = policy, read_managing_app = managing_app, write_policy = policy, write_managing_app = managing_app WHERE policy IS NOT NULL',
  'ALTER TABLE space_member ADD COLUMN can_read INTEGER NOT NULL DEFAULT 1',
  'ALTER TABLE space_member ADD COLUMN can_write INTEGER NOT NULL DEFAULT 1',
  // A writer recorded before space revisions existed is sequenced at its repo
  // rev. Both are TIDs, so every revision allocated afterwards is later.
  'ALTER TABLE space_writer ADD COLUMN space_rev TEXT',
  'UPDATE space_writer SET space_rev = rev WHERE space_rev IS NULL',
  'CREATE INDEX IF NOT EXISTS space_writer_rev_idx ON space_writer(space, space_rev)',
  // A commit's digest is its `data`.
  'ALTER TABLE space_writer RENAME COLUMN hash TO data',
  'ALTER TABLE space_notify_outbox RENAME COLUMN hash TO data',
  // A space is governed by one policy object. The six simplespace columns
  // above hold a space's configuration from before policies, and only these
  // statements read them. They convert it as the reference's migration 006
  // does. A managing app on either side makes a managedspace, which keeps no
  // member list and restricts no apps. Any other configuration makes a
  // memberspace.
  'ALTER TABLE space ADD COLUMN policy_json TEXT',
  // Before the conversion below, so it touches only the spaces it converts.
  `DELETE FROM space_member WHERE space IN (
     SELECT uri FROM space
     WHERE policy_json IS NULL AND is_owner = 1
       AND COALESCE(read_managing_app, write_managing_app) IS NOT NULL)`,
  `UPDATE space SET policy_json = CASE
     WHEN COALESCE(read_managing_app, write_managing_app) IS NOT NULL THEN
       json_object(
         '$type', 'com.atproto.managedspace.policy',
         'managingApp', COALESCE(read_managing_app, write_managing_app),
         'publishPolicy', json('false'))
     ELSE json_patch(
       json_object(
         '$type', 'com.atproto.memberspace.policy',
         'publicRead', json(CASE WHEN read_policy = 'public' THEN 'true' ELSE 'false' END),
         'publicWrite', json(CASE WHEN write_policy = 'public' THEN 'true' ELSE 'false' END),
         'publishPolicy', json('false'),
         'publishMembers', json('false')),
       CASE WHEN app_access_type = 'allowList'
         THEN json_object('allowedApps', json(app_allowed))
         ELSE '{}' END)
     END
   WHERE policy_json IS NULL AND is_owner = 1`,
];

// A record's position in its repo. The reference orders by the record's AT-URI,
// whose tail this is; the space and the writer are the same for every row here.
const SORT_KEY = "(collection || '/' || rkey)";

/**
 * Build the listRecords query. Four of its clauses vary per request, so it
 * cannot be one prepared statement, and SQLite takes neither a column list nor
 * a sort direction as a bound parameter.
 *
 * @param {string} space
 * @param {Object} opts
 * @param {string|null} [opts.collection] - all collections when absent
 * @param {string|null} [opts.cursor] - the sort key the last page ended on
 * @param {number} opts.limit
 * @param {boolean} [opts.reverse] - ascending rather than descending
 * @param {boolean} [opts.excludeValues] - metadata only
 * @returns {{sql: string, params: Array<string|number>}}
 */
export function spaceRecordsQuery(
  space,
  { collection, cursor, limit, reverse, excludeValues },
) {
  const columns = excludeValues
    ? 'collection, rkey, cid, repo_rev, indexed_at'
    : 'collection, rkey, cid, value, repo_rev, indexed_at';
  const where = ['space = ?'];
  /** @type {Array<string|number>} */
  const params = [space];
  if (collection) {
    where.push('collection = ?');
    params.push(collection);
  }
  if (cursor) {
    where.push(`${SORT_KEY} ${reverse ? '>' : '<'} ?`);
    params.push(cursor);
  }
  params.push(limit);
  return {
    sql: `SELECT ${columns} FROM space_record WHERE ${where.join(' AND ')} ORDER BY ${SORT_KEY} ${reverse ? 'ASC' : 'DESC'} LIMIT ?`,
    params,
  };
}

/**
 * The cursor a listRecords page ends on.
 * @param {{collection: string, rkey: string}} row
 * @returns {string}
 */
export function spaceRecordCursor(row) {
  return `${row.collection}/${row.rkey}`;
}