majidbl72/base
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
base/internal/repository/postgres/asset/RELATIONS.md
m.zare 77ca6c34a3 initial commit
2026-04-10 18:25:21 +03:30

3.6 KiB
Raw Permalink Blame History

Asset relations: Report and Comment

Overview

Reports and comments are child relations of an asset. They are stored in separate tables (asset_reports, asset_comments) and are always loaded/saved through the asset (no standalone Report or Comment repository in this layer).

Comment relation

Storage

  • Table: asset_comments
  • Columns: id, asset_id, content, writer_id, writer_type, parent_id, created_at, updated_at, deleted_at
  • Parent link: asset_id → assets.id

What happens

Operation Behavior
Create asset If asset.Comments is non-empty, each comment is inserted with asset_id = new_asset_id. IDs/timestamps come from DB.
FindByID / FindByProfileID All rows in asset_comments with asset_id = asset.id are loaded and mapped to asset.Comments (flat list).
Update asset All existing comments for that asset are deleted, then asset.Comments is re-inserted (replace strategy).
Delete asset All comments for that asset are deleted in the same transaction before the asset row is deleted.

Domain vs persistence

  • Domain: Comment has Replies []Comment (nested).
  • Persistence: Stored as rows in asset_comments with parent_id for replies.
  • When loading: A flat list is read from DB, then buildCommentTree() turns it into a tree: top-level comments have Replies populated.
  • When saving: flattenComments() turns the tree into a flat list (parent then its replies); all rows are persisted. Note: when creating a new asset with new nested comments, reply ParentID in the domain may be zero (parent not yet saved); the current single-batch insert does not resolve parent IDs, so nested replies on create may end up with parent_id = NULL. For updates after load, parent IDs exist and nested replies persist correctly.

Report relation

Storage

  • Table: asset_reports
  • Columns: id, asset_id, reported_by (JSONB), reported_at, reason (JSONB), status, notes, attachments (JSONB), created_at, updated_at, deleted_at
  • Parent link: asset_id → assets.id
  • Nested data: ReportedBy, ReportReason, and Attachments are stored as JSON in the same row.

What happens

Operation Behavior
Create asset If asset.Reports is non-empty, each report is inserted: ReportedBy, Reason, and Attachments are JSON-encoded into the report row.
FindByID / FindByProfileID All rows in asset_reports with asset_id = asset.id are loaded; JSONB columns are decoded into Report.ReportedBy, Report.Reason, Report.Attachments.
Update asset All existing reports for that asset are deleted, then asset.Reports is re-inserted (replace strategy).
Delete asset All reports for that asset are deleted in the same transaction before the asset row is deleted.

Domain vs persistence

  • ReportedBy, ReportReason, Attachments are fully round-tripped via JSON; no separate tables.
  • Report ID and ReportedAt are set when loading from DB; on create, IDs/timestamps come from DB.

Summary

  • Comment: Stored and loaded as a flat list per asset; parent_id is persisted but Replies are not built when loading and nested replies are not written when saving.
  • Report: Stored and loaded as a list per asset; nested structures (ReportedBy, Reason, Attachments) are stored as JSONB and fully restored on load.
  • Both relations use a replace-on-update strategy: updating an asset deletes all its comments and reports and re-inserts from asset.Comments and asset.Reports.