祝融说。

"The past is never dead. It's not even past." — William Faulkner

If micro-level decompression is about clearing the clutter from a room, then mesoscopic decompression is about re-planning the room's layout. In this part, we will no longer be satisfied with fixing a single field; we will challenge the design philosophy of entire table structures. We will face the most elusive and destructive enemy in software systems -- time.

Most of our data models have a congenital, fatal flaw: they are "present tense." They excel at describing "what the world currently looks like," but are poor at answering "why the world came to look this way." This "present tense" design exposes huge cracks when access control decisions and business auditing are needed.

This part will introduce you to a "four-dimensional" data modeling mindset. We will learn how to introduce "time" as the fourth dimension into our data through table designs based on separation of concerns. Our goal is to build a system that can "travel through time" -- one that not only knows is, but also knows was and why.

Prologue: Customer Service Manager Sarah's Permission Nightmare

Sarah is a senior manager at the "Starfish E-commerce" customer service center. Her team uses an in-house ticketing system to handle post-sale user issues. The heart of this system is a t_ticket table, whose design seems clean and straightforward:

t_ticket table (V1.0):

The assignee_id field is the core of the entire system workflow. When a new ticket comes in, assignee_id is null. Sarah assigns it to Agent A, and assignee_id becomes A's ID. If A can't solve it, they reassign it to Agent B, and assignee_id is updated to B's ID.

One afternoon, Sarah receives an urgent call from the legal department. A VIP customer is complaining that a ticket containing sensitive personal information was seen by Zhang San, a customer service employee who has already left the company, suggesting a data leak. Legal requires Sarah to immediately provide evidence proving "whether Zhang San had permission to view this ticket, ID T-12345, at 3 PM yesterday."

Confidently, Sarah opens the admin panel. She knows the system's permission rules are simple: only the ticket's creator and current handler (assignee_id) can view it.

She queries ticket T-12345 and finds that creator_id is the VIP customer, and assignee_id is Agent Li Si. Zhang San's ID doesn't appear at all. She then checks the application's operation logs, which only have a simple record: "Ticket T-12345's assignee_id updated from Z-333 (Zhang San's ID) to L-444 (Li Si's ID)."

Sarah is stuck. She cannot prove anything to the legal department.

• Based on current data: assignee_id is Li Si, so Zhang San has no permission.
• Based on operation logs: Zhang San was once the handler.

So at 3 PM yesterday, when assignee_id was still Zhang San, he certainly had permission to view it. But after the ticket was reassigned to Li Si, the value of assignee_id was overwritten. The important historical fact that "Zhang San once had permission" has been erased from the core business data. The system only remembers "who is handling it now," but has forgotten "who was ever authorized."

This small assignee_id field plunged Sarah into a permission management nightmare. Behind this problem lies a deep design pitfall: we are trying to use a field that describes "current state" to answer questions about "historical intent."

• Current State: "Who is currently handling this ticket?" This is a transient, mutable, operational-level fact.
• Historical Intent: "Who has the system ever authorized to handle this ticket?" This is a persistent, cumulative, permission-level fact.

When we compress these two completely different concepts into the same assignee_id field, we are hand-crafting a forgetful system that cannot answer "why." In this chapter, we will perform a deep dissection of this "present tense" modeling malady, and introduce the "Snapshot Pattern," learning how to build table structures that can remember history and clearly define rights and responsibilities.

Section 1: The Multiple Dilemmas of assignee_id -- When a Field Is Expected to Do More Than It Can

The problem with assignee_id goes deeper than the audit dilemma Sarah encountered. The fundamental flaw of this field is that it is expected to simultaneously play four completely different roles, each with entirely different behavioral patterns along the "time" dimension.

Role One: Operator

This is assignee_id's most legitimate role. It answers a very specific question: "At this very moment, who should perform the next action on this ticket?"

• Time attribute: Instantaneous. The value of this role is highly volatile. A ticket may be reassigned three or four times in a day, and assignee_id changes accordingly.
• Business meaning: Workflow pointer. It's like a "whose turn is it" marker on a chessboard, guiding the system's next action.
• Design expectation: This value should be singular and overwritable. Because at any given point in time, there is typically one primary operator.

If assignee_id played only this role, its design would be perfectly reasonable. The problem is that we greedily assigned it more expectations.

Role Two: Permission Owner

This is the root cause of Sarah's nightmare. We have an unwritten rule: "Anyone who assignee_id has ever pointed to should be able to continue viewing the ticket's subsequent progress, even if they are no longer the current handler." For example, Agent A reassigns a complex problem to Technical Support B. Naturally, A wants to keep an eye on the ticket to see how B resolves it.

This role answers the question: "Who has been granted the right to access the content of this ticket?"

• Time attribute: Cumulative. This set only grows. Every time a ticket is reassigned, a new member is added to this set. Zhang San is reassigned to Li Si, so the permission owners change from {Zhang San} to {Zhang San, Li Si}.
• Business meaning: Access Control List. It defines a "circle" within which people can access the resource.
• Design expectation: This value should be a set, and it should be immutable, append-only.

Clearly, using a single, overwritable bigint field to carry the responsibility of a continuously growing set is fundamentally impossible. This is a fundamental design contradiction.

Role Three: Notification Subscriber

When the ticket's status changes (e.g., the user adds new information), who should be notified? Typically, we consider that the current handler (operator) and all historical handlers (permission owners) should receive the notification.

This role answers the question: "Who should be alerted when this ticket changes?"

• Time attribute: Dynamic subscription. This role's set can not only grow but also shrink. For example, after Agent A reassigns the ticket, they might choose to "unfollow," meaning they should no longer receive subsequent notifications.
• Business meaning: Observer list. It defines who has "subscribed" to update events for this ticket.
• Design expectation: This value should be a set that can both increase and decrease.

This is yet another new dimension. assignee_id is completely incapable of expressing such a complex subscription relationship.

Role Four: Audit Trail

This is the legal department's requirement. They need to know exactly: "At any point in the past, who was responsible for this ticket?"

• Time attribute: Historical snapshot. This role requires the ability to "time travel" and view the system state at any historical moment.
• Business meaning: Chain of responsibility. It records how responsibility has transferred over time.
• Design expectation: The system needs to store a series of state change records with timestamps.

Summarizing the dilemma of assignee_id:

We expect a single-value field to simultaneously satisfy the completely different data structure requirements of an append-only set, a mutable set, and a historical log. This is like asking a regular employee to simultaneously be the CEO, CFO, and corporate counsel -- doomed to fail.

The root of this problem is precisely the "conceptual compression" we emphasized in the previous part, but this time, the compressed core dimension is "time." We tried to use a flat, timeless "current state" to cover all the complex intents related to history, permissions, and subscriptions.

To untie this knot, the only solution is to perform a "spacetime separation" -- creating independent, dedicated storage models for "current state" and "historical intent" at the table structure level.

Section 2: The "Snapshot Pattern" -- Making Auditing Rely on Immutable Historical Records

Facing the dilemma of assignee_id, we can no longer try to "fix" this field. We must fundamentally change our modeling approach. We need to introduce a new design pattern, which I call the "Snapshot Pattern."

The core idea of the Snapshot Pattern is: when a business event occurs that would cause a change in permission, ownership, or responsibility state, the system should not simply overwrite the old state value. Instead, it should create a new, timestamped, immutable record that fully "snapshots" the key context at the moment that event occurred. Future permission decisions and audits should preferentially, or even exclusively, rely on these immutable snapshot records, rather than the mutable current state.

This is a shift in thinking from "state management" to "event recording."

• State management thinking: The world is a mutable state machine. We only care about its current state. UPDATE t_ticket SET assignee_id = ? WHERE id = ?
• Event recording thinking: The world is a river of history composed of immutable events. The current state is just the latest cross-section of this river. INSERT INTO t_ticket_assignment_log (ticket_id, from_user, to_user, timestamp) VALUES (...)

Let's apply the "Snapshot Pattern" to perform a thorough refactoring of the ticketing system.

Refactoring Table Structure: Separating Responsibilities

We will split the responsibilities of the t_ticket table.

t_ticket Table (Responsibility Reduced)

This table now only describes the ticket's "current snapshot" and "core attributes." Its fields should only contain information describing "what," not "who" or "how."

We deliberately renamed assignee_id to current_assignee_id. This naming itself serves as a reminder to developers: this is a mutable, "now-only" pointer. Do not use it for any historical or permission-related judgments!

t_ticket_participant Table (Introducing Historical Intent Snapshots)

This is the core of this refactoring. We create a new table specifically designed to record "who participated in this ticket and in what capacity." Each row in this table is an "authorization snapshot."

The design essence of this table lies in:

• It is append-only: Normally, we only INSERT data into this table, never UPDATE or DELETE. Each reassignment is not about modifying an old record, but adding a new one.
• It records the role: It clarifies the capacity in which the participant joined (assignee or follower), enabling more granular permission control in the future.
• It includes a timestamp: The granted_at field is the soul of this table. It firmly anchors the "authorization" action on the timeline.

Refactoring Business Logic: From Modifying State to Recording Events

Now, let's refactor the core "reassign ticket" business logic.

Old reassignTicket method:

@Transactional
public void reassignTicket(long ticketId, long newAssigneeId) {
    Ticket ticket = ticketRepository.findById(ticketId);
    // Simply overwriting state
    ticket.setAssigneeId(newAssigneeId);
    ticket.setUpdatedAt(new Date());
    ticketRepository.save(ticket);
  
    // Operation log (unstructured, hard to query)
    log.info("Ticket {} reassigned to {}", ticketId, newAssigneeId);
}

New reassignTicket method:

@Transactional
public void reassignTicket(long ticketId, long fromAssigneeId, long toAssigneeId, long operatorId) {
    Ticket ticket = ticketRepository.findById(ticketId);
  
    // 1. Update the current state pointer (single responsibility)
    ticket.setCurrentAssigneeId(toAssigneeId);
    ticket.setUpdatedAt(new Date());
    ticketRepository.save(ticket);
  
    // 2. Create a snapshot of the "old handler becoming a follower" (optional, based on business decision)
    // Optional: Keep the old assignee as a follower
    // participantRepository.updateRole(ticketId, fromAssigneeId, Role.FOLLOWER);
    // This could be an UPDATE on the role, or adding a new FOLLOWER record
    // For simplicity and immutability, let's stick to adding new records.
  
    // 3. Create a snapshot of the "new handler being authorized" (core step)
    TicketParticipant newAssigneeParticipant = new TicketParticipant(
        ticketId,
        toAssigneeId,
        Role.ASSIGNEE,
        new Date() // The moment of granting permission
    );
    participantRepository.save(newAssigneeParticipant);

    // 4. (Optional) Record a more detailed operation log
    // actionLogService.logReassign(...)
}

In this new logic, UPDATE and INSERT are given completely different meanings:

• UPDATE t_ticket ... is updating a non-critical, mutable "current state" cache.
• INSERT INTO t_ticket_participant ... is recording a critical, immutable "historical authorization" fact.

Resolving Sarah's Permission Nightmare

Now, let's see how the new design perfectly solves all the problems Sarah encountered.

Problem One: How to determine whether a user (Zhang San) has permission to view ticket T-12345?

Old logic (fragile):

Ticket ticket = ticketRepository.findById(ticketId);
boolean hasPermission = ticket.getCreatorId() == userId || ticket.getAssigneeId() == userId;

This logic is "time-sensitive." It will give completely different answers before and after a reassignment.

New logic (robust):

// Permission check now only relates to the immutable participant table
boolean hasPermission = participantRepository.existsByTicketIdAndUserId(ticketId, userId);

The logic of this query is: "As long as you have participated in this ticket in any capacity, you have permission to view it." Its result is "time-independent." No matter who the ticket is reassigned to now, as long as Zhang San was once an ASSIGNEE, his record eternally exists in the t_ticket_participant table, so this query will always return true.

Problem Two: The legal department needs to audit "whether Zhang San had permission at 3 PM yesterday."

With the t_ticket_participant table, this question becomes trivial.

SELECT COUNT(*) 
FROM t_ticket_participant
WHERE ticket_id = 'T-12345'
  AND user_id = 'Z-333' -- Zhang San's ID
  AND role = 'ASSIGNEE'
  AND granted_at <= 'YYYY-MM-DD 15:00:00'; -- Check if authorized before 3 PM
  -- AND (revoked_at IS NULL OR revoked_at > 'YYYY-MM-DD 15:00:00'); -- If revocation logic exists

The result of this SQL query is a credible, non-repudiable piece of legal evidence. It no longer relies on anyone's memory or unstructured logs, but directly on the timestamped business facts recorded in the database.

Problem Three: How to distinguish between "who is handling" and "who can see"?

• "Who is handling?" -> SELECT current_assignee_id FROM t_ticket WHERE id = ?
  • This is a simple state query to drive the workflow.
• "Who can see?" -> SELECT user_id FROM t_ticket_participant WHERE ticket_id = ?
  • This is a permission query for access control.

Through table splitting, we have physically isolated these two concepts, completely preventing the possibility of them being confused.

The essence of the Snapshot Pattern is acknowledging the value of "the past." By creating an immutable, append-only "fact log" table, it provides the system with a reliable "memory." This table becomes the foundation for all features that need to look back in time, such as permissions, auditing, and notifications. The cost we pay is an additional table and some extra write operations, but what we gain is a logically clear, well-defined, robust system that stands the test of time.

This is not just a database technique; it is a defensive design philosophy: don't trust the mutable present; trust the immutable past. When your system needs to make any critical decision (especially permissions and auditing), ask yourself: is my decision based on a status field that could be overwritten at any moment, or on an indelible, timestamped event record?

In the next chapter, we will continue to deepen this idea. We will explore how to apply this "separation of concerns" principle to broader table design, building a self-explanatory auditing system that can clearly answer "why."

Section 3: Beyond Auditing -- Additional Benefits of the "Snapshot Pattern"

The core value of the "Snapshot Pattern" is providing a solid foundation for auditing and permissions, but its advantages go far beyond that. Once we correctly introduce the "time" dimension into our data model and start recording immutable historical facts, it's like opening a treasure chest -- many previously thorny problems are easily solved.

Benefit One: Building a Rich "Ticket Timeline"

Under the old model, if the product manager wanted to display an activity timeline on the ticket detail page, similar to a "GitHub Issue" or "JIRA" page, it would be very difficult. We could only guess what happened from the vague updated_at field and unstructured logs.

Under the new model, the t_ticket_participant table itself is a prototype of a structured "activity log." We can easily aggregate it with other event logs (such as comments, status changes) to generate a rich, second-by-second precise ticket history timeline.

Querying the timeline for ticket T-12345:

-- Union query to build timeline view
(SELECT granted_at AS event_time, 'ASSIGNMENT' AS event_type, user_id AS subject_id, role AS details FROM t_ticket_participant WHERE ticket_id = 'T-12345')
UNION ALL
(SELECT created_at AS event_time, 'COMMENT' AS event_type, author_id AS subject_id, content AS details FROM t_ticket_comment WHERE ticket_id = 'T-12345')
UNION ALL
(SELECT changed_at AS event_time, 'STATUS_CHANGE' AS event_type, operator_id AS subject_id, CONCAT(old_status, ' -> ', new_status) AS details FROM t_ticket_status_log WHERE ticket_id = 'T-12345')
ORDER BY event_time ASC;

This query result can be directly rendered by the front end as a clear user interface, showing users and customer service agents the complete journey of the ticket from creation to resolution. This greatly enhances product transparency and user experience.

Benefit Two: Achieving Precise SLA Calculation

Customer service centers typically have strict SLA metrics, such as "the average time from ticket assignment to first response should not exceed 30 minutes."

Under the old model, this metric is nearly impossible to calculate. Because when a ticket is reassigned from A to B, the time point when A was assigned is lost. We can only calculate the total ticket duration, not how long it stayed with each handler.

Under the new model, the t_ticket_participant table precisely records the timestamp of each ASSIGNMENT event. Calculating SLA becomes very simple.

Calculating the duration Agent A handled ticket T-12345: We can use a window function to calculate the duration of each assignment.

SELECT
    user_id,
    granted_at AS assignment_start,
    LEAD(granted_at, 1, NOW()) OVER (ORDER BY granted_at) AS assignment_end,
    -- Calculate time difference
    TIMESTAMPDIFF(MINUTE, granted_at, LEAD(granted_at, 1, NOW()) OVER (ORDER BY granted_at)) AS duration_in_minutes
FROM
    t_ticket_participant
WHERE
    ticket_id = 'T-12345' AND role = 'ASSIGNEE';

This query returns the start time (assignment_start), end time (assignment_end), and duration for each handler (user_id). Based on this precise data, manager Sarah can generate detailed agent performance reports, identify bottlenecks in the process, and optimize the efficiency of the entire customer service team.

Benefit Three: Simplifying Revocation and Rollback Logic

Imagine a scenario: Manager Sarah incorrectly assigns a technical ticket to Agent A. She wants to "revoke" this assignment.

Under the old model, "revocation" is a vague operation. Do we change assignee_id back to the previous value? What was the previous value? We'd have to dig through the logs to find out. This operation is lossy and error-prone.

Under the new model, "revocation" can be modeled as a clear, new business event. Instead of deleting the old record, we append a new "revocation" record.

We could extend the t_ticket_participant table, or create a new t_ticket_assignment_action table to record:

t_ticket_assignment_action table:

When we need to determine Zhang San's permission, we not only check if he was GRANTED, but also whether that GRANT was REVOKED. This makes the entire permission change history traceable and auditable. This event-append-based approach is the foundation for building a reliable "revocation" function, and is a core idea of the Event Sourcing architectural pattern.

Chapter Summary: Install a "Time Machine" for Your Data Model

In this chapter, we started with a seemingly simple assignee_id field and revealed the deep crisis of "present tense" data modeling. We discovered that when a field is expected to simultaneously carry the multiple roles of operator, permission owner, notification subscriber, and audit trail, it inevitably collapses because it cannot handle the "time" dimension.

To solve this dilemma, we introduced the "Snapshot Pattern," a design philosophy that fundamentally changes table structure design. Its core points are:

1. Separate responsibilities: Separate the mutable data describing "current state" (e.g., current_assignee_id) from the immutable data describing "historical intent," storing them in different tables.
2. Record events, not states: Use an append-only, timestamped "snapshot" table (e.g., t_ticket_participant) to record each key business event (e.g., authorization, reassignment).
3. Trust history: Base critical business logic such as permission decisions and audit queries entirely on these immutable "snapshot" records, rather than on the current state that could be overwritten at any time.

By implementing the "Snapshot Pattern," we not only solved the initial permission audit problem, but also unexpectedly gained a series of additional benefits such as building a "feature timeline," precisely calculating "SLA," and simplifying "revocation logic."

The application scope of this pattern extends far beyond ticketing systems. In your business, be wary of the following types of fields:

• order.handler_id (Order handler)
• document.owner_id (Document owner)
• project.manager_id (Project manager)
• user.approver_id (User's approver)

All these fields, like assignee_id, have the connotation of "ownership" or "responsibility," and therefore are highly susceptible to the "current state vs. historical intent" trap. When you encounter them, ask yourself:

• Will I need to know what this field used to be?
• Will changes to this field affect user permissions?
• In the future, will I need to audit every change to this field?

If the answer to any of these is "yes," then decisively adopt the "Snapshot Pattern" and install a reliable "time machine" for your data model.

In our journey of mesoscopic decompression, we have learned how to handle the "time" dimension. In the next chapter, we will explore "space" -- or more precisely, the boundary of responsibilities. We will learn how to design tables with more singular responsibilities, completely separating business operations from access control, thereby building a self-explanatory auditing system that can answer "why" without needing to trace back through code versions.