Production engineering guide for erp document management covering patterns, implementation strategies, and operational best practices.
ERP Document Management is the systematic handling of business documents—purchase orders, invoices, work orders, shipping manifests, and service reports—within an enterprise resource planning system. It matters when document flow is critical to operational execution, compliance, and audit readiness, especially in manufacturing, supply chain, and project-based service environments. This is not about storing PDFs in a folder; it’s about linking documents to business entities, enforcing lifecycle rules, and enabling cross-system visibility.
Documents in an ERP are not static files; they are first-class entities with states, owners, and dependencies. A purchase order, for instance, begins as a draft, transitions to approved, then to sent to vendor, and finally to received and billed. Each state triggers actions: notifications, approvals, and system updates.
To move a document from draft to approved, the system checks:
workflowId field in the Document table.role lookup in the UserRole table.UPDATE Document
SET status = 'Approved',
approvedBy = 'user-42',
approvedAt = NOW(),
workflowId = 'PO-APPROVAL-2024'
WHERE id = 'PO-2024-001'
AND status = 'Draft'
AND (SELECT COUNT(*)
FROM DocumentLine
WHERE documentId = 'PO-2024-001'
) > 0;A failed transition logs an error in DocumentAuditLog:
{
"documentId": "PO-2024-001",
"action": "approve",
"timestamp": "2024-04-05T10:23:15Z",
"status": "failed",
"error": {
"code": "MISSING_LINE_ITEMS",
"message": "Purchase order must have at least one line item",
"details": {
"expected": 1,
"actual": 0
}
}
}The DocumentAuditLog is updated by a background job that polls the Document table every 15 seconds. If a document remains in Draft for more than 48 hours without activity, it is flagged in DocumentAlert:
INSERT INTO DocumentAlert (documentId, type, severity, message, createdAt)
SELECT d.id, 'STALE_DRAFT', 'warning',
'Draft document has no activity in 48 hours',
NOW()
FROM Document d
WHERE d.status = 'Draft'
AND d.lastModified < NOW() - INTERVAL '48 hours'
AND d.id NOT IN (
SELECT documentId FROM DocumentActivity
WHERE createdAt > NOW() - INTERVAL '48 hours'
);Approval workflows are defined as sequences of steps in a WorkflowDefinition table. Each step has:
assignee (user or role)condition (SQL expression or JSON filter)actions (list of actionType and parameters)Example workflow for a purchase order:
{
"id": "PO-APPROVAL-2024",
"steps": [
{
"stepId": 1,
"assignee": "finance-team",
"condition": "document.totalAmount > 10000",
"actions": [
{
"actionType": "send-email",
"parameters": {
"template": "po-approval-urgent",
"recipients": ["[email protected]"]
}
}
]
},
{
"stepId": 2,
"assignee": "procurement-manager",
"condition": "true",
"actions": [
{
"actionType": "update-status",
"parameters": {
"newStatus": "Awaiting-Procurement-Approval"
}
}
]
}
]
}When a step is completed, the workflow engine updates the WorkflowInstance table:
UPDATE WorkflowInstance
SET currentStep = 2,
stepStarted = NOW(),
stepCompleted = NULL,
status = 'in-progress'
WHERE workflowId = 'PO-APPROVAL-2024'
AND documentId = 'PO-2024-001';A step fails if any action returns an HTTP 500 error or a non-zero exit code. The system logs the failure in WorkflowErrorLog:
{
"workflowId": "PO-APPROVAL-2024",
"stepId": 1,
"documentId": "PO-2024-001",
"timestamp": "2024-04-05T10:45:00Z",
"error": {
"code": "EMAIL_SEND_FAILED",
"message": "Failed to send approval email via SMTP",
"details": {
"smtpHost": "mail.company.com",
"port": 587,
"response": "550 5.1.1 <[email protected]>: Recipient address rejected: User unknown"
}
}
}Documents are not isolated; they are tied to business entities via foreign keys. The Document table has a linkedEntity field that references a BusinessEntity table.
When a purchase order is created, the system creates a Document record and links it to the PurchaseOrder record:
INSERT INTO Document (
id,
documentType,
linkedEntity,
linkedEntityId,
status,
createdAt
) VALUES (
'PO-2024-001',
'PurchaseOrder',
'PurchaseOrder',
'PO-2024-001',
'Draft',
'2024-04-05T09:00:00Z'
);To retrieve all documents related to a purchase order, use this query:
SELECT d.id, d.documentType, d.status, d.createdAt
FROM Document d
JOIN PurchaseOrder po ON d.linkedEntityId = po.id
WHERE po.id = 'PO-2024-001'
ORDER BY d.createdAt DESC;The linkedEntity field is a composite key: entityType and entityId. This allows a single document to be linked to multiple types of entities—e.g., a vendor invoice can be linked to both a PurchaseOrder and a Payment.
Each document has a version history stored in DocumentVersion. When a document is updated, a new version is created with a versionId, changeLog, and metadata.
INSERT INTO DocumentVersion (
documentId,
versionId,
versionNumber,
changeLog,
createdBy,
createdAt,
metadata
) VALUES (
'PO-2024-001',
'v2-2024-04-05T11:30:00Z',
2,
'Updated delivery date and added line item for 100 units',
'user-42',
'2024-04-05T11:30:00Z',
{
"fileSize": 1048576,
"fileType": "application/pdf",
"checksum": "sha256:abcdef1234567890"
}
);Attachments are stored in a DocumentAttachment table with documentId, attachmentId, fileName, mimeType, and blobId. The blobId references a file storage system (e.g., S3, NFS, or a database BLOB column).
A service runs every 5 minutes to synchronize document attachments:
#!/bin/bash
while true; do
psql -c "
SELECT d.id, a.attachmentId, a.fileName, a.mimeType, a.blobId
FROM Document d
JOIN DocumentAttachment a ON d.id = a.documentId
WHERE a.syncedAt IS NULL
OR a.syncedAt < NOW() - INTERVAL '1 hour'
LIMIT 50
" | while read line; do
documentId=$(echo "$line" | cut -d'|' -f1)
attachmentId=$(echo "$line" | cut -d'|' -f2)
fileName=$(echo "$line" | cut -d'|' -f3)
mimeType=$(echo "$line" | cut -d'|' -f4)
blobId=$(echo "$line" | cut -d'|' -f5)
# Upload to blob storage via curl
curl -X PUT \
-H "Content-Type: $mimeType" \
--data-binary "@/data/attachments/$blobId" \
"https://blob-storage.company.com/documents/$documentId/$attachmentId"
# Update sync status
psql -c "
UPDATE DocumentAttachment
SET syncedAt = NOW()
WHERE documentId = '$documentId'
AND attachmentId = '$attachmentId'
"
done
sleep 300
doneIf the DocumentAttachment table grows beyond 1 million rows, the sync service becomes a bottleneck. A common failure is that attachments are marked as synced but the blob file is missing. This happens when a file is uploaded to blob storage, but the syncedAt timestamp is set before the upload completes.
To detect this, a health check runs daily:
SELECT
da.documentId,
da.attachmentId,
da.fileName,
da.blobId,
CASE
WHEN EXISTS (
SELECT 1 FROM blob_storage
WHERE blobId = da.blobId
) THEN 'present'
ELSE 'missing'
END AS status
FROM DocumentAttachment da
WHERE da.syncedAt < NOW() - INTERVAL '2 days'
AND da.blobId IS NOT NULL;A missing attachment triggers a DocumentAlert with severity critical.
Documents are searched using a DocumentSearch endpoint that accepts query, filters, and sort.
GET /api/document/search
Host: erp.company.com
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c
Content-Type: application/json
{
"query": "Q2024-001",
"filters": {
"documentType": "PurchaseOrder",
"status": ["Approved", "SentToVendor"],
"createdAt": {
"from": "2024-04-01T00:00:00Z",
"to": "2024-04-15T23:59:59Z"
}
},
"sort": [
{ "field": "createdAt", "order": "desc" },
{ "field": "documentId", "order": "asc" }
]
}Response:
{
"results": [
{
"id": "PO-2024-001",
"documentType": "PurchaseOrder",
"status": "SentToVendor",
"linkedEntity": "PurchaseOrder",
"linkedEntityId": "PO-2024-001",
"createdAt": "2024-04-05T09:00:00Z",
"updatedAt": "2024-04-05T11:30:00Z",
"version": "v2",
"title": "Purchase Order for 100 Units of Widget A",
"summary": "PO issued to Vendor X for 100 units of Widget A, delivery due May 15."
}
],
"total": 1,
"page": 1,
"size": 10
}Search performance degrades when the Document table exceeds 10 million rows. The system uses a DocumentSearchIndex table that stores a denormalized version of document data for fast queries.
An indexing job runs every 10 minutes:
INSERT INTO DocumentSearchIndex (
documentId,
title,
summary,
documentType,
status,
linkedEntity,
linkedEntityId,
createdAt,
updatedAt,
version
)
SELECT
d.id,
d.title,
d.summary,
d.documentType,
d.status,
d.linkedEntity,
d.linkedEntityId,
d.createdAt,
d.updatedAt,
dv.versionNumber
FROM Document d
JOIN DocumentVersion dv ON d.id = dv.documentId
WHERE dv.isCurrent = true
AND d.updatedAt > NOW() - INTERVAL '10 minutes'
AND d.id NOT IN (
SELECT documentId FROM DocumentSearchIndex
WHERE updatedAt > NOW() - INTERVAL '1 hour'
)
ON CONFLICT (documentId) DO UPDATE
SET
title = EXCLUDED.title,
summary = EXCLUDED.summary,
status = EXCLUDED.status,
updatedAt = EXCLUDED.updatedAt,
version = EXCLUDED.version
WHERE DocumentSearchIndex.updatedAt < EXCLUDED.updatedAt;If the indexing job fails to complete within 3 minutes, it logs an error in SystemAlert:
{
"system": "document-indexer",
"alertType": "indexing-failed",
"timestamp": "2024-04-05T11:40:00Z",
"details": {
"duration": "3m45s",
"rowsProcessed": 1234,
"expected": 2000,
"message": "Indexing job took longer than 3 minutes"
}
}Documents are distributed across systems via a DocumentDistribution pipeline that pushes documents to external systems: ERP, CRM, EDI, and file shares.
A document is eligible for distribution when:
status is Approved or SentToVendordistributionEnabled flag is true in DocumentThe pipeline uses a DocumentDistributionQueue table to track pending distributions:
INSERT INTO DocumentDistributionQueue (
documentId,
targetSystem,
status,
attemptCount,
lastAttempt,
payload
) SELECT
d.id,
'edifiles',
'pending',
0,
NOW(),
json_build_object(
'documentId', d.id,
'documentType', d.documentType,
'data', (
SELECT json_agg(row_to_json(t))
FROM (
SELECT
line.itemCode,
line.quantity,
line.unitPrice,
line.total
FROM DocumentLine line
WHERE line.documentId = d.id
) t
)
)
FROM Document d
WHERE d.status IN ('Approved', 'SentToVendor')
AND d.distributionEnabled = true
AND d.id NOT IN (
SELECT documentId FROM DocumentDistributionQueue
WHERE status = 'pending'
);Each distribution target has a DistributionConfig table with system, endpoint, authType, and authConfig.
Example for EDI:
{
"system": "edifiles",
"endpoint": "https://edi.company.com/v1/send",
"authType": "bearer",
"authConfig": {
"token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c"
},
"transform": "edi-940"
}The distribution service polls the queue every 30 seconds and sends each document via HTTP:
#!/bin/bash
# distribute-documents.sh
while true; do
psql -c "
SELECT documentId, targetSystem, payload, endpoint, authType, authConfig
FROM DocumentDistributionQueue
JOIN DistributionConfig ON DistributionConfig.system = DocumentDistributionQueue.targetSystem
WHERE status = 'pending'
ORDER BY createdAt
LIMIT 10
" | while read line; do
documentId=$(echo "$line" | cut -d'|' -f1)
targetSystem=$(echo "$line" | cut -d'|' -f2)
payload=$(echo "$line" | cut -d'|' -f3)
endpoint=$(echo "$line" | cut -d'|' -f4)
authType=$(echo "$line" | cut -d'|' -f5)
authConfig=$(echo "$line" | cut -d'|' -f6)
# Build auth header
authHeader=""
if [ "$authType" = "bearer" ]; then
token=$(echo "$authConfig" | jq -r '.token')
authHeader="Authorization: Bearer $token"
fi
# Send request
curl -X POST \
-H "$authHeader" \
-H "Content-Type: application/json" \
-d "$payload" \
"$endpoint"
# Update queue
psql -c "
UPDATE DocumentDistributionQueue
SET status = 'sent',
lastAttempt = NOW(),
attemptCount = attemptCount + 1
WHERE documentId = '$documentId'
"
done
sleep 30
doneWhen the DocumentDistributionQueue grows to over 50,000 entries, the service starts missing messages. A common cause is that the DocumentDistributionQueue table is not vacuumed regularly, leading to index bloat.
A nightly maintenance job runs:
VACUUM ANALYZE DocumentDistributionQueue;Failure to run this job results in a 30% increase in distribution latency. The system detects this via a health check that compares queueSize to averageDailyDistributionCount.
When queueSize > 2 * averageDailyDistributionCount, a DocumentAlert is generated with type distribution-backlog.
Documents can have arbitrary metadata stored in a DocumentMetadata table with documentId, key, value, and dataType.
INSERT INTO DocumentMetadata (documentId, key, value, dataType)
VALUES ('PO-2024-001', 'contractVersion', '2.1', 'string');The system supports string, number, date, and boolean values. A custom field projectCode is stored as:
{
"key": "projectCode",
"value": "PROJ-2024-001",
"dataType": "string"
}When a document is queried, the metadata is included in the response:
{
"id": "PO-2024-001",
"documentType": "PurchaseOrder",
"status": "Approved",
"metadata": {
"projectCode": "PROJ-2024-001",
"contractVersion": "2.1",
"isUrgent": true
}
}Metadata can be used for filtering, reporting, and workflow logic. A common failure is that value is stored as a string but expected as a number, leading to incorrect filtering. The system detects this via a daily job that validates metadata types against a schema.
This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy