fhanyuh
caf8e98378
No content changes: git diff --ignore-all-space over these files is empty. The churn came from editing on Windows against a repo checked out with LF.
111 lines
4.8 KiB
Markdown
111 lines
4.8 KiB
Markdown
# Database Entity Relationship Diagram (ERD)
|
|
|
|
This document describes the PostgreSQL database schema used to store OCR documents, parsed layout elements, inline cell edits, and row flagging status for the DO-PFM system.
|
|
|
|
## Relationship Diagram
|
|
|
|
```mermaid
|
|
erDiagram
|
|
documents {
|
|
integer id PK "SERIAL"
|
|
varchar filename UK "VARCHAR(255)"
|
|
timestamp upload_time "TIMESTAMP"
|
|
integer size "INTEGER"
|
|
boolean parsed "BOOLEAN"
|
|
jsonb metadata "JSONB"
|
|
jsonb layout_parsing_result "JSONB"
|
|
boolean is_sample "BOOLEAN"
|
|
varchar file_hash "VARCHAR(64)"
|
|
}
|
|
|
|
ocr_items {
|
|
integer id PK "SERIAL"
|
|
integer document_id FK "INTEGER"
|
|
integer row_index "INTEGER"
|
|
varchar kode_barang_original "VARCHAR(255)"
|
|
varchar kode_barang "VARCHAR(255)"
|
|
varchar nama_barang "VARCHAR(255)"
|
|
varchar banyak_original "VARCHAR(255)"
|
|
varchar banyak "VARCHAR(255)"
|
|
varchar jumlah_original "VARCHAR(255)"
|
|
varchar jumlah "VARCHAR(255)"
|
|
boolean is_flagged "BOOLEAN"
|
|
varchar remark "VARCHAR(1000)"
|
|
}
|
|
|
|
documents ||--o{ ocr_items : "has"
|
|
|
|
vendors {
|
|
integer id PK "SERIAL"
|
|
varchar name UK "VARCHAR(255)"
|
|
timestamp created_at "TIMESTAMP"
|
|
}
|
|
|
|
customers {
|
|
integer id PK "SERIAL"
|
|
varchar name UK "VARCHAR(255)"
|
|
timestamp created_at "TIMESTAMP"
|
|
}
|
|
```
|
|
|
|
## Schema Definitions
|
|
|
|
### 1. `documents` Table
|
|
Stores parsed OCR files (both static sample pages and user-uploaded invoices/documents).
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|---|---|---|---|
|
|
| `id` | `SERIAL` | `PRIMARY KEY` | Unique autoincrement ID of the document. |
|
|
| `filename` | `VARCHAR(255)` | `UNIQUE`, `NOT NULL` | The unique name of the document file. |
|
|
| `upload_time` | `TIMESTAMP` | `DEFAULT NOW()`, `NOT NULL` | The timestamp of the file upload. |
|
|
| `size` | `INTEGER` | `DEFAULT 0`, `NOT NULL` | The file size in bytes. |
|
|
| `parsed` | `BOOLEAN` | `DEFAULT FALSE`, `NOT NULL` | Indicates whether the document layout parsing has completed. |
|
|
| `metadata` | `JSONB` | | Structured general metadata (Vendor, Customer, PO, SO, DO, etc.). |
|
|
| `layout_parsing_result` | `JSONB` | | Raw layout parser response JSON from pipeline backend. |
|
|
| `is_sample` | `BOOLEAN` | `DEFAULT FALSE`, `NOT NULL` | True if the file belongs to the pre-seeded static sample pages. |
|
|
| `file_hash` | `VARCHAR(64)` | | SHA-256 hash of the document file contents. |
|
|
|
|
---
|
|
|
|
### 2. `ocr_items` Table
|
|
Stores the extracted row items from tabular components of the document, supporting inline modifications and flagging details.
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|---|---|---|---|
|
|
| `id` | `SERIAL` | `PRIMARY KEY` | Unique autoincrement ID of the item row. |
|
|
| `document_id` | `INTEGER` | `REFERENCES documents(id) ON DELETE CASCADE`, `NOT NULL` | The associated document ID. |
|
|
| `row_index` | `INTEGER` | `NOT NULL` | The index of the item row in the document table list (0-indexed). |
|
|
| `kode_barang_original` | `VARCHAR(255)` | | The initial "Kode Barang" value extracted directly from OCR. |
|
|
| `kode_barang` | `VARCHAR(255)` | | The edited/current "Kode Barang" value. |
|
|
| `nama_barang` | `VARCHAR(255)` | | The "Nama Barang" value (read-only reference). |
|
|
| `banyak_original` | `VARCHAR(255)` | | The initial "Banyak" value extracted from OCR. |
|
|
| `banyak` | `VARCHAR(255)` | | The edited/current "Banyak" value. |
|
|
| `jumlah_original` | `VARCHAR(255)` | | The initial "Jumlah" value extracted from OCR. |
|
|
| `jumlah` | `VARCHAR(255)` | | The edited/current "Jumlah" value. |
|
|
| `is_flagged` | `BOOLEAN` | `DEFAULT FALSE`, `NOT NULL` | True if the line item is flagged/strikethrough ("dicoret"). |
|
|
| `remark` | `VARCHAR(1000)` | | Custom notes/remarks provided for flagging. |
|
|
|
|
* **Unique Constraints**: A unique index on `(document_id, row_index)` prevents duplicate indexes for the same page.
|
|
|
|
---
|
|
|
|
### 3. `vendors` Table
|
|
Stores the Vendor Master registry.
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|---|---|---|---|
|
|
| `id` | `SERIAL` | `PRIMARY KEY` | Unique autoincrement ID of the vendor. |
|
|
| `name` | `VARCHAR(255)` | `UNIQUE`, `NOT NULL` | The unique name of the vendor (e.g. including kawasan/address). |
|
|
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()`, `NOT NULL` | The registration timestamp. |
|
|
|
|
---
|
|
|
|
### 4. `customers` Table
|
|
Stores the Customer Master registry.
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|---|---|---|---|
|
|
| `id` | `SERIAL` | `PRIMARY KEY` | Unique autoincrement ID of the customer. |
|
|
| `name` | `VARCHAR(255)` | `UNIQUE`, `NOT NULL` | The unique name of the customer (e.g. including branch/address). |
|
|
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()`, `NOT NULL` | The registration timestamp. |
|