chadtmiller.com/pds.js
An atproto PDS in JavaScript, for Cloudflare Workers and Node
0
0
// @pdsjs/storage-sqlite - SQLite storage adapter for Node.js
import { APP_PASSWORD_SCHEMA_SQL } from '@pdsjs/core/app-password';
import { SESSION_SCHEMA_SQL } from '@pdsjs/core/auth';
import {
BLOB_FACETS_APPS_SQL,
BLOB_FACETS_KINDS_SQL,
BLOB_FACETS_TOTAL_SQL,
BLOB_FACETS_UNUSED_SQL,
blobListCursor,
buildBlobListQuery,
} from '@pdsjs/core/blob-sql';
import { PASSKEY_SCHEMA_SQL } from '@pdsjs/core/webauthn';
export { createSpaceStorage } from './space.js';
/** @typedef {{name: string, privileged: number, created_at: string}} AppPasswordRow */
/**
* @param {AppPasswordRow} row
* @returns {import('@pdsjs/core/ports').AppPasswordRecord}
*/
function toAppPassword(row) {
return {
name: row.name,
privileged: row.privileged === 1,
createdAt: row.created_at,
};
}
// ============================================================================
// Row type definitions for SQLite queries
// ============================================================================
/** @typedef {{cid: string, value: Uint8Array}} RecordResult */
/** @typedef {{uri: string, cid: string, rkey: string, value: Uint8Array|Buffer}} RecordRow */
/** @typedef {{collection: string, rkey: string, cid: string}} RecordKeyRow */
/** @typedef {{data: Uint8Array}} BlockRow */
/** @typedef {{seq: number, cid: string, rev: string}} CommitRow */
/** @typedef {{seq: number, did: string, commit_cid: string, evt: Uint8Array}} EventRow */
/** @typedef {{mime_type: string, size: number, created_at: number}} BlobMetaRow */
/** @typedef {{cid: string, mime_type: string, size: number, created_at: number, uris: string|null, time_ms: number, time_known: number}} BlobDetailRow */
/** @typedef {{count: number, bytes: number}} BlobBucketRow */
/** @typedef {{kind: import('@pdsjs/core/ports').BlobKind, count: number, bytes: number}} BlobKindRow */
/** @typedef {{authority: string, count: number, bytes: number}} BlobAppRow */
/** @typedef {{cid: string}} CidRow */
/** @typedef {{value: string}} MetadataStringRow */
/** @typedef {{value: Buffer}} MetadataBlobRow */
/** @typedef {{data: string, expires_at: number}} OAuthRequestRow */
/** @typedef {{data: string}} OAuthTokenRow */
/** @typedef {{credential_id: string, did: string, name: string, public_key: string, algorithm: number, sign_count: number, created_at: string, last_used_at: string|null}} PasskeyRow */
/** @typedef {{jti: string, did: string, label: string, scope: string, user_agent: string|null, created_at: string, refreshed_at: string|null, expires_at: number|null, next_jti: string|null}} SessionRow */
/**
* @param {SessionRow} row
* @returns {import('@pdsjs/core/ports').SessionRecord}
*/
function toSessionRecord(row) {
return {
jti: row.jti,
did: row.did,
label: row.label,
scope: row.scope,
userAgent: row.user_agent,
createdAt: row.created_at,
refreshedAt: row.refreshed_at,
expiresAt: row.expires_at ?? undefined,
nextJti: row.next_jti ?? null,
};
}
/**
* @param {PasskeyRow} row
* @returns {import('@pdsjs/core/ports').PasskeyRecord}
*/
function toPasskeyRecord(row) {
return {
credentialId: row.credential_id,
did: row.did,
name: row.name,
publicKey: row.public_key,
algorithm: row.algorithm,
signCount: row.sign_count,
createdAt: row.created_at,
lastUsedAt: row.last_used_at,
};
}
/** @typedef {{token_id: string, data: string}} OAuthTokenListRow */
// ============================================================================
// Storage adapter
// ============================================================================
/**
* Create storage adapter for better-sqlite3 (Node.js)
* @param {import('./driver.js').SQLiteDatabase} db
* @returns {import('@pdsjs/core/ports').ActorStoragePort}
*/
export function createActorStorage(db) {
// Initialize schema
db.exec(`
CREATE TABLE IF NOT EXISTS metadata (
key TEXT PRIMARY KEY,
value BLOB
);
CREATE TABLE IF NOT EXISTS blocks (
cid TEXT PRIMARY KEY,
data BLOB
);
CREATE TABLE IF NOT EXISTS records (
uri TEXT PRIMARY KEY,
cid TEXT,
collection TEXT,
rkey TEXT,
value BLOB
);
CREATE INDEX IF NOT EXISTS idx_records_collection ON records(collection, rkey);
CREATE INDEX IF NOT EXISTS idx_records_cid ON records(cid);
CREATE TABLE IF NOT EXISTS commits (
seq INTEGER PRIMARY KEY,
cid TEXT,
rev TEXT,
prev TEXT
);
CREATE TABLE IF NOT EXISTS seq_events (
seq INTEGER PRIMARY KEY,
did TEXT,
commit_cid TEXT,
evt BLOB
);
CREATE TABLE IF NOT EXISTS blobs (
cid TEXT PRIMARY KEY,
mime_type TEXT,
size INTEGER,
created_at INTEGER
);
CREATE TABLE IF NOT EXISTS record_blobs (
blob_cid TEXT,
record_uri TEXT,
record_time INTEGER,
PRIMARY KEY (blob_cid, record_uri)
);
`);
// Databases created before record_time existed gain the column in place.
try {
db.exec('ALTER TABLE record_blobs ADD COLUMN record_time INTEGER');
} catch {
// Already present
}
const stmts = {
getRecord: db.prepare('SELECT cid, value FROM records WHERE uri = ?'),
listRecords: db.prepare(
'SELECT uri, cid, rkey, value FROM records WHERE collection = ? ORDER BY rkey LIMIT ?',
),
listRecordsAfter: db.prepare(
'SELECT uri, cid, rkey, value FROM records WHERE collection = ? AND rkey > ? ORDER BY rkey LIMIT ?',
),
listRecordsDesc: db.prepare(
'SELECT uri, cid, rkey, value FROM records WHERE collection = ? ORDER BY rkey DESC LIMIT ?',
),
listRecordsBefore: db.prepare(
'SELECT uri, cid, rkey, value FROM records WHERE collection = ? AND rkey < ? ORDER BY rkey DESC LIMIT ?',
),
listAllRecords: db.prepare(
"SELECT collection, rkey, cid FROM records ORDER BY (collection || '/' || rkey)",
),
putRecord: db.prepare(
'INSERT OR REPLACE INTO records (uri, cid, collection, rkey, value) VALUES (?, ?, ?, ?, ?)',
),
deleteRecord: db.prepare('DELETE FROM records WHERE uri = ?'),
deleteRecordBlobs: db.prepare(
'DELETE FROM record_blobs WHERE record_uri = ?',
),
getBlock: db.prepare('SELECT data FROM blocks WHERE cid = ?'),
putBlock: db.prepare(
'INSERT OR IGNORE INTO blocks (cid, data) VALUES (?, ?)',
),
deleteBlock: db.prepare('DELETE FROM blocks WHERE cid = ?'),
listBlocks: db.prepare('SELECT cid, data FROM blocks ORDER BY cid LIMIT ?'),
listBlocksAfter: db.prepare(
'SELECT cid, data FROM blocks WHERE cid > ? ORDER BY cid LIMIT ?',
),
recordWithCid: db.prepare('SELECT 1 FROM records WHERE cid = ? LIMIT 1'),
getLatestCommit: db.prepare(
'SELECT seq, cid, rev FROM commits ORDER BY seq DESC LIMIT 1',
),
putCommit: db.prepare(
'INSERT INTO commits (seq, cid, rev, prev) VALUES (?, ?, ?, ?)',
),
getEvents: db.prepare(
'SELECT seq, did, commit_cid, evt FROM seq_events ORDER BY seq LIMIT ?',
),
getEventsAfter: db.prepare(
'SELECT seq, did, commit_cid, evt FROM seq_events WHERE seq > ? ORDER BY seq LIMIT ?',
),
getEventsBefore: db.prepare(
'SELECT seq, evt FROM seq_events WHERE seq < ? ORDER BY seq DESC LIMIT ?',
),
putEvent: db.prepare(
'INSERT INTO seq_events (seq, did, commit_cid, evt) VALUES (?, ?, ?, ?)',
),
getBlob: db.prepare(
'SELECT mime_type, size, created_at FROM blobs WHERE cid = ?',
),
putBlob: db.prepare(
'INSERT OR REPLACE INTO blobs (cid, mime_type, size, created_at) VALUES (?, ?, ?, ?)',
),
deleteBlob: db.prepare('DELETE FROM blobs WHERE cid = ?'),
listBlobs: db.prepare('SELECT cid FROM blobs ORDER BY cid LIMIT ?'),
listBlobsAfter: db.prepare(
'SELECT cid FROM blobs WHERE cid > ? ORDER BY cid LIMIT ?',
),
blobFacetsTotal: db.prepare(BLOB_FACETS_TOTAL_SQL),
blobFacetsUnused: db.prepare(BLOB_FACETS_UNUSED_SQL),
blobFacetsKinds: db.prepare(BLOB_FACETS_KINDS_SQL),
blobFacetsApps: db.prepare(BLOB_FACETS_APPS_SQL),
getOrphanedBlobs: db.prepare(
'SELECT b.cid FROM blobs b LEFT JOIN record_blobs rb ON b.cid = rb.blob_cid WHERE rb.blob_cid IS NULL AND b.created_at < ?',
),
linkBlobToRecord: db.prepare(
'INSERT OR REPLACE INTO record_blobs (blob_cid, record_uri, record_time) VALUES (?, ?, ?)',
),
unlinkBlobsFromRecord: db.prepare(
'DELETE FROM record_blobs WHERE record_uri = ?',
),
listBlobRecordUris: db.prepare(
'SELECT record_uri FROM record_blobs WHERE blob_cid = ?',
),
getMetadata: db.prepare('SELECT value FROM metadata WHERE key = ?'),
setMetadata: db.prepare(
'INSERT OR REPLACE INTO metadata (key, value) VALUES (?, ?)',
),
};
return {
async getRecord(uri) {
const row = /** @type {RecordResult|undefined} */ (
stmts.getRecord.get(uri)
);
return row ? { cid: row.cid, value: row.value } : null;
},
async listRecords(collection, cursor, limit, reverse = false) {
const stmt = reverse
? cursor
? stmts.listRecordsBefore
: stmts.listRecordsDesc
: cursor
? stmts.listRecordsAfter
: stmts.listRecords;
const rows = /** @type {RecordRow[]} */ (
cursor
? stmt.all(collection, cursor, limit)
: stmt.all(collection, limit)
);
return {
records: rows.map((r) => ({ uri: r.uri, cid: r.cid, value: r.value })),
cursor: rows.length === limit ? rows[rows.length - 1].rkey : null,
};
},
async listAllRecords() {
const rows = /** @type {RecordKeyRow[]} */ (stmts.listAllRecords.all());
return rows.map((r) => ({
key: `${r.collection}/${r.rkey}`,
cid: r.cid,
}));
},
async putRecord(uri, cid, collection, rkey, value) {
stmts.putRecord.run(uri, cid, collection, rkey, value);
},
async deleteRecord(uri) {
stmts.deleteRecordBlobs.run(uri);
stmts.deleteRecord.run(uri);
},
async getBlock(cid) {
const row = /** @type {BlockRow|undefined} */ (stmts.getBlock.get(cid));
return row ? row.data : null;
},
async putBlock(cid, data) {
stmts.putBlock.run(cid, data);
},
async deleteBlocks(cids) {
for (const cid of cids) stmts.deleteBlock.run(cid);
},
async listBlocks(cursor, limit) {
const rows = /** @type {Array<{cid: string, data: Uint8Array}>} */ (
cursor
? stmts.listBlocksAfter.all(cursor, limit)
: stmts.listBlocks.all(limit)
);
const last = rows.length === limit ? rows[limit - 1].cid : null;
return { blocks: rows, cursor: last };
},
async recordCidsAmong(cids) {
return cids.filter((cid) => stmts.recordWithCid.get(cid));
},
async getBlocksCollected() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('blocksCollected')
);
return row?.value === '1';
},
async setBlocksCollected(collected) {
stmts.setMetadata.run('blocksCollected', collected ? '1' : '0');
},
async getLatestCommit() {
const row = /** @type {CommitRow|undefined} */ (
stmts.getLatestCommit.get()
);
return row || null;
},
async putCommit(seq, cid, rev, prev) {
stmts.putCommit.run(seq, cid, rev, prev);
},
async getEvents(cursor, limit) {
const rows = /** @type {EventRow[]} */ (
cursor
? stmts.getEventsAfter.all(cursor, limit)
: stmts.getEvents.all(limit)
);
return {
events: rows.map((r) => ({
seq: r.seq,
commit_cid: r.commit_cid,
evt: r.evt,
})),
cursor: rows.length > 0 ? rows[rows.length - 1].seq : cursor,
};
},
async putEvent(seq, did, commitCid, evt) {
stmts.putEvent.run(seq, did, commitCid, evt);
},
async getEventsBefore(before, limit) {
const rows = /** @type {{seq: number, evt: Uint8Array}[]} */ (
stmts.getEventsBefore.all(before, limit)
);
return rows.map((r) => ({ seq: r.seq, evt: r.evt }));
},
async getLatestSeq() {
// Max across both sequenced tables: a crash between putCommit and
// putEvent leaves a commit whose seq the events table never saw, and
// counting only events would hand that seq out again forever.
const row = /** @type {{seq: number|null}|undefined} */ (
db
.prepare(
'SELECT MAX(s) as seq FROM (SELECT MAX(seq) as s FROM seq_events UNION ALL SELECT MAX(seq) as s FROM commits)',
)
.get()
);
return row?.seq ?? 0;
},
async getBlob(cid) {
const row = /** @type {BlobMetaRow|undefined} */ (stmts.getBlob.get(cid));
return row
? { mimeType: row.mime_type, size: row.size, createdAt: row.created_at }
: null;
},
async putBlob(cid, mimeType, size) {
stmts.putBlob.run(cid, mimeType, size, Date.now());
},
async deleteBlob(cid) {
stmts.deleteBlob.run(cid);
},
async listBlobs(cursor, limit) {
const rows = /** @type {CidRow[]} */ (
cursor
? stmts.listBlobsAfter.all(cursor, limit)
: stmts.listBlobs.all(limit)
);
return {
cids: rows.map((r) => r.cid),
cursor: rows.length === limit ? rows[rows.length - 1].cid : null,
};
},
async listBlobDetails(cursor, limit, options = {}) {
const { sql, params } = buildBlobListQuery(cursor, limit, options);
const rows = /** @type {BlobDetailRow[]} */ (
db.prepare(sql).all(...params)
);
const blobs = rows.map((r) => ({
cid: r.cid,
mimeType: r.mime_type,
size: r.size,
createdAt: r.created_at,
time: r.time_ms,
timeFromRecord: r.time_known === 1,
records: r.uris ? r.uris.split(',') : [],
}));
return {
blobs,
cursor:
blobs.length === limit
? blobListCursor(blobs[blobs.length - 1], options)
: null,
};
},
async getBlobFacets() {
const total = /** @type {BlobBucketRow} */ (stmts.blobFacetsTotal.get());
const unused = /** @type {BlobBucketRow} */ (
stmts.blobFacetsUnused.get()
);
const kinds = /** @type {BlobKindRow[]} */ (stmts.blobFacetsKinds.all());
const apps = /** @type {BlobAppRow[]} */ (stmts.blobFacetsApps.all());
return { total, unused, kinds, apps };
},
async getOrphanedBlobs(olderThan) {
const rows = /** @type {CidRow[]} */ (
stmts.getOrphanedBlobs.all(olderThan)
);
return rows.map((r) => r.cid);
},
async linkBlobToRecord(blobCid, recordUri, recordTime = null) {
stmts.linkBlobToRecord.run(blobCid, recordUri, recordTime);
},
async unlinkBlobsFromRecord(recordUri) {
stmts.unlinkBlobsFromRecord.run(recordUri);
},
async listBlobRecordUris(blobCid) {
const rows = /** @type {{record_uri: string}[]} */ (
stmts.listBlobRecordUris.all(blobCid)
);
return rows.map((row) => row.record_uri);
},
async getDid() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('did')
);
return row ? row.value : null;
},
async setDid(did) {
stmts.setMetadata.run('did', did);
},
async getPrivateKey() {
const row = /** @type {MetadataBlobRow|undefined} */ (
stmts.getMetadata.get('privateKey')
);
return row ? new Uint8Array(row.value) : null;
},
async setPrivateKey(key) {
stmts.setMetadata.run('privateKey', key);
},
async getKemKey() {
const row = /** @type {MetadataBlobRow|undefined} */ (
stmts.getMetadata.get('kemKey')
);
return row ? new Uint8Array(row.value) : null;
},
async setKemKey(key) {
stmts.setMetadata.run('kemKey', key);
},
async getHandle() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('handle')
);
return row ? row.value : null;
},
async getEmail() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('email')
);
if (!row) return null;
try {
return JSON.parse(row.value);
} catch {
return null;
}
},
async setEmail(state) {
stmts.setMetadata.run('email', JSON.stringify(state));
},
async getAccountPassword() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('accountPassword')
);
if (!row) return null;
try {
return JSON.parse(row.value);
} catch {
return null;
}
},
async setAccountPassword(state) {
stmts.setMetadata.run('accountPassword', JSON.stringify(state));
},
async getPlcToken() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('plcToken')
);
if (!row) return null;
try {
return JSON.parse(row.value);
} catch {
return null;
}
},
async setPlcToken(token) {
stmts.setMetadata.run('plcToken', JSON.stringify(token));
},
async getAccountStatus() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('accountStatus')
);
return row ? row.value : null;
},
async setAccountStatus(status) {
stmts.setMetadata.run('accountStatus', status);
},
async setHandle(handle) {
stmts.setMetadata.run('handle', handle);
},
// Preferences storage
async getPreferences() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('preferences')
);
return row ? JSON.parse(row.value) : [];
},
async setPreferences(preferences) {
stmts.setMetadata.run('preferences', JSON.stringify(preferences));
},
// Backup settings and run history, one JSON document
async getBackupState() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('backupState')
);
if (!row) return null;
try {
return JSON.parse(row.value);
} catch {
return null;
}
},
async setBackupState(state) {
stmts.setMetadata.run('backupState', JSON.stringify(state));
},
// The account app's own preferences, one JSON document
async getAccountUiState() {
const row = /** @type {MetadataStringRow|undefined} */ (
stmts.getMetadata.get('accountUiState')
);
if (!row) return null;
try {
return JSON.parse(row.value);
} catch {
return null;
}
},
async setAccountUiState(state) {
stmts.setMetadata.run('accountUiState', JSON.stringify(state));
},
};
}
// ============================================================================
// Shared storage adapter for Node.js
// ============================================================================
/** @typedef {{did: string, handle: string|null, created_at: number}} ActorRow */
/**
* Create shared storage adapter for better-sqlite3 (Node.js)
* @param {import('./driver.js').SQLiteDatabase} db
* @returns {import('@pdsjs/core/ports').SharedStoragePort}
*/
export function createSharedStorage(db) {
// Initialize schema
db.exec(`
CREATE TABLE IF NOT EXISTS actors (
did TEXT PRIMARY KEY,
handle TEXT,
created_at INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS actors_handle_lower_idx ON actors(lower(handle));
CREATE TABLE IF NOT EXISTS oauth_requests (
request_uri TEXT PRIMARY KEY,
data TEXT,
expires_at INTEGER
);
CREATE INDEX IF NOT EXISTS oauth_requests_expires_idx ON oauth_requests(expires_at);
CREATE TABLE IF NOT EXISTS oauth_tokens (
token_id TEXT PRIMARY KEY,
did TEXT NOT NULL,
data TEXT
);
CREATE INDEX IF NOT EXISTS oauth_tokens_did_idx ON oauth_tokens(did);
CREATE TABLE IF NOT EXISTS dpop_jtis (
jti TEXT PRIMARY KEY,
expires_at INTEGER
);
CREATE INDEX IF NOT EXISTS dpop_jtis_expires_idx ON dpop_jtis(expires_at);
`);
db.exec(APP_PASSWORD_SCHEMA_SQL);
db.exec(PASSKEY_SCHEMA_SQL);
db.exec(SESSION_SCHEMA_SQL);
// Databases created before sessions carried an expiry lack the column; add
// it once. SQLite has no ADD COLUMN IF NOT EXISTS, so a re-run just throws.
try {
db.exec('ALTER TABLE sessions ADD COLUMN expires_at INTEGER');
} catch {
// Column already present.
}
// Likewise for the successor a rotation names when it spends a token.
try {
db.exec('ALTER TABLE sessions ADD COLUMN next_jti TEXT');
} catch {
// Column already present.
}
const stmts = {
getActor: db.prepare(
'SELECT did, handle, created_at FROM actors WHERE did = ?',
),
resolveHandle: db.prepare(
'SELECT did FROM actors WHERE lower(handle) = lower(?)',
),
putActor: db.prepare(
'INSERT OR REPLACE INTO actors (did, handle, created_at) VALUES (?, ?, ?)',
),
deleteActor: db.prepare('DELETE FROM actors WHERE did = ?'),
getOAuthRequest: db.prepare(
'SELECT data, expires_at FROM oauth_requests WHERE request_uri = ?',
),
putOAuthRequest: db.prepare(
'INSERT OR REPLACE INTO oauth_requests (request_uri, data, expires_at) VALUES (?, ?, ?)',
),
deleteOAuthRequest: db.prepare(
'DELETE FROM oauth_requests WHERE request_uri = ?',
),
getOAuthToken: db.prepare(
'SELECT data FROM oauth_tokens WHERE token_id = ?',
),
putOAuthToken: db.prepare(
'INSERT OR REPLACE INTO oauth_tokens (token_id, did, data) VALUES (?, ?, ?)',
),
deleteOAuthToken: db.prepare('DELETE FROM oauth_tokens WHERE token_id = ?'),
listOAuthTokensByDid: db.prepare(
'SELECT token_id, data FROM oauth_tokens WHERE did = ?',
),
storeDpopJti: db.prepare(
'INSERT OR IGNORE INTO dpop_jtis (jti, expires_at) VALUES (?, ?)',
),
cleanupExpiredDpopJtis: db.prepare(
'DELETE FROM dpop_jtis WHERE expires_at < ?',
),
putAppPassword: db.prepare(
'INSERT OR IGNORE INTO app_passwords (did, name, hash, privileged, created_at) VALUES (?, ?, ?, ?, ?)',
),
getAppPasswordByHash: db.prepare(
'SELECT name, privileged, created_at FROM app_passwords WHERE did = ? AND hash = ?',
),
listAppPasswords: db.prepare(
'SELECT name, privileged, created_at FROM app_passwords WHERE did = ? ORDER BY created_at',
),
putSession: db.prepare(
'INSERT OR REPLACE INTO sessions (jti, did, label, scope, user_agent, created_at, refreshed_at, expires_at, next_jti) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
),
getSession: db.prepare('SELECT * FROM sessions WHERE jti = ?'),
listSessions: db.prepare(
'SELECT * FROM sessions WHERE did = ? ORDER BY created_at',
),
deleteSession: db.prepare('DELETE FROM sessions WHERE jti = ?'),
// Conditional on the successor, so that of two rotations racing the same
// token only the first takes effect. Statements run to completion here, so
// the test and the write cannot be separated.
spendSession: db.prepare(
'UPDATE sessions SET expires_at = ?, next_jti = ? WHERE jti = ? AND (next_jti IS NULL OR next_jti = ?)',
),
putPasskey: db.prepare(
'INSERT OR IGNORE INTO passkeys (credential_id, did, name, public_key, algorithm, sign_count, created_at, last_used_at) VALUES (?, ?, ?, ?, ?, 0, ?, NULL)',
),
getPasskey: db.prepare('SELECT * FROM passkeys WHERE credential_id = ?'),
listPasskeys: db.prepare(
'SELECT * FROM passkeys WHERE did = ? ORDER BY created_at',
),
deletePasskey: db.prepare(
'DELETE FROM passkeys WHERE did = ? AND credential_id = ?',
),
touchPasskey: db.prepare(
'UPDATE passkeys SET sign_count = ?, last_used_at = ? WHERE credential_id = ?',
),
deleteAppPassword: db.prepare(
'DELETE FROM app_passwords WHERE did = ? AND name = ?',
),
};
return {
async getActor(did) {
const row = /** @type {ActorRow|undefined} */ (stmts.getActor.get(did));
return row
? { did: row.did, handle: row.handle, createdAt: row.created_at }
: null;
},
async resolveHandle(handle) {
const row = /** @type {{did: string}|undefined} */ (
stmts.resolveHandle.get(handle)
);
return row ? row.did : null;
},
async putActor(did, handle, createdAt) {
stmts.putActor.run(did, handle, createdAt);
},
async deleteActor(did) {
stmts.deleteActor.run(did);
},
async getOAuthRequest(requestUri) {
const row = /** @type {OAuthRequestRow|undefined} */ (
stmts.getOAuthRequest.get(requestUri)
);
if (!row) return null;
if (row.expires_at < Date.now()) {
stmts.deleteOAuthRequest.run(requestUri);
return null;
}
try {
return JSON.parse(row.data);
} catch {
stmts.deleteOAuthRequest.run(requestUri);
return null;
}
},
async putOAuthRequest(requestUri, data, expiresAt) {
stmts.putOAuthRequest.run(requestUri, JSON.stringify(data), expiresAt);
},
async deleteOAuthRequest(requestUri) {
stmts.deleteOAuthRequest.run(requestUri);
},
async getOAuthToken(tokenId) {
const row = /** @type {OAuthTokenRow|undefined} */ (
stmts.getOAuthToken.get(tokenId)
);
if (!row) return null;
try {
return JSON.parse(row.data);
} catch {
stmts.deleteOAuthToken.run(tokenId);
return null;
}
},
async putOAuthToken(tokenId, data) {
const tokenData = /** @type {{did: string}} */ (data);
stmts.putOAuthToken.run(tokenId, tokenData.did, JSON.stringify(data));
},
async deleteOAuthToken(tokenId) {
stmts.deleteOAuthToken.run(tokenId);
},
async putSession(session) {
stmts.putSession.run(
session.jti,
session.did,
session.label,
session.scope,
session.userAgent,
session.createdAt,
session.refreshedAt,
session.expiresAt ?? null,
session.nextJti ?? null,
);
},
async getSession(jti) {
const row = /** @type {SessionRow|undefined} */ (
stmts.getSession.get(jti)
);
return row ? toSessionRecord(row) : null;
},
async listSessions(did) {
const rows = /** @type {SessionRow[]} */ (stmts.listSessions.all(did));
return rows.map(toSessionRecord);
},
async deleteSession(jti) {
return stmts.deleteSession.run(jti).changes > 0;
},
async spendSession(jti, expiresAt, nextJti) {
return (
stmts.spendSession.run(expiresAt, nextJti, jti, nextJti).changes > 0
);
},
async putPasskey(record) {
const result = stmts.putPasskey.run(
record.credentialId,
record.did,
record.name,
record.publicKey,
record.algorithm,
record.createdAt,
);
return result.changes > 0;
},
async getPasskey(credentialId) {
const row = /** @type {PasskeyRow|undefined} */ (
stmts.getPasskey.get(credentialId)
);
return row ? toPasskeyRecord(row) : null;
},
async listPasskeys(did) {
const rows = /** @type {PasskeyRow[]} */ (stmts.listPasskeys.all(did));
return rows.map(toPasskeyRecord);
},
async deletePasskey(did, credentialId) {
const result = stmts.deletePasskey.run(did, credentialId);
return result.changes > 0;
},
async touchPasskey(credentialId, signCount, lastUsedAt) {
stmts.touchPasskey.run(signCount, lastUsedAt, credentialId);
},
async listOAuthTokensByDid(did) {
const rows = /** @type {OAuthTokenListRow[]} */ (
stmts.listOAuthTokensByDid.all(did)
);
return rows
.map((r) => {
try {
// The row key travels with the data so a caller can revoke a
// single session without a second lookup.
return { ...JSON.parse(r.data), tokenId: r.token_id };
} catch {
return null;
}
})
.filter(Boolean);
},
async checkAndStoreDpopJti(jti, expiresAt) {
const result = stmts.storeDpopJti.run(jti, expiresAt);
return result.changes > 0;
},
async cleanupExpiredDpopJtis() {
stmts.cleanupExpiredDpopJtis.run(Date.now());
},
async putAppPassword(did, name, hash, privileged, createdAt) {
// INSERT OR IGNORE rather than REPLACE: reusing a name must not
// silently invalidate the password already issued under it.
const result = stmts.putAppPassword.run(
did,
name,
hash,
privileged ? 1 : 0,
createdAt,
);
return Number(result.changes) > 0;
},
async getAppPasswordByHash(did, hash) {
const row = /** @type {AppPasswordRow|undefined} */ (
stmts.getAppPasswordByHash.get(did, hash)
);
return row ? toAppPassword(row) : null;
},
async listAppPasswords(did) {
const rows = /** @type {AppPasswordRow[]} */ (
stmts.listAppPasswords.all(did)
);
return rows.map(toAppPassword);
},
async deleteAppPassword(did, name) {
const result = stmts.deleteAppPassword.run(did, name);
return Number(result.changes) > 0;
},
};
}