search

cybersemics / em

A beautiful, minimalistic note-taking app for personal sensemaking.
Other
286 stars 120 forks source link

YJS + SQL #1718

Closed raineorshine closed 12 months ago

raineorshine commented 1 year ago

Currently the server persists YJS Docs to leveldb on the local file system. This is a poor solution as the DigitalOcean file system is ephemeral.

(As a workaround, there is a backup script that uploads a backup to DigitalOcean Spaces [an S3-compatible endpoint]. Running npm run predeploy before deploying to DigitalOcean will preserve the database.)

Unfortunately there is no readily available Postgres plugin for YJS, and indeed the founder has even stated that Postgres is not a good target to store incremental updates. However that does not leave is with a lot of options for reliable server-side persistence.

HocusPocus Database Extensions apparently work fine with Postgres. Perhaps they perform the throttling and update merging necessary to make it performant.

This will work for loading Thought and Lexeme docs, but there may need to be some custom logic for the permissions doc, which must be loaded before the server starts as it is used for authentication. This requires querying the database outside of HocusPocus hooks.

Resources

Notes

/*
DB Schema:

items {
  id: serial/bigserial
  docname [indexed]
  update: binary
}

*/

interface DBUpdate {
  id: string;
  docname: string;
  update: Uint8Array;
}

const get = (name: string): DBUpdate[] =>
  knex<DBUpdate>('items').where('docname', name).orderBy('id')

const compact = async () => {
  const updates: DBUpdate[] = await knex<DBUpdate>('items').where('docname', doc).orderBy('id')
  const doc = new Y.Doc()
  doc.transact(() => {
    for (const u of updates) {
      Y.applyUpdate(doc, u.update)
    }
  })
  const [mergedUpdates] = await Promise.all([
    // insert single update
    knex<DBUpdate>('items').insert({docname: doc.name, update: Y.encodeStateAsUpdate(doc)}).returning('*'),
    // delete other updates
    knex('items').where('docname', doc.name).whereIn('id', updates.map(({id}) => id)).delete()
  ])
}

const persist = async (name: string, update: Uint8Array): Promise<void> =>
  knex('items').insert({ docname: name, update })

/* y-leveldb */

// There is a lot of logic for state vectors that I don't yet understand.

Sample server logs:

# HOCUSPOCUS SERVER LOGS

# server start
PM2      | Stopping app:server id:0
PM2      | App [server:0] exited with code [0] via signal [SIGINT]
PM2      | pid=11328 msg=process killed
PM2      | App [server:0] starting in -fork mode-
0|server | Loading permissions...
0|server | syncLevelDb permissions
0|server | Permissions loaded
0|server |   Hocuspocus v2.0.6 running at:
0|server |   > HTTP: http://0.0.0.0:3001
0|server |   > WebSocket: ws://0.0.0.0:3001
PM2      | App [server:0] online

# authenticate and load permissions
0|server | onAuthenticate E7cfEQdGUJCqF/permissions
0|server | onLoadDocument E7cfEQdGUJCqF/permissions

# authenticate, load, and sync doclog and thoughts
0|server | onAuthenticate E7cfEQdGUJCqF/doclog
0|server | onAuthenticate E7cfEQdGUJCqF/t/__EM__
0|server | onAuthenticate E7cfEQdGUJCqF/l/27f1123ea6a031cea0b59c63d4be848f
0|server | onAuthenticate E7cfEQdGUJCqF/t/JHaVVROf16vfr
0|server | onAuthenticate E7cfEQdGUJCqF/l/5f91c388edbaa147a3faf14281100291
0|server | onAuthenticate E7cfEQdGUJCqF/t/llcWJO7sX0bvR
0|server | onAuthenticate E7cfEQdGUJCqF/l/cdbc8c25ca68d96971e57db3f38298ad
0|server | onAuthenticate E7cfEQdGUJCqF/t/PCfrCIoeww1De
0|server | onAuthenticate E7cfEQdGUJCqF/l/674313aceb22ee1d8fc128bf141df88d
0|server | onAuthenticate E7cfEQdGUJCqF/t/__ROOT__
0|server | onAuthenticate E7cfEQdGUJCqF/l/323720a6648d6a2272003af034bc823a
0|server | onAuthenticate E7cfEQdGUJCqF/t/nSQ46ps-ADgQ3
0|server | onAuthenticate E7cfEQdGUJCqF/t/t2mdAklSxTtjA
0|server | onAuthenticate E7cfEQdGUJCqF/l/85555565f6597889e6b53a48510e895a
0|server | onAuthenticate E7cfEQdGUJCqF/l/00000000000000000000000000000000
0|server | onAuthenticate E7cfEQdGUJCqF/t/7eVmHkG3GgdP-
0|server | onAuthenticate E7cfEQdGUJCqF/l/7a98a957b1d3d1eefa2e131e544e94e9
0|server | onLoadDocument E7cfEQdGUJCqF/doclog
0|server | onLoadDocument E7cfEQdGUJCqF/t/__EM__
0|server | syncLevelDb E7cfEQdGUJCqF/t/__EM__
0|server | onLoadDocument E7cfEQdGUJCqF/l/27f1123ea6a031cea0b59c63d4be848f
0|server | syncLevelDb E7cfEQdGUJCqF/l/27f1123ea6a031cea0b59c63d4be848f
0|server | onLoadDocument E7cfEQdGUJCqF/t/JHaVVROf16vfr
0|server | syncLevelDb E7cfEQdGUJCqF/t/JHaVVROf16vfr
0|server | onLoadDocument E7cfEQdGUJCqF/l/5f91c388edbaa147a3faf14281100291
0|server | syncLevelDb E7cfEQdGUJCqF/l/5f91c388edbaa147a3faf14281100291
0|server | onLoadDocument E7cfEQdGUJCqF/t/llcWJO7sX0bvR
0|server | syncLevelDb E7cfEQdGUJCqF/t/llcWJO7sX0bvR
0|server | onLoadDocument E7cfEQdGUJCqF/l/cdbc8c25ca68d96971e57db3f38298ad
0|server | syncLevelDb E7cfEQdGUJCqF/l/cdbc8c25ca68d96971e57db3f38298ad
0|server | onLoadDocument E7cfEQdGUJCqF/t/PCfrCIoeww1De
0|server | syncLevelDb E7cfEQdGUJCqF/t/PCfrCIoeww1De
0|server | onLoadDocument E7cfEQdGUJCqF/l/674313aceb22ee1d8fc128bf141df88d
0|server | syncLevelDb E7cfEQdGUJCqF/l/674313aceb22ee1d8fc128bf141df88d
0|server | onLoadDocument E7cfEQdGUJCqF/t/__ROOT__
0|server | syncLevelDb E7cfEQdGUJCqF/t/__ROOT__
0|server | onLoadDocument E7cfEQdGUJCqF/l/323720a6648d6a2272003af034bc823a
0|server | syncLevelDb E7cfEQdGUJCqF/l/323720a6648d6a2272003af034bc823a
0|server | onLoadDocument E7cfEQdGUJCqF/t/nSQ46ps-ADgQ3
0|server | syncLevelDb E7cfEQdGUJCqF/t/nSQ46ps-ADgQ3
0|server | onLoadDocument E7cfEQdGUJCqF/t/t2mdAklSxTtjA
0|server | syncLevelDb E7cfEQdGUJCqF/t/t2mdAklSxTtjA
0|server | onLoadDocument E7cfEQdGUJCqF/l/85555565f6597889e6b53a48510e895a
0|server | syncLevelDb E7cfEQdGUJCqF/l/85555565f6597889e6b53a48510e895a
0|server | onLoadDocument E7cfEQdGUJCqF/l/00000000000000000000000000000000
0|server | syncLevelDb E7cfEQdGUJCqF/l/00000000000000000000000000000000
0|server | onLoadDocument E7cfEQdGUJCqF/t/7eVmHkG3GgdP-
0|server | syncLevelDb E7cfEQdGUJCqF/t/7eVmHkG3GgdP-
0|server | onLoadDocument E7cfEQdGUJCqF/l/7a98a957b1d3d1eefa2e131e544e94e9
0|server | syncLevelDb E7cfEQdGUJCqF/l/7a98a957b1d3d1eefa2e131e544e94e9

0|server | (node:13140) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 close listeners added to [WebSocket]. Use emitter.setMaxListeners() to increase limit
0|server | (Use `node --trace-warnings ...` to show where the warning was created)
0|server | (node:13140) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 message listeners added to [WebSocket]. Use emitter.setMaxListeners() to increase limit
0|server | (node:13140) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 pong listeners added to [WebSocket]. Use emitter.setMaxListeners() to increase limit

0|server | permissionsClientMap.observe {
0|server |   tsid: 'E7cfEQdGUJCqF',
0|server |   type: 'permissions',
0|server |   permissionsDocName: 'E7cfEQdGUJCqF/permissions'
0|server | }

# edit thought

# doclog: delete lexeme
0|server | replicationController: next {
0|server |   action: 0,
0|server |   id: '00000000000000000000000000000000',
0|server |   type: 'lexeme',
0|server |   onLoadDocumentVars: {
0|server |     tsid: 'E7cfEQdGUJCqF',
0|server |     type: 'doclog',
0|server |     documentName: 'E7cfEQdGUJCqF/doclog'
0|server |   }
0|server | }
# doclog: update lexeme
0|server | replicationController: next {
0|server |   action: 1,
0|server |   id: '8458b53bda226293c034db1eb00b708f',
0|server |   type: 'lexeme',
0|server |   onLoadDocumentVars: {
0|server |     tsid: 'E7cfEQdGUJCqF',
0|server |     type: 'doclog',
0|server |     documentName: 'E7cfEQdGUJCqF/doclog'
0|server |   }
0|server | }
# authenticate, load, and sync lexeme
0|server | onAuthenticate E7cfEQdGUJCqF/l/8458b53bda226293c034db1eb00b708f
0|server | onLoadDocument E7cfEQdGUJCqF/l/8458b53bda226293c034db1eb00b708f
0|server | syncLevelDb E7cfEQdGUJCqF/l/8458b53bda226293c034db1eb00b708f

# replicationContrller: setItem
0|server | setItem {
0|server |   tsid: 'E7cfEQdGUJCqF',
0|server |   type: 'doclog',
0|server |   documentName: 'E7cfEQdGUJCqF/doclog',
0|server |   key: 'lexemeReplicationCursor',
0|server |   value: '2'
0|server | }
raineorshine commented 12 months ago

Added y-mongodb-provider.