如何用C# .NET实现数据变更审批？求非工作流方案及最佳实践

Hey there! Let's break down the most practical, production-proven approaches to implement a superuser approval workflow for data modifications—no .NET Workflows required. These patterns balance simplicity, scalability, and maintainability depending on your system's complexity:

1. Dual-Table Pattern (Pending + Official Tables) #

This is the most straightforward and widely used approach. Here's how it works:

• Create a "pending" table that mirrors your official table's structure, plus additional metadata fields like requested_by, requested_at, approval_status (pending/approved/rejected), approved_by, approved_at, and rejection_reason.
• When a user submits a modification, write the changes to the pending table instead of the official one.
• Superusers access an approval dashboard to review pending requests. On approval, sync the data from the pending table to the official table and update the request's status. On rejection, mark it as rejected and add a note for the requester.

Pros & Cons #

• Pros: Clear separation of pending vs. live data, no risk of unapproved changes affecting production, easy to implement with basic SQL/CRUD logic.
• Cons: Requires maintaining two similar tables, sync logic needs careful handling (avoid race conditions with transactions).

Example Snippets #

Inserting a pending request:

INSERT INTO product_pending (id, name, price, category, requested_by, requested_at)
VALUES (101, 'Wireless Headphones', 99.99, 'Electronics', 'user_456', NOW());

Approving and syncing to official table:

BEGIN TRANSACTION;
-- Update official table
UPDATE product SET name = 'Wireless Headphones', price = 99.99 WHERE id = 101;
-- Mark request as approved
UPDATE product_pending SET approval_status = 'approved', approved_by = 'admin_789', approved_at = NOW() WHERE id = 101;
COMMIT;

2. Status Field + Version Control (Single Table) #

If you prefer to keep all data in one place, add status and versioning fields to your official table:

• Add fields like status (active/pending/rejected), version, requested_by, and requested_at to your existing table.
• When a user submits a modification, either create a new row with status = 'pending' and incremented version, or update the existing row's values and set status to pending (while keeping a history of previous active versions if needed).
• Superusers approve by setting status = 'active'. When querying live data, always filter for status = 'active' and the latest version.

Pros & Cons #

• Pros: No extra tables to maintain, easier to track full modification history with versions.
• Cons: Requires filtering queries to exclude pending data (add indexes on status and version to avoid performance hits), needs logic to handle conflicting pending requests for the same record.

Example Query for Live Data #

SELECT * FROM product WHERE id = 101 AND status = 'active' ORDER BY version DESC LIMIT 1;

3. Event-Driven Architecture (Message Queue + Approval Service) #

For more complex, scalable systems, decouple the request submission, approval, and data writing steps using events:

• When a user submits a modification, publish an event (containing the change details, requester info, etc.) to a message queue (like RabbitMQ or Redis).
• An approval service consumes these events, stores them in a request repository, and triggers notifications to superusers.
• On approval, the approval service publishes an "approved" event. A separate execution service consumes this event and applies the changes to the official database.

Pros & Cons #

• Pros: Fully decoupled components, easy to scale each service independently, works well with microservices architectures.
• Cons: Adds architectural complexity (needs to manage message queues, retries, and event ordering).

Example Python Snippet (Celery as Queue) #

Submitting a modification request:

from celery import Celery

app = Celery('approval_workflow', broker='redis://localhost:6379/0')

@app.task
def submit_product_update(product_data, requester_id):
    # Store request in DB for approval
    save_update_request(product_data, requester_id)
    # Notify admin dashboard of new request
    send_admin_notification(f"New product update request from user {requester_id}")

Applying approved changes:

@app.task
def apply_product_update(request_id):
    request = get_update_request(request_id)
    # Write to official DB
    update_product_in_db(request.product_data)
    # Mark request as completed
    mark_request_approved(request_id, admin_id)

4. Database Triggers + Audit Table (Low-Code App Changes) #

If you don't want to modify your application code extensively, use database triggers to intercept changes and route them for approval:

• Create an audit table to capture modification attempts, including old/new values, operation type (insert/update/delete), requester, and status.
• Add a trigger that fires before any write operation on the official table: it inserts the change into the audit table, then rolls back the original write (blocking direct updates).
• Superusers approve changes by running a stored procedure that applies the audit table's data to the official table and updates the request status.

Pros & Cons #

• Pros: Minimal changes to application code, database-level enforcement of approval rules.
• Cons: Trigger logic can be hard to debug, database-specific syntax limits portability, risk of trigger errors breaking writes.

Example MySQL Trigger #

DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
    -- Log the update attempt to audit table
    INSERT INTO product_audit (product_id, old_price, new_price, operation_type, requested_by, requested_at, status)
    VALUES (OLD.id, OLD.price, NEW.price, 'UPDATE', CURRENT_USER(), NOW(), 'pending');
    -- Block the direct update
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Updates require superuser approval';
END //
DELIMITER ;

General Best Practices #

• Approval Notifications: Send real-time alerts (email, in-app notifications, Slack) to superusers when new requests come in to avoid delays.
• RBAC Enforcement: Use role-based access control to ensure only authorized superusers can view and approve requests.
• Conflict Resolution: Handle concurrent requests for the same record—either auto-reject older requests or notify the requester to review the latest version.
• Full Audit Logs: Track every step: who submitted the request, when, who approved/rejected it, and the reason for rejection. This is critical for compliance and debugging.
• Failure Recovery: If applying approved changes fails (e.g., database error), mark the request as failed and notify admins to reprocess it—don't leave it in a limbo state.

内容的提问来源于stack exchange，提问作者Karufere Jr