祝融说。

Prologue: A Confusing Audit Report

It was another Monday morning. Li Lei, an auditor from the internal control department, tracked down architect Zhang Wei with a look of utter confusion on his face. Pointing to a report exported from the database, he said, "Zhang Wei, I need your help. We are auditing all 'price modification' operations from last quarter, and this report makes no sense to me at all."

The report came from a table named t_operation_log, a generic operation log table designed years ago to meet basic auditing requirements. Nearly every core business system (orders, products, users) wrote a record to this table whenever a critical operation was performed.

t_operation_log table (V1.0):

Li Lei pointed to a row in the report: id=123, operator_id=U-555, target_entity_type='Product', target_entity_id=P-987, operation_type='UPDATE_PRICE', details={'before': 100, 'after': 80}, timestamp='...'

Li Lei asked, "This record tells me that user U-555 (Operations Specialist Wang Wu) changed the price of product P-987 from 100 to 80. But it does not answer the question I care about most: on what basis did Wang Wu have the authority to modify the price?"

Zhang Wei explained, "Our permission system is based on Role-Based Access Control (RBAC). Wang Wu's role is 'Operations Specialist,' and the 'Operations Specialist' role has been granted the 'modify product price' permission."

Li Lei frowned, "So, to verify the legality of this operation, I must:

1. Know what Wang Wu's role was at the time of the operation.
2. Know whether, at that time, the 'Operations Specialist' role included the 'modify product price' permission.

Neither piece of information is in this log table. I would have to dig through the change history of your permission system, and I might even need your programmers to help me trace back the code version at that time to determine whether this operation was compliant. For auditing, this is a disaster."

Li Lei's complaint hit the nail on the head. The t_operation_log table only recorded "what happened," but not "why it was allowed to happen."

The root of this problem lies in a common but extremely harmful design habit: in business systems, we tightly couple the actions of "executing an operation" and "checking permissions" within the code logic, without creating independent, persistent records for each at the data level.

// ProductService.java
@Transactional
public void updatePrice(long productId, Money newPrice, User operator) {
    // 1. Check permission (happens in memory, leaves no trace)
    if (!permissionService.hasPermission(operator, "PRODUCT_PRICE_UPDATE")) {
        throw new AccessDeniedException("You are not allowed to update price.");
    }
  
    // 2. Execute operation (leaves an operation log)
    Product product = productRepository.findById(productId);
    Money oldPrice = product.getPrice();
    product.setPrice(newPrice);
    productRepository.save(product);
  
    // 3. Record operation log
    operationLogService.log(operator, "UPDATE_PRICE", product, ...);
}

When auditor Li Lei reviews the logs, he can only see the results of steps 2 and 3. The crucial step 1 -- that fleeting permission check that returned true in memory -- has vanished without a trace.

In this chapter, we will solve this problem completely. We will propose a core table design principle: business tables and authorization tables must be physically separated. We will learn how to design an audit table that can "self-prove guilt" or "self-prove innocence," one that not only records "What" but also clearly documents "Why."

Section 1: Separating Business Tables from Authorization Tables

In most systems, permission logic and business logic are intertwined like vines. A Product object's data is stored in the t_product table, while the rules about "who can modify this Product" might be stored in a series of tables such as t_role, t_permission, and t_role_permission_mapping. This separation is necessary, but insufficient.

We often overlook a key point: a successful business operation is actually the intersection of two independent events:

1. Authorization Event: At some point before the operation occurs, the system granted a certain subject (user/role) permission to perform a certain action on a certain object (product/order). This is a declaration of "intent."
2. Operation Event: At the moment the operation occurs, the subject actually exercised that permission and performed the action on the object. This is the execution of a "behavior."

Our existing t_operation_log table only records the "operation event," completely ignoring the "authorization event." It is like catching someone picking a lock and entering a house, but finding no evidence that this person holds a legitimate key.

To solve this problem, we must provide a clear, traceable home for the concept of "authorization" at the data level. What we need is not just a static permission model describing "who has what permissions," but also a dynamic log that can record "which authorization was used."

Redesigning the Audit Model: Introducing "Authorization Credentials"

Let us perform a thorough redesign of the audit log. Instead of using a single monolithic t_operation_log, we will introduce a more refined model with separated responsibilities.

t_business_operation Table (Only Records "What")

This table returns to its essence, solely responsible for precisely and immutably recording the business operation itself.

This table becomes more generic and pure. It follows the Actor-Verb-Target pattern, clearly describing "who did what to what." But it still does not answer "why."

t_authorization_decision Table (Core: Records "Why")

This is the soul of the new design. We create a brand-new table specifically to record every successful permission check (authorization decision). Each row in this table is an "authorization credential" -- a "one-time pass" issued by the permission system to the business system just before an operation is about to occur.

The power of this table lies in:

• Establishing a clear cause-and-effect relationship: Through op_id, we firmly bind an "operation" to the "authorization decision" that allowed it. Together, they form a complete, indivisible audit unit.
• Recording the "evidence" of authorization: The policy_id and policy_version fields are the finishing touch. It no longer vaguely says "because you are an Operations Specialist." Instead, it precisely records: "Because you meet the rule defined by policy ID POLICY-012, version v1.3, I allowed your operation."
• Context snapshot: The context field can capture all relevant environmental information at the time of the decision, such as the operator's IP address, whether the operation time was within working hours, and so on. This enables more sophisticated risk control auditing.

Refactoring Business and Permission Logic

To implement this model, we need to refactor the interaction between ProductService and PermissionService.

Refactored PermissionService: Its responsibility is no longer simply to return a boolean, but to return a "decision object" containing decision details.

// PermissionService.java
public Decision checkPermission(Principal principal, Action action, Resource resource, Context context) {
    // 1. Find the matching policy
    Policy matchedPolicy = policyRepository.findMatchingPolicy(principal, action, resource, context);
  
    // 2. Make the decision
    if (matchedPolicy != null) {
        return Decision.allow(
            principal, action, resource,
            matchedPolicy.getId(), matchedPolicy.getVersion(),
            context
        );
    } else {
        return Decision.deny(...);
    }
}

Refactored ProductService: The business code now needs to first "apply" for an authorization credential, then execute the operation, and finally associate the two.

// ProductService.java
@Transactional
public void updatePrice(long productId, Money newPrice, User operator) {
  
    // 1. Prepare the permission request
    Principal principal = Principal.fromUser(operator);
    Action action = new Action("product:price:update");
    Resource resource = new Resource("product/" + productId);
    Context context = Context.fromHttpRequest(...); // e.g., IP address
  
    // 2. Request an authorization decision
    Decision decision = permissionService.checkPermission(principal, action, resource, context);
  
    // 3. (Important) Persist the authorization decision
    // Regardless of whether the decision is ALLOW or DENY, it should be recorded for security analysis
    authorizationDecisionRepository.save(decision);
  
    // 4. If not allowed, throw an exception
    if (decision.isDeny()) {
        throw new AccessDeniedException("Reason: " + decision.getReason());
    }
  
    // 5. Execute the business operation
    BusinessOperation op = businessOperationService.create(operator, "UPDATE", resource, ...);
  
    // 6. (Important) Associate the decision with the operation
    decision.setOperationId(op.getId());
    authorizationDecisionRepository.save(decision); // Save again to update op_id
  
    // ... actual database update logic ...
}

This code looks more complex than before, but this complexity is valuable. It transforms an implicit, in-memory permission check process into an explicit, persistent data recording process. We moved the complexity from the "future audit" stage forward to the "current operation" stage -- a strategic investment.

Answering Li Lei's Question

Now, when auditor Li Lei comes again to ask why Wang Wu could modify the price, Zhang Wei no longer needs to explain verbally. He can execute a simple SQL JOIN query:

SELECT
    op.op_id,
    op.actor_id,
    op.verb,
    op.target_id,
    op.timestamp AS operation_time,
  
    auth.decision_id,
    auth.effect,
    auth.policy_id,
    auth.policy_version
FROM
    t_business_operation op
JOIN
    t_authorization_decision auth ON op.op_id = auth.op_id
WHERE
    op.id = 123;

The query result clearly shows: ... operation_type='UPDATE_PRICE', ..., effect='ALLOW', policy_id='OP-SPECIALIST-POLICY', policy_version='v2.1'

This report is self-explanatory. It not only tells Li Lei "what happened," but also perfectly answers "it was allowed because it conformed to the v2.1 version of the OP-SPECIALIST-POLICY policy in effect at the time," using the non-repudiable "evidence" of policy_id and policy_version.

If Li Lei wants to dig deeper, he can look up the policy content itself based on the policy ID and version number (policies themselves should also be stored as versioned data). The entire audit chain is complete, data-based, and requires no code archaeology.

By separating and associating business tables and authorization tables at the event level, we have built a truly robust auditing system. But this is not enough; we also need the audit log design itself to withstand the test of time.

Section 2: How to Design an Audit Table That Can Answer "Why" Without Tracing Code Versions

The previous section solved the separation of "operation" and "authorization." But a well-designed audit table has value far beyond that. It should be a "time machine" -- capable not only of recording the past, but also of independently and clearly telling the story of what happened, even when business rules, code logic, and even organizational structures have become completely unrecognizable in the future.

To achieve this goal, audit log design must follow a golden rule: de-normalization and de-codification.

Traditional database design teaches us to normalize and avoid data redundancy. But in the context of audit logs, moderate, strategic redundancy is key to ensuring long-term explainability. Because the core value of an audit log lies in "freezing context" -- it must capture all relevant information at the moment the event occurred, even if that information is redundant elsewhere.

Design Principle One: Redundantly Store Key "Nouns," Not Just "IDs"

In our t_business_operation table, we recorded actor_id and target_id. This was sufficient at the time, but what about five years later?

• actor_id = U-555: Five years later, user Wang Wu might have left the company. Their account might have been deleted from the user table or anonymized. U-555 would become an unresolvable "ghost reference."
• target_id = product/P-987: Five years later, product P-987 might have long since been delisted and soft-deleted from the product table. We might not easily know what the product's name was at that time.

A good audit table should not be strongly dependent on the future state of other business tables. It must snapshot all key "nouns" at the time the event occurs.

Improved t_business_operation table:

By adding the two JSON fields actor_snapshot and target_snapshot, we redundantly store the key descriptive information about the operator and the target object at the time of the event into the log.

Now, even if the user and product tables undergo radical changes five years later, an auditor can still clearly read from this log: "A person named 'Wang Wu' with the role 'Operations Specialist' modified a product named 'Starfish Brand T-Shirt-Red.'" The log becomes self-sufficient, no longer needing cross-table JOINs to guess what happened.

Design Principle Two: Record "Business Reason Codes," Don't Rely on "Code Logic"

When an order is automatically canceled by the system, our operation log might record: actor_id='SYSTEM', verb='CANCEL', target_id='order/O-123'

The auditor will ask: "Why did the system cancel this order?" The developer might answer: "Because our code logic is that an order is automatically canceled if it remains unpaid for more than 30 minutes."

This "30-minute unpaid" rule was hard-coded in the code version at that time. If, six months later, the rule is changed to 15 minutes, then when the auditor reviews this log from six months ago, the developer might mistakenly explain the old behavior using the new rule, leading to misunderstanding.

A good audit table must record the specific "business reason" that triggered the operation.

Improved t_business_operation table (adding reason code):

Now, when the system automatically cancels an order, the log written is: actor_id='SYSTEM', ..., reason_code='PAYMENT_TIMEOUT', reason_details='Exceeded 30 minutes payment window'

This log is "de-codified." It no longer requires a developer to recall or dig through old code to explain. The reason_code and reason_details fields have snapshotted and solidified the business rule that triggered the operation at that time within the log record.

• reason_code is a standardized enum value that machines can query.
• reason_details is a human-readable, detailed explanation.

This design makes the audit log an independent, reliable historical document of business, separate from the evolution of the code.

The Final "Self-Explanatory" Audit Model

Combining all the above principles, we arrive at a highly robust audit model consisting of two tables:

t_business_operation:

• op_id
• actor_id
• actor_snapshot (redundant)
• verb
• target_id
• target_snapshot (redundant)
• payload
• reason_code (business reason)
• reason_details (business reason details)
• timestamp

t_authorization_decision:

• decision_id
• op_id (foreign key)
• principal_id
• principal_snapshot (redundant)
• action
• resource_id
• resource_snapshot (redundant)
• effect
• policy_id (permission policy)
• policy_version (permission policy version)
• context
• timestamp

This model can answer almost any question an auditor might ask about "What" and "Why," and the answers are entirely contained within the data itself, requiring no external knowledge (such as old code or old organizational structures).

It achieves our ultimate goal: designing an audit table that can answer "why" without needing to trace back through code versions.

Chapter Summary: From "Recording" to "Storytelling"

In this chapter, we focused on mesoscopic-level table structure design, exploring how to build clear, auditable systems through separation of concerns.

We started from auditor Li Lei's dilemma, revealing the fundamental flaw of traditional operation logs: they only record "what happened," while ignoring "why it was allowed to happen." We pointed out that a successful business operation is the intersection of an "authorization event" and an "operation event," and our data model must be able to capture both events simultaneously.

To solve this problem, we proposed a core design principle: business tables and authorization tables must be separated at the event level. We designed an audit model consisting of two core tables:

1. t_business_operation: Specifically records the business operation itself.
2. t_authorization_decision: Specifically records the authorization decision that allowed the operation, linking the two via op_id. This model provides a non-repudiable "authorization credential" for each operation by recording policy_id and policy_version.

We then further deepened the design philosophy of audit logs, proposing two principles to make them "self-explanatory":

1. Redundantly store key "nouns," not just "IDs": Through fields like actor_snapshot, freeze the operation context in the log, making it independent of the future state of other volatile business tables.
2. Record "business reason codes," don't rely on "code logic": Through the reason_code field, capture the business rule that triggered the operation directly in the log, making it independent of code evolution.

Ultimately, the audit model we built is no longer a simple collection of "records," but individual "narrative" units capable of independently telling complete stories. Each associated "operation-authorization" record clearly tells a future reviewer a complete story about "who, at what time, based on what reason, and according to which policy, did what to what."

Having completed the mesoscopic-level "spacetime separation" and "separation of concerns," our system has become quite clear and robust in its internal structure. However, modern software systems are rarely monolithic. In the next part, "Macroscopic Decompression," we will once again raise our perspective to explore, in the distributed world composed of multiple services, how system boundaries should be drawn, how trust should be established, and how data should be redundantly stored and reconciled to ensure eventual consistency. This will be the highest level of challenge on our journey to refactoring software complexity.