A temporal EAV entity store in Postgres
Part 2 — The abstract repository

Part 1 defined the storage: six tables, half-open time windows, and a behavior matrix for every update. This part is about the code that makes the storage usable — one abstract repository class that owns all the machinery, so that a concrete domain type is nothing but a family id and a field list.

Goal

Write the hard parts exactly once. Transactions, the close-and-open temporal dance, relation handling, validators, batch reads, pagination, history — all of it lives in one abstract class. A new domain type ("product", "category", "promo campaign") subclasses it, declares its fields as metadata, and gets a complete CRUD interface for free: create, point-in-time read (one, list, history), temporal update, and soft delete — identical in shape across every domain type, without writing a single query. Consumers speak CRUD in domain terms; only the inside of the class knows the storage is EAV.

The stack

• PostgreSQL — the six tables from Part 1; the only stateful thing in the design.
• TypeORM — entity mapping, and more importantly DataSource / QueryRunner / EntityManager for explicit transactions, plus an escape hatch for raw CTE queries on the read path.
• NestJS — dependency injection: repositories are bound to string tokens behind interfaces, so consumers never name a concrete class.
• TypeScript generics — the abstract class is parameterized over four types (DTO, Entity, View, History), which is what lets one implementation return correctly-typed results for every domain.
• lodash — groupBy / mapValues pivot the flat raw SQL rows back into per-entity, per-field structures on the batch read path.

The shape

One class, four type parameters — the four shapes a domain object takes on its way through the system. The DTO comes in from the API; the View goes out with one current value per field; the History goes out with the full value timeline per field.

@Injectable()
export abstract class SmentityRepository<
  EntityDTO,                          // create payload (API in)
  Entity extends BaseSmentity,        // flat domain object
  EntityView extends BaseSmentity,    // read model: current value per field
  EntityHistory extends BaseSmentity, // read model: value timeline per field
> implements IGenericSmentityRepository<EntityDTO, Entity, EntityView, EntityHistory>
{
  // ...
}

Minimal fields

The entire state of the class. The first four are the contract a subclass fulfills; the rest is injected plumbing shared by every domain type.

interface IFieldMeta {
  field: string;      // storage name: 'name', 'price', 'parentCategory', ...
  type: string;       // TYPE_STR | TYPE_INT | TYPE_NUM | TYPE_BOOL
                      //   | TYPE_REL_UUID_CHILD_OF | TYPE_REL_UUID_PARENT_OF
                      //   | TYPE_TAG_SET
  validators: FieldValidator[];  // async checks, run inside the write tx
  default?: unknown;  // used when the DTO omits the field
  dtoField?: string;  // DTO property name, when it differs from `field`
}

interface FieldValidator {
  name: string;       // shows up in the 422 when the check fails
  exec: (queryRunner: QueryRunner, value: unknown) => Promise<boolean>;
}

Minimal methods

Split by visibility — public vs private. Strict visibility isn't a popular discipline in TypeScript codebases, but here the split is the design: the public surface speaks CRUD in domain terms, the private one speaks EAV. Nothing temporal or EAV-shaped may leak upward — a consumer should be unable to tell the storage isn't a plain table. The public set below is also deliberately deduplicated to the minimal full-CRUD contract: one method per operation (e.g. a single paginated list read — a separate findAll is the same read with the paging left out, so it doesn't earn a slot; same for batch variants of close).

One more rule: every method accepts an optional tx — a transaction handle — reads included. Passed one, the method joins the caller's transaction; omitted, it opens its own (writes) or borrows a connection (reads). For now tx is derived from TypeORM's EntityManager — inside the class it is unwrapped back to an EntityManager/QueryRunner pair — but the parameter is named for what it means, not what it currently is. This single rule is what lets a service compose creates, updates and relation changes across several repositories into one atomic commit.

Public — the CRUD surface

Private — the EAV machinery

Honest footnote: in the running codebase several of these internals are technically public (batch reads are called directly by services, the primitives by tests). The table above is the contract as designed — if you build this fresh, enforce the visibility the keywords claim.

The optimization: handwritten SQL on the read path

The write path stays on the ORM — writes are per-entity and transaction-shaped, exactly what an ORM is good at. The read path does not: assembling an EAV entity is a pivot, and pivots are where ORMs go to die. The abstract class actually contains three generations of the read path, and the fossil record is instructive:

The arithmetic that settled it: reading one entity with 8 fields went from ~17 queries to 3; a page of 50 entities went from ~850 to 4 (one count + the three batch queries). Same information, same database — the only thing that changed is who does the pivot.

Batch search — how findByIdsView actually works

-- one round-trip for the whole batch: $1 = uuid[], $2/$3 = timestamp, $4 = family
WITH targets AS (
  SELECT e.uuid, e.active_from, e.active_to
  FROM sm_entity e
  WHERE e.uuid = ANY($1::uuid[])
),
entity_data AS (
  SELECT t.uuid AS e_uuid,
         ef.name AS ef_attr_name, ef.type AS ef_attr_type,
         ev.val_str, ev.val_int, ev.val_num, ev.val_date,
         ev.active_from AS ev_active_from, ev.active_to AS ev_active_to
  FROM targets t
  JOIN sm_entity_attr ev      ON ev.fk_sm_entity_uuid = t.uuid
  JOIN sm_entity_attr_name ef ON ef.uuid = ev.fk_sm_entity_attr_name_uuid
  WHERE /* entity + attr [from, to) window filters, family scope */
  ORDER BY e_uuid, ev_active_from DESC
)
SELECT * FROM entity_data;
-- (+ two sibling queries for child-of / parent-of edges)

// the pivot — flat rows back into entities, in one pass
const byEntity = groupBy(rawRows, 'e_uuid');
const grouped  = mapValues(byEntity, rows => ({
  uuid: rows[0].e_uuid,
  ...mapValues(groupBy(rows, 'ef_attr_name'),
               fieldRows => fieldRows.map(toAttrView)),  // newest first
}));

Two design points make this an abstract-class optimization rather than a one-off fast query. First, the SQL is metadata-parameterized — family id, timestamps and the uuid array are the only inputs, so every domain type gets the fast path without writing any SQL of its own. Second, it is the single funnel: findByIdView is a batch of one, and findAllPaginated pages bare entity ids first and hydrates the page through the same engine — one implementation to index for, one to profile, one to get right. The known residue of generation 1 (Part 1's pagination N+1 caveat) is the roadmap item; the engine to fix it with already exists.

A minimal concrete repository

This is the payoff — the entire cost of a new domain type. Everything else in this article is inherited.

@Injectable()
export class ProductRepository
  extends SmentityRepository<CreateProductDTO, Product, ProductView, ProductHistory>
  implements IProductRepository
{
  constructor(
    @InjectDataSource() dataSource: DataSource,
    @InjectRepository(Smentity) smentityRepository: Repository<Smentity>,
    @InjectRepository(SmentityAttr) smentityAttrRepository: Repository<SmentityAttr>,
    @InjectRepository(SmentityAttrName) smentityAttrNameRepository: Repository<SmentityAttrName>,
    @InjectRepository(SmentityFamily) smentityFamilyRepository: Repository<SmentityFamily>,
    @InjectRepository(SmentityRel) smEntityRelRepository: Repository<SmentityRel>,
    @InjectRepository(SmCustomerEntity) smCustomerEntity: Repository<SmCustomerEntity>,
  ) {
    super(CreateProductDTO, Product, ProductView, ProductHistory,
      dataSource, smentityRepository, smentityAttrRepository,
      smentityAttrNameRepository, smentityFamilyRepository,
      smEntityRelRepository, smCustomerEntity);

    this.familyId = SM_ENTITY_PRODUCT_FAMILY;   // e.g. 2
    this.familyName = 'product';
    this.fieldsMeta = [
      {
        field: 'name', type: TYPE_STR,
        validators: [{
          name: 'Validate `name`',
          exec: async (_queryRunner, value: string) => value?.trim().length > 0,
        }],
      },
      { field: 'sku',   type: TYPE_STR, validators: [] },
      { field: 'price', type: TYPE_NUM, validators: [positive] },
      { field: 'order', type: TYPE_INT, validators: [] },
      {
        field: 'parentCategory', type: TYPE_REL_UUID_CHILD_OF,
        validators: [categoryExistsInFamily],   // async, runs in the write tx
      },
    ];
  }
}

Wiring it into NestJS

Consumers depend on an interface and a string token, never on the class. Swapping an implementation — or mocking one in tests — is a one-line provider change.

// constants
export const SM_PRODUCT_REPOSITORY = 'SmProductRepository';

// module
const productRepositoryProvider: ClassProvider = {
  provide: SM_PRODUCT_REPOSITORY,
  useClass: ProductRepository,
};

@Module({
  imports: [TypeOrmModule.forFeature([
    Smentity, SmentityAttr, SmentityAttrName,
    SmentityFamily, SmentityRel, SmCustomerEntity,
  ])],
  providers: [productRepositoryProvider /*, productServiceProvider, ... */],
  exports: [productRepositoryProvider],
})
export class SmentityModule {}

// consumer (a service holding the business rules)
@Injectable()
export class ProductService {
  constructor(
    @Inject(SM_PRODUCT_REPOSITORY)
    private readonly products: IProductRepository,
  ) {}
}

Where a write actually goes

What each method actually does

The algorithms behind the CRUD surface, one expandable walkthrough per method — every step driven by fieldsMeta, every write obeying Part 1's behavior matrix.

Caveats

Part 1's caveats were about the storage semantics; these are about the class that implements them — each one paid for in the code.

• Generate the transaction timestamp once, on the backend. The entity's active_from and every field's active_from must be the same instant. Let the database default each row (CURRENT_TIMESTAMP) and they drift apart by fractions of a millisecond per row — the entity becomes visible at a moment when its fields don't exist yet, and the atomic close-and-open handover stops being atomic. create and update compute one timestamp at the top and stamp every row they touch with it; the column defaults exist as a fallback, never as the mechanism.
• Re-derive repositories from the transaction. The injected Repository instances are bound to the default connection. Inside a transaction you must get fresh ones from the EntityManager (entityManager.getRepository(Smentity)) — a query on the injected instance silently escapes the transaction and commits on its own.
• Commit only the transaction you opened. A method that received a caller's tx must never commit, roll back or release it — that right belongs to whoever opened it. The queryRunner?.commitTransaction() optional-chaining guard in every write path is the visible trace of this rule: the runner is only non-null when the method created it.
• Runtime constructors must match the generics. TypeScript types erase at runtime, so the class news up DTOs, Views and Histories from the four constructor arguments. A mismatch between the generic list and the constructor list compiles cleanly and fails only at runtime — keep them in the same order, always.
• Guard every dynamic where clause. findOneBy({ uuid: undefined }) drops the predicate and returns the table's first row (typeorm#9316) — and in a shared-table design, "the first row" is some other family's entity. Null-check every lookup built from input before it reaches the query builder.
• Validators run inside the write transaction. That is what makes them consistent — and also what makes them dangerous: a slow validator holds the transaction (and its locks) open. Keep validator queries cheap; and note that a validator that throws is caught, logged and treated as a plain failure — so the real cause lives in the logs, not in the 422.
• Lazy creation plus cache equals races. Family and attr-name rows are find-or-create with a 2-second query cache; two concurrent first-writes can both miss and both insert. The unique index makes the loser fail loudly — add the retry, or pre-seed the names in a migration (Part 1's checklist).
• Wrap field-level errors with context. A failure deep in the EAV machinery surfaces as an anonymous constraint violation. The update path re-wraps every field error with the entity uuid and field name (Field update error entity: …, name(str)) before rethrowing — without that, production debugging in a generic store is guesswork.

The abstract class is the design's fixed cost, paid once. Every domain type after it is a constructor, two constants and a field list.

Before you implement: the decision checklist

Same rule as Part 1's storage checklist: none of these have a universally right answer, all of them have a wrong one — deciding by accident. Settle each before the first subclass exists.

• Abstract class, or a real layer. First and foremost. Inheritance: concrete repositories extend the machinery — one extends, one super() call, and every behavior is just there. Composition: a standalone EAV engine injected into thin per-family repositories — cleaner boundaries, easier to test in isolation, but every method becomes delegation plumbing and the four-generic typing gets awkward at the seam. (my choice — abstract class: the entire goal is that a domain type is a constructor, two constants and a field list; a delegation layer would double that cost for boundary purity nobody asked for)
• What tx actually is. An EntityManager, a QueryRunner, or an opaque wrapper you control? Whatever you pick, every method signature carries it — changing later touches everything. (my choice — derived from TypeORM's EntityManager for now, but named tx so the contract survives a future unwrap)
• Generics or loose records. Four type parameters plus four runtime constructors is real ceremony — the alternative is untyped records and casting at every call site. (my choice — the generics: the ceremony is paid once per family, the type safety is collected on every call)
• Where the virtual schema lives. fieldsMeta in the subclass constructor, or externalized to config / a database table? External means runtime-editable fields; in-code means every field change is a reviewed, versioned commit. (my choice — in code: the virtual schema is the API contract, and contracts go through PRs)
• Timestamp authority. Who mints the transaction instant — the backend or the database defaults? This is caveat #1; it only looks cosmetic until the first drift bug. (my choice — backend, one timestamp per transaction, stamped on every row it touches)
• One read engine or per-method queries. Every read path delegating to a single batch engine keeps the CTEs in one place and makes N+1 structurally hard; per-method queries are simpler to write and diverge immediately. (my choice — one engine: findByIdView is findByIdsView with one uuid, no special cases)
• Visibility discipline. Will you actually enforce the public-CRUD / private-EAV split, or let internals leak because TypeScript makes leaking easy? (my choice — designed as a strict split; honestly enforced only partially in the running code, which is exactly why it's on this list)
• DI binding. Concrete classes, or string tokens behind interfaces? Tokens cost a constants file and buy mockability and swappable implementations. (my choice — tokens + interfaces everywhere; no consumer names a concrete repository)
• Subclass override policy. When may a family override a base method instead of just declaring metadata? Unlimited overriding quietly forks the machinery per family. (my choice — metadata first; overriding is the exception and each override must justify itself in review)
• Error surfacing. What a failed validator returns to the API (and what it leaves in the logs), and how deep EAV errors get wrapped with entity/field context. (my choice — validators throw 422 with the validator's name; every field-level error is re-wrapped with entity uuid + field before it climbs)

Part 3

The layer above — services owning business rules and cross-entity transactions, the validation ladder, and controllers that expose point-in-time reads — is covered in Part 3 — Services and the API edge.