TransactionDocument

TransactionDocument is a soft-deletable N:M join entity that links a Transaction to a Document (e.g. a bank-statement attachment or receipt). It lives in the core_api schema and mirrors the CompanyMedia / PersonMedia bridge-table shape. A partial unique index on (transaction_pk) WHERE deleted_at IS NULL enforces the current product invariant of one active attachment per transaction; a second transaction hard-delete CASCADE and a RESTRICT on the document side protect referential integrity. Workspace scope is inherited transitively through the Transaction relation — no direct workspace FK is declared on this table.

Naming	Value
Object	TransactionDocument
Resource type (JSON:API `type`)	`transaction_document`
Collection / records root	— _{(not a records root)}
REST base	`/v1/transaction-documents`
Entity class	`TransactionDocument`

Internal object. Not currently exposed on the public REST API. The operations below describe the intended contract.

API operations

Operation	Method & path	Status
List	`GET /v1/transaction-documents`	🟡 Planned
Retrieve	`GET /v1/transaction-documents/{id}`	🟡 Planned
Create	`POST /v1/transaction-documents`	🟡 Planned
Update	`PATCH /v1/transaction-documents/{id}`	🟡 Planned
Delete	`DELETE /v1/transaction-documents/{id}`	🟡 Planned

Data model

Attributes

Field	Type	Required	Constraints	Allowed values	Description
created_at	🔒 system — timestamptz	✅ Yes	Auto-set via `onCreate: () => new Date()`. Not nullable.	—	Timestamp at which this join row was created (i.e. the document was attached to the transaction).
deleted_at	timestamptz \| null	⚪ No	Nullable. Soft-delete sentinel. Participates in partial unique index `uq_transaction_documents_one_active_per_transaction` — only one row with `deleted_at IS NULL` may exist per `transaction_pk` at any time.	—	When set, the document is considered detached from the transaction. All active-join queries filter `deleted_at IS NULL`.

Relationships

Name	Type	Required	Description
transaction	to-one (ManyToOne)	✅ Yes	The transaction this document is attached to. FK `transaction_pk` with `ON DELETE CASCADE` — deleting the parent transaction removes this join row. Composite index `idx_transaction_documents_transaction_deleted` covers `(transaction_pk, deleted_at)` for the common read path.
document	to-one (ManyToOne)	✅ Yes	The document being attached. FK `document_pk` with `ON DELETE RESTRICT` — hard-deleting a Document that has live join rows raises a FK violation, forcing callers to go through `DocumentService.softDelete`. Index `idx_transaction_documents_document` supports the reverse-traversal path ('which transactions reference this document').

System-computed

pk — auto-increment serial primary key, internal join only; never exposed via public API.
created_at — set once on insert via onCreate: () => new Date() MikroORM hook.
deleted_at — set to current timestamp by the service layer on detach (soft-delete); never set by the user directly.
The partial unique index uq_transaction_documents_one_active_per_transaction on (transaction_pk) WHERE deleted_at IS NULL is enforced by Postgres at the DB layer — the service layer cooperates via a soft-delete-then-insert pattern on re-attach.
Workspace scope is inherited transitively through the Transaction relation; no explicit workspace_pk column exists on this table.

Example

{
  "data": {
    "type": "transaction_document",
    "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "attributes": {
      "created_at": "2026-04-22T09:14:32.000Z",
      "deleted_at": null
    },
    "relationships": {
      "transaction": {
        "data": { "type": "transaction", "id": "f47ac10b-58cc-4372-a567-0e02b2c3d479" }
      },
      "document": {
        "data": { "type": "document", "id": "d290f1ee-6c54-4b01-90e6-d701748f0851" }
      }
    }
  }
}

_{Source: apps/api/src/database/entities/TransactionDocument.ts · domain: financial-graph · tier: Supporting}