Skip to main content

@idpass/data-collect-core / PostgresEntityStorageAdapter

Class: PostgresEntityStorageAdapter

Defined in: storage/PostgresEntityStorageAdapter.ts:147

PostgreSQL implementation of the EntityStorageAdapter for server-side entity persistence.

This adapter provides scalable, ACID-compliant entity storage using PostgreSQL with advanced features like JSONB support, full-text search, and multi-tenant isolation. It's designed for production server deployments requiring robust data persistence.

Key features:

  • ACID Transactions: Full PostgreSQL transaction support for data consistency
  • JSONB Storage: Efficient JSON storage with native PostgreSQL indexing and querying
  • Multi-Tenant Support: Complete tenant isolation using tenant_id partitioning
  • Advanced Search: Rich query capabilities with JSONB operators and regex support
  • Connection Pooling: Efficient connection management for high-concurrency scenarios
  • Scalable Architecture: Designed for production workloads with proper indexing
  • Duplicate Detection: Optimized duplicate tracking with compound primary keys

Architecture:

  • Uses PostgreSQL connection pooling for performance and scalability
  • Stores entities as JSONB documents for flexible schema evolution
  • Implements tenant isolation at the database level
  • Provides optimized queries with proper indexing strategies
  • Supports advanced search operations using PostgreSQL's JSONB capabilities

Database Schema:

-- Main entities table
CREATE TABLE entities (
  id TEXT,
  guid TEXT,
  initial JSONB,           -- Original entity state
  modified JSONB,          -- Current entity state
  sync_level TEXT,         -- Synchronization status
  last_updated TIMESTAMP,  -- Last modification timestamp
  tenant_id TEXT,          -- Tenant isolation
  PRIMARY KEY (id, tenant_id),
  UNIQUE (guid, tenant_id)
);

-- Potential duplicates tracking
CREATE TABLE potential_duplicates (
  entity_guid TEXT,
  duplicate_guid TEXT,
  tenant_id TEXT,
  PRIMARY KEY (entity_guid, duplicate_guid, tenant_id)
);

Examples​

Basic server setup:

const adapter = new PostgresEntityStorageAdapter(
  'postgresql://user:pass@localhost:5432/datacollect',
  'tenant-123'
);

await adapter.initialize();

// Save an entity
const entityPair: EntityPair = {
  guid: 'person-456',
  initial: originalEntity,
  modified: updatedEntity
};
await adapter.saveEntity(entityPair);

Advanced search with JSONB:

// Search for adults with complex criteria
const adults = await adapter.searchEntities([
  { "data.age": { $gte: 18 } },           // Age >= 18
  { "data.status": "active" },             // Exact string match
  { "data.name": { $regex: "john" } },     // Case-insensitive regex
  { "data.verified": true }                // Boolean match
]);

// Search with numeric ranges
const middleAged = await adapter.searchEntities([
  { "data.age": { $gte: 30 } },
  { "data.age": { $lte: 65 } }
]);

Multi-tenant deployment:

// Tenant-specific adapters
const orgAAdapter = new PostgresEntityStorageAdapter(connectionString, 'org-a');
const orgBAdapter = new PostgresEntityStorageAdapter(connectionString, 'org-b');

// Each adapter operates on isolated data
await orgAAdapter.initialize();
await orgBAdapter.initialize();

// Data is completely isolated between tenants
await orgAAdapter.saveEntity(entityForOrgA);
await orgBAdapter.saveEntity(entityForOrgB);

Production connection configuration:

const adapter = new PostgresEntityStorageAdapter(
  'postgresql://datacollect_user:secure_pass@db.example.com:5432/datacollect_prod?sslmode=require',
  process.env.TENANT_ID
);

// Initialize with proper error handling
try {
  await adapter.initialize();
  console.log('Database initialized successfully');
} catch (error) {
  console.error('Database initialization failed:', error);
  process.exit(1);
}

Implements​

Constructors​

Constructor​

new PostgresEntityStorageAdapter(connectionString, tenantId?): PostgresEntityStorageAdapter

Defined in: storage/PostgresEntityStorageAdapter.ts:173

Creates a new PostgresEntityStorageAdapter instance.

Parameters​

connectionString​

string

PostgreSQL connection string with credentials and database info.

tenantId?​

string

Optional tenant identifier for multi-tenant isolation (defaults to "default").

Returns​

PostgresEntityStorageAdapter

Example​

// Local development
const adapter = new PostgresEntityStorageAdapter(
  'postgresql://user:pass@localhost:5432/datacollect_dev'
);

// Production with tenant isolation
const prodAdapter = new PostgresEntityStorageAdapter(
  'postgresql://datacollect_user:secure_pass@db.prod.com:5432/datacollect?sslmode=require',
  'tenant-org-123'
);

Constructor​

new PostgresEntityStorageAdapter(pool, tenantId?): PostgresEntityStorageAdapter

Defined in: storage/PostgresEntityStorageAdapter.ts:181

Creates a PostgresEntityStorageAdapter using an existing Pool. When constructed this way, the adapter does NOT own the pool and will not close it.

Parameters​

pool​

Pool

An existing pg Pool to use for database operations.

tenantId?​

string

Optional tenant identifier for multi-tenant isolation (defaults to "default").

Returns​

PostgresEntityStorageAdapter

Methods​

getPool()​

getPool(): Pool

Defined in: storage/PostgresEntityStorageAdapter.ts:198

Returns the underlying pg Pool used by this adapter. Useful for creating transactional EDM stacks that share a single connection pool.

Returns​

Pool

setDrizzleInstance()​

setDrizzleInstance(db): void

Defined in: storage/PostgresEntityStorageAdapter.ts:209

Replaces the internal Drizzle database instance. Used to inject a Drizzle transaction object so that all operations on this adapter participate in an external transaction.

Parameters​

db​

DrizzleDatabase

A Drizzle database or transaction instance.

Returns​

void

closeConnection()​

closeConnection(): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:220

Closes all connections in the PostgreSQL connection pool. Only closes the pool if this adapter owns it (created from a connection string). When constructed with an external Pool, the caller is responsible for pool lifecycle.

Returns​

Promise<void>

A Promise that resolves when the connection is closed.

Implementation of​

EntityStorageAdapter.closeConnection

initialize()​

initialize(): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:250

Initializes the PostgreSQL database with required tables and schemas.

Creates entities table with JSONB storage, potential_duplicates table for duplicate detection, and sets up proper indexing and constraints for multi-tenant isolation.

This method is idempotent and safe to call multiple times.

Returns​

Promise<void>

A Promise that resolves when the database is successfully initialized.

Throws​

When database connection fails or table creation fails.

Example​

const adapter = new PostgresEntityStorageAdapter(connectionString, tenantId);

try {
  await adapter.initialize();
  console.log('Database schema initialized');
} catch (error) {
  console.error('Failed to initialize database:', error);
  throw error;
}

Implementation of​

EntityStorageAdapter.initialize

getAllEntities()​

getAllEntities(): Promise<EntityPair[]>

Defined in: storage/PostgresEntityStorageAdapter.ts:283

Retrieves all entity pairs for the current tenant.

Returns​

Promise<EntityPair[]>

A Promise that resolves with an array of EntityPair objects.

Throws​

If the database query fails.

Implementation of​

EntityStorageAdapter.getAllEntities

searchEntities()​

searchEntities(criteria): Promise<EntityPair[]>

Defined in: storage/PostgresEntityStorageAdapter.ts:339

Searches entities using advanced PostgreSQL JSONB query capabilities.

Supports rich query operators optimized for PostgreSQL:

  • $gt, $gte: Greater than, greater than or equal (numeric)
  • $lt, $lte: Less than, less than or equal (numeric)
  • $eq: Exact equality (numeric)
  • $regex: Case-insensitive regex matching using PostgreSQL ~* operator
  • String values: Case-insensitive exact matching
  • Boolean values: Direct boolean comparison
  • Numeric values: Exact numeric comparison

All searches examine both initial and modified entity states for comprehensive results.

Parameters​

criteria​

SearchCriteria

Array of search criteria objects.

Returns​

Promise<EntityPair[]>

A Promise that resolves with an array of entity pairs matching all criteria.

Throws​

If the database query fails.

Example​

// Complex multi-criteria search
const results = await adapter.searchEntities([
  { "data.age": { $gte: 21 } },          // Adults over 21
  { "data.age": { $lt: 65 } },           // Under retirement age
  { "data.status": "active" },            // Active status
  { "data.name": { $regex: "smith" } },    // Name contains "smith"
  { "data.verified": true }               // Verified accounts
]);

// Geographic search
const localUsers = await adapter.searchEntities([
  { "data.address.city": "Boston" },
  { "data.address.state": "MA" }
]);

Implementation of​

EntityStorageAdapter.searchEntities

saveEntity()​

saveEntity(entity): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:442

Saves an EntityPair to the entity store.

If an entity with the same GUID and tenant ID already exists, it will be updated.

Parameters​

entity​

EntityPair

The EntityPair object to save.

Returns​

Promise<void>

A Promise that resolves when the entity is successfully saved.

Throws​

If the database operation fails.

Implementation of​

EntityStorageAdapter.saveEntity

getEntity()​

getEntity(id): Promise<EntityPair | null>

Defined in: storage/PostgresEntityStorageAdapter.ts:478

Retrieves an entity by its internal ID.

Parameters​

id​

string

The internal ID of the entity.

Returns​

Promise<EntityPair | null>

A Promise that resolves with the EntityPair if found, or null otherwise.

Throws​

If the database query fails.

Implementation of​

EntityStorageAdapter.getEntity

getAllEntityData()​

getAllEntityData(): Promise<EntityPair[]>

Defined in: storage/PostgresEntityStorageAdapter.ts:506

Retrieves all entity data. This is an alias for getAllEntities.

Returns​

Promise<EntityPair[]>

A Promise that resolves with an array of all EntityPair objects.

Throws​

If the database query fails.

getModifiedEntitiesSince()​

getModifiedEntitiesSince(timestamp): Promise<EntityPair[]>

Defined in: storage/PostgresEntityStorageAdapter.ts:517

Retrieves entities that have been modified since a specific timestamp.

Parameters​

timestamp​

string

The ISO 8601 timestamp string from which to retrieve modified entities.

Returns​

Promise<EntityPair[]>

A Promise that resolves with an array of EntityPair objects modified after the timestamp.

Throws​

If the database query fails.

Implementation of​

EntityStorageAdapter.getModifiedEntitiesSince

markEntityAsSynced()​

markEntityAsSynced(id): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:544

Marks an entity as synced by updating its sync_level to "SYNCED" and last_updated timestamp.

Parameters​

id​

string

The internal ID of the entity to mark as synced.

Returns​

Promise<void>

A Promise that resolves when the entity's sync status is updated.

Throws​

If the database update fails.

deleteEntity()​

deleteEntity(id): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:563

Deletes an entity from the store by its internal ID.

Also deletes any associated potential duplicate records.

Parameters​

id​

string

The internal ID of the entity to delete.

Returns​

Promise<void>

A Promise that resolves when the entity and its duplicates are deleted.

Throws​

If the database deletion fails.

Implementation of​

EntityStorageAdapter.deleteEntity

getEntityByExternalId()​

getEntityByExternalId(externalId): Promise<EntityPair | null>

Defined in: storage/PostgresEntityStorageAdapter.ts:586

Retrieves an entity by its external ID.

Searches both initial and modified states for the externalId field.

Parameters​

externalId​

string

The external ID of the entity to retrieve.

Returns​

Promise<EntityPair | null>

A Promise that resolves with the EntityPair if found, or null otherwise.

Throws​

If the database query fails.

Implementation of​

EntityStorageAdapter.getEntityByExternalId

setExternalId()​

setExternalId(guid, externalId): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:626

Sets or updates the externalId for an entity.

Updates the externalId within the modified JSONB data of the entity.

Parameters​

guid​

string

The GUID of the entity to update.

externalId​

string

The new external ID to set.

Returns​

Promise<void>

A Promise that resolves when the externalId is successfully set.

Throws​

If the database update fails.

getPotentialDuplicates()​

getPotentialDuplicates(): Promise<object[]>

Defined in: storage/PostgresEntityStorageAdapter.ts:645

Retrieves all potential duplicate entity pairs for the current tenant.

Returns​

Promise<object[]>

A Promise that resolves with an array of objects containing entityGuid and duplicateGuid.

Throws​

If the database query fails.

Implementation of​

EntityStorageAdapter.getPotentialDuplicates

savePotentialDuplicates()​

savePotentialDuplicates(duplicates): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:669

Saves an array of potential duplicate entity pairs.

Uses ON CONFLICT DO NOTHING to prevent duplicate entries if a pair already exists.

Parameters​

duplicates​

object[]

An array of objects, each containing entityGuid and duplicateGuid.

Returns​

Promise<void>

A Promise that resolves when the potential duplicates are saved.

Throws​

If the database transaction fails.

Implementation of​

EntityStorageAdapter.savePotentialDuplicates

resolvePotentialDuplicates()​

resolvePotentialDuplicates(duplicates): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:691

Resolves (deletes) an array of potential duplicate entity pairs.

Parameters​

duplicates​

object[]

An array of objects, each containing entityGuid and duplicateGuid of the duplicates to resolve.

Returns​

Promise<void>

A Promise that resolves when the potential duplicates are removed.

Throws​

If the database deletion fails.

Implementation of​

EntityStorageAdapter.resolvePotentialDuplicates

clearStore()​

clearStore(): Promise<void>

Defined in: storage/PostgresEntityStorageAdapter.ts:711

Clears all entities and potential duplicates for the current tenant from the store.

Returns​

Promise<void>

A Promise that resolves when all data is cleared.

Throws​

If the database deletion fails.

Implementation of​

EntityStorageAdapter.clearStore