Morning @Ritesh_Solanke ! Sure, here’s the actual migrations that create the objects tables:
await knex.schema.createTable( 'objects', table => {
table.string( 'id' ).primary( )
table.string( 'speckleType', 1024 ).defaultTo( 'Base' ).notNullable( )
table.integer( 'totalChildrenCount' )
table.jsonb( 'totalChildrenCountByDepth' )
table.timestamp( 'createdAt' ).defaultTo( knex.fn.now( ) )
table.jsonb( 'data' )
} )
// Closure table for tracking the nesting relationships of objects.
// Note: the usecase optimised for is that when we request an object, we either:
// a) interactively request/query for its subchildren (sequentially)
// or
// b) we want all of it!
await knex.schema.createTable( 'object_children_closure', table => {
table.string( 'parent' ).notNullable( ).index( )
table.string( 'child' ).notNullable( ).index( )
table.integer( 'minDepth' ).defaultTo( 1 ).notNullable( ).index( )
table.unique( [ 'parent', 'child' ], 'obj_parent_child_index' )
table.index( [ 'parent', 'minDepth' ], 'full_pcd_index' )
} )
The decomposition logic explains more in depth how things are structured, so it can be considered “required” reading to understand the approach in detail.