How Enterprise Customers Forced Us to Rebuild Our Database Architecture

"Can multiple people work on the same project?"

This question highlighted a limitation in our user-centric application, where every piece of content belonged to exactly one user.

Over the next six months, we heard similar requests:

• "How do we share documents across teams?"
• "Can managers see all projects in their department?"
• "We need workspace-level billing and admin controls."
• "What happens when employees leave the company?"

We realized we had an architecture challenge. We'd built a solid individual productivity tool, but enterprise customers needed team collaboration features. This meant implementing workspaces.

The Problem We Never Saw Coming

Our Individual User Model

Our original schema was clean and simple. Every table had a direct foreign key to the Users table - projects belonged to users, documents belonged to users, files belonged to users. It was a straightforward one-to-many relationship model that worked perfectly for individual productivity.

The schema enforced cascade deletes, meaning when a user was removed, all their content disappeared with them. This seemed like good data hygiene at the time.

The Wake-Up Call

The issue became clear during a demo with an enterprise prospect. Their IT director asked:

"So if Sarah from marketing creates a campaign document, how does the design team collaborate on it? And what happens when Sarah goes on maternity leave?"

I had to explain that sharing would need to be done manually, and if Sarah was unavailable, they'd need admin access to her account.

It wasn't the answer they were looking for.

The Real Enterprise Requirements:

• Team Collaboration: Multiple people working on shared content simultaneously
• Organizational Hierarchy: Managers need visibility into team projects
• Employee Lifecycle: Content can't disappear when people leave
• Compliance: Audit trails and access controls at the organizational level
• Billing: Per-workspace pricing instead of per-user

The Analysis That Changed Everything

When we analyzed our usage patterns, we discovered something interesting. By grouping users by email domain, we found that many of our "individual" users were actually from the same organizations.

What the data showed:

• Many users shared email domains with other users
• Several domains generated multiple sharing-related support tickets
• Users from the same organization were creating separate projects due to collaboration limitations

The pattern was clear: teams were trying to work together, but our architecture forced them into individual silos.

Support Ticket Patterns

Our support tickets revealed collaboration attempts:

1-- Users with high content creation (potential team leads)
2SELECT
3    u.Email,
4    u.FirstName,
5    u.LastName,
6    COUNT(DISTINCT p.Id) as ProjectCount,
7    COUNT(DISTINCT d.Id) as DocumentCount,
8    COUNT(DISTINCT f.Id) as FileCount,
9    MAX(d.LastModified) as LastActivity
10FROM Users u
11LEFT JOIN Projects p ON u.Id = p.UserId
12LEFT JOIN Documents d ON u.Id = d.UserId
13LEFT JOIN Files f ON u.Id = f.UserId
14GROUP BY u.Id, u.Email, u.FirstName, u.LastName
15ORDER BY ProjectCount DESC, DocumentCount DESC;

This analysis helped us identify potential workspace owners and understand collaboration patterns within organizations.

Designing the New Workspace Architecture

The Target Schema

Here's our new workspace-based schema that maintains backward compatibility while enabling collaboration:

1-- New workspace-based schema
2CREATE TABLE Workspaces (
3    Id UNIQUEIDENTIFIER PRIMARY KEY,
4    Name NVARCHAR(255) NOT NULL,
5    Description NTEXT NULL,
6    OwnerId UNIQUEIDENTIFIER NOT NULL,
7    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
8    UpdatedAt DATETIME2 DEFAULT GETUTCDATE(),
9    IsPersonal BIT DEFAULT 0, -- Flag for user's personal workspace
10    FOREIGN KEY (OwnerId) REFERENCES Users(Id) ON DELETE CASCADE
11);
12
13CREATE TABLE WorkspaceMembers (
14    Id UNIQUEIDENTIFIER PRIMARY KEY,
15    WorkspaceId UNIQUEIDENTIFIER NOT NULL,
16    UserId UNIQUEIDENTIFIER NOT NULL,
17    Role NVARCHAR(50) NOT NULL DEFAULT 'Member', -- Owner, Admin, Member, Viewer
18    JoinedAt DATETIME2 DEFAULT GETUTCDATE(),
19    InvitedBy UNIQUEIDENTIFIER NULL,
20    FOREIGN KEY (WorkspaceId) REFERENCES Workspaces(Id) ON DELETE CASCADE,
21    FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE,
22    FOREIGN KEY (InvitedBy) REFERENCES Users(Id) ON DELETE SET NULL,
23    UNIQUE(WorkspaceId, UserId)
24);
25
26-- Modified Projects table
27CREATE TABLE Projects_New (
28    Id UNIQUEIDENTIFIER PRIMARY KEY,
29    WorkspaceId UNIQUEIDENTIFIER NOT NULL,
30    CreatedByUserId UNIQUEIDENTIFIER NOT NULL,
31    Title NVARCHAR(255) NOT NULL,
32    Description NTEXT,
33    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
34    UpdatedAt DATETIME2 DEFAULT GETUTCDATE(),
35    FOREIGN KEY (WorkspaceId) REFERENCES Workspaces(Id) ON DELETE CASCADE,
36    FOREIGN KEY (CreatedByUserId) REFERENCES Users(Id) ON DELETE RESTRICT
37);
38
39-- Modified Documents table
40CREATE TABLE Documents_New (
41    Id UNIQUEIDENTIFIER PRIMARY KEY,
42    ProjectId UNIQUEIDENTIFIER NOT NULL,
43    WorkspaceId UNIQUEIDENTIFIER NOT NULL, -- Denormalized for faster queries
44    CreatedByUserId UNIQUEIDENTIFIER NOT NULL,
45    LastModifiedByUserId UNIQUEIDENTIFIER NOT NULL,
46    Title NVARCHAR(255) NOT NULL,
47    Content NTEXT,
48    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
49    LastModified DATETIME2 DEFAULT GETUTCDATE(),
50    Version INT DEFAULT 1,
51    FOREIGN KEY (ProjectId) REFERENCES Projects_New(Id) ON DELETE CASCADE,
52    FOREIGN KEY (WorkspaceId) REFERENCES Workspaces(Id) ON DELETE CASCADE,
53    FOREIGN KEY (CreatedByUserId) REFERENCES Users(Id) ON DELETE RESTRICT,
54    FOREIGN KEY (LastModifiedByUserId) REFERENCES Users(Id) ON DELETE RESTRICT
55);
56
57-- Modified Files table
58CREATE TABLE Files_New (
59    Id UNIQUEIDENTIFIER PRIMARY KEY,
60    WorkspaceId UNIQUEIDENTIFIER NOT NULL,
61    DocumentId UNIQUEIDENTIFIER NULL,
62    UploadedByUserId UNIQUEIDENTIFIER NOT NULL,
63    FileName NVARCHAR(255) NOT NULL,
64    FilePath NVARCHAR(500) NOT NULL,
65    FileSize BIGINT NOT NULL,
66    MimeType NVARCHAR(100),
67    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
68    FOREIGN KEY (WorkspaceId) REFERENCES Workspaces(Id) ON DELETE CASCADE,
69    FOREIGN KEY (DocumentId) REFERENCES Documents_New(Id) ON DELETE SET NULL,
70    FOREIGN KEY (UploadedByUserId) REFERENCES Users(Id) ON DELETE RESTRICT
71);

Key Design Decisions

Personal Workspaces: Every user gets a personal workspace for backward compatibility, ensuring existing content remains accessible.

The Migration Strategy: Phased Approach

Phase 1: Schema Preparation

We started by adding the new workspace tables alongside the existing user-centric schema. This allowed us to run both systems simultaneously during the migration period.

The key was creating a migration tracking table that monitored progress for each user individually. This let us handle failures gracefully - if one user's migration failed, we could retry just that user without affecting others.

Phase 2: Personal Workspace Creation

Every existing user needed a personal workspace to maintain backward compatibility. We built this as a stored procedure that processed users in batches, creating both the workspace and the initial membership record.

The procedure used transactions to ensure data consistency - if creating the workspace succeeded but adding the membership failed, we'd roll back the entire operation for that user. Error handling was crucial since this step affected every user in the system.

Naming the workspaces was surprisingly tricky. We settled on "[FirstName]'s Personal Workspace" when available, falling back to email-based names for users with missing profile information.

Phase 3: Data Migration with Batching

The actual content migration was the most complex part. We moved projects, documents, and files from user ownership to workspace ownership, maintaining all the relationships between them.

Batching was essential to avoid long-running transactions that could lock tables and impact performance. We processed 100 records at a time, with small delays between batches to let other operations proceed.

The migration logic processed each user individually, migrating their projects first, then documents, then files. This order maintained referential integrity - documents needed their parent projects to exist in the new schema before they could be migrated.

Each content type was migrated in small batches with delays between operations. This prevented the migration from overwhelming the database and allowed normal user operations to continue. If any batch failed, we'd log the error and continue with the next user.

The most complex part was handling the relationships correctly. Documents needed to reference both their original project and the new workspace. Files could optionally reference documents, and we had to preserve those relationships while changing the ownership model.

Progress tracking was essential for troubleshooting. When migrations failed, we could see exactly which user and which content type had problems, making debugging much faster.

Application Layer Migration

1. Abstraction Layer for Dual Operation

Note: This approach requires significant development effort and may not be suitable for all migrations. Consider your timeline, resources, and business requirements before implementing dual operation.

When to use dual operation:

• Long migration periods (weeks/months)
• High availability requirements
• Large user base requiring gradual rollout
• Complex data relationships requiring careful validation

When to avoid:

• Short migration windows (hours/days)
• Limited development resources
• Simple schema changes
• Small user base where downtime is acceptable

Alternative approaches to consider:

• Maintenance window migration: Schedule downtime for direct cutover
• Blue-green deployment: Migrate to new environment, then switch traffic
• Read-only mode: Put application in read-only during migration
• Feature freeze: Stop new features, focus entirely on migration

If you do choose dual operation, create an abstraction layer:

1public interface IContentRepository
2{
3    Task<IEnumerable<Project>> GetUserProjectsAsync(Guid userId);
4    Task<Project> GetProjectAsync(Guid projectId, Guid userId);
5    Task<Project> CreateProjectAsync(CreateProjectRequest request);
6    Task<Document> CreateDocumentAsync(CreateDocumentRequest request);
7}
8
9// Implementation that works with both schemas during migration
10public class MigrationContentRepository : IContentRepository
11{
12    private readonly string _connectionString;
13    private readonly IFeatureManager _featureManager;
14    private readonly ILogger<MigrationContentRepository> _logger;
15
16    public async Task<IEnumerable<Project>> GetUserProjectsAsync(Guid userId)
17    {
18        var useWorkspaceModel = await _featureManager.IsEnabledAsync("UseWorkspaceModel", userId.ToString());
19
20        if (useWorkspaceModel)
21        {
22            return await GetProjectsFromWorkspaceModel(userId);
23        }
24        else
25        {
26            return await GetProjectsFromLegacyModel(userId);
27        }
28    }
29
30    private async Task<IEnumerable<Project>> GetProjectsFromWorkspaceModel(Guid userId)
31    {
32        const string sql = @"
33            SELECT DISTINCT p.Id, p.Title, p.Description, p.CreatedAt, p.UpdatedAt,
34                   w.Name as WorkspaceName, w.Id as WorkspaceId
35            FROM Projects_New p
36            INNER JOIN Workspaces w ON p.WorkspaceId = w.Id
37            INNER JOIN WorkspaceMembers wm ON w.Id = wm.WorkspaceId
38            WHERE wm.UserId = @UserId
39            ORDER BY p.UpdatedAt DESC";
40
41        using var connection = new SqlConnection(_connectionString);
42        var projects = await connection.QueryAsync<Project>(sql, new { UserId = userId });
43
44        return projects.Select(p => new Project
45        {
46            Id = p.Id,
47            Title = p.Title,
48            Description = p.Description,
49            CreatedAt = p.CreatedAt,
50            WorkspaceId = p.WorkspaceId,
51            WorkspaceName = p.WorkspaceName
52        });
53    }
54
55    private async Task<IEnumerable<Project>> GetProjectsFromLegacyModel(Guid userId)
56    {
57        const string sql = @"
58            SELECT Id, Title, Description, CreatedAt, UserId
59            FROM Projects
60            WHERE UserId = @UserId
61            ORDER BY CreatedAt DESC";
62
63        using var connection = new SqlConnection(_connectionString);
64        var projects = await connection.QueryAsync<Project>(sql, new { UserId = userId });
65
66        return projects.Select(p => new Project
67        {
68            Id = p.Id,
69            Title = p.Title,
70            Description = p.Description,
71            CreatedAt = p.CreatedAt,
72            // Map to personal workspace concept for UI consistency
73            WorkspaceId = Guid.Empty, // Indicates legacy mode
74            WorkspaceName = "Personal"
75        });
76    }
77}

Permission System Evolution

The workspace model required completely rethinking our permission system. Instead of simple "user owns content" checks, we now had role-based permissions within workspaces.

Permission checking became more complex but more flexible. A user's access to a project depended on their workspace membership and role. Viewers could read content, Members could edit, and Admins could manage workspace settings. The permission service handled these checks by joining projects with workspace memberships.

The tricky part was performance - permission checks happened on every request, so they needed to be fast. We optimized the queries and added caching for workspace memberships since they changed infrequently.

Data Integrity and Validation

Before flipping the final switch, we built comprehensive validation to ensure our migration hadn't introduced any data corruption. The challenge was verifying referential integrity across multiple related tables without impacting production performance.

Migration Validation Strategy

We created validation scripts that checked migration completeness at multiple levels. The most critical validation was ensuring that every user had been assigned a personal workspace, since this was fundamental to the new model. We also verified that all projects, documents, and files had been migrated with their correct relationships intact.

The validation needed to run quickly since we were doing final checks during the deployment window. We used aggregate queries to quickly identify any gaps in the migration rather than row-by-row comparisons.

Foreign Key Constraint Validation

The trickiest part was validating the new foreign key relationships. We had to verify that every migrated record maintained its proper workspace association and that permission inheritance worked correctly throughout the hierarchy.

We created queries to identify orphaned records at every level - projects without workspaces, documents without projects, and files without their parent entities. Finding any orphaned data would have meant rolling back the migration to investigate the issue.

The most important validation was ensuring that users could still access all the content they previously owned, now through their workspace memberships. We cross-referenced the old user-based ownership with the new workspace-based access to verify that nothing was lost in translation.

Performance Optimization During Migration

The new workspace model introduced additional table joins, which required careful index strategy to maintain query performance. The challenge was that queries now needed to traverse workspace membership relationships to determine access, adding complexity to what were previously simple user-based lookups.

Index Strategy for New Tables

We designed indexes specifically for the new query patterns. The workspace membership table needed composite indexes since most queries filtered by both user and role. Projects and documents required indexes on their workspace associations since these became the primary access control mechanism.

The most important optimization was creating covering indexes that included commonly selected columns. This allowed SQL Server to satisfy entire queries from the index without accessing the base tables, dramatically improving performance for dashboard and list views.

Query Optimization Challenges

The biggest performance challenge was adapting queries that previously did simple user-based filtering. In the old model, finding a user's projects was a single table lookup. In the workspace model, it required joins across workspaces and membership tables.

We had to rewrite queries to efficiently handle the permission inheritance model. Document searches, in particular, became more complex since they needed to verify access through the workspace-project-document hierarchy. The key was using proper join strategies and ensuring the query planner could use our indexes effectively.

We also discovered that denormalizing workspace information directly into the project and document tables improved query performance significantly. While this created some data duplication, the performance gains for our most common queries justified the trade-off.

Zero-Downtime Deployment Strategy

Feature Flag Controlled Rollout

The key to zero-downtime deployment was using feature flags to control which users experienced the new workspace model. This allowed us to migrate users gradually while keeping the application running normally for everyone else.

We implemented a layered feature flag system that checked both global rollout percentages and individual user migration status. A user would only see the workspace model if their migration was complete AND the feature was enabled for them. This prevented users from accessing the new system before their data was ready.

The gradual rollout also gave us confidence in the system. We started with 5% of users, then 25%, 50%, and finally 100%. At each stage, we could monitor performance and user feedback before proceeding.

Rollback Strategy

Having a reliable rollback strategy was crucial for confidence during deployment. We created procedures that could quickly reverse a user's migration if they encountered issues with the new workspace model.

The rollback process had to be surgical - removing the user's migrated data from the new tables while preserving their personal workspace and original data. The most complex part was handling workspace memberships, since rolling back a user shouldn't affect other members of shared workspaces.

We practiced the rollback procedures extensively in staging environments. During the actual migration, we had to use the rollback twice - once for a user who reported data access issues, and once for a user whose migration had incomplete file references. Both rollbacks completed successfully within minutes.

Production Monitoring and Alerting

Monitoring during the migration was critical for catching issues before they affected users. We built a real-time dashboard that showed migration progress across our entire user base, including success rates, error patterns, and performance metrics.

Migration Progress Dashboard

The dashboard provided visibility into the migration's health at a glance. We tracked completion rates, average migration times, and failure patterns. This helped us identify when certain user types were experiencing issues - for example, users with large numbers of files took significantly longer to migrate.

The error tracking was particularly valuable. We could quickly identify common failure patterns and fix them before they affected more users. Most errors were related to file permissions or orphaned references that hadn't been cleaned up in the original data.

Performance Monitoring

We instrumented both the old and new code paths to compare performance in real-time. This helped us verify that the workspace model was delivering the expected performance improvements for most queries.

The metrics also revealed some unexpected patterns. While simple project queries were faster in the workspace model, complex cross-project searches initially performed worse due to the additional joins. This led us to create additional indexes and optimize those specific queries.

We also tracked batch processing performance during the migration itself. Monitoring batch sizes and completion times helped us optimize the migration process and predict completion times for remaining users.

Common Pitfalls and Solutions

1. Foreign Key Constraint Violations

Problem: Migrating child records before parent records creates constraint violations.

Solution: Always maintain topological order in your migration. We learned this the hard way when our first migration attempt failed because we tried to migrate projects before their workspaces existed. The solution was establishing a clear dependency order: create workspaces first, then workspace memberships, then projects, documents, and finally files.

2. Performance Degradation During Migration

Problem: Large batch operations lock tables and impact user experience.

Solution: Implement adaptive batch sizing. We discovered that users with different amounts of data required different batch sizes for optimal performance. Users with thousands of files needed smaller batches to avoid timeouts, while users with minimal data could handle larger batches.

The key was monitoring batch execution time and dynamically adjusting the size. If a batch took too long, we'd reduce the size for the next iteration. If it completed quickly, we'd increase it. This adaptive approach kept the migration running smoothly regardless of data volume.

3. Data Inconsistency Issues

Problem: Application reads from old and new schemas simultaneously, causing inconsistent data views.

Solution: Implement read-through caching and eventual consistency checks. During the transition period, the application needed to handle users who were partially migrated - some of their data in the old schema, some in the new.

We implemented a service layer that could query both schemas and merge the results intelligently. If data existed in both places, we preferred the workspace model but validated consistency. This approach prevented users from seeing incomplete or conflicting information during their migration.

The consistency checking service became essential during the transition period. It would fetch data from both the legacy and workspace models, compare them, and return the most complete version. If inconsistencies were detected, we'd log them for investigation while still providing users with their data.

The Results

After implementing workspace functionality, we saw positive changes:

Improvements:

• Support tickets about sharing decreased significantly
• Enterprise prospects were more interested in our collaboration features
• Teams using workspaces showed better engagement
• New collaboration features saw good adoption

What We Learned:

User support tickets often reveal product architecture limitations. The "How do I share this?" questions weren't just about user education—they indicated our data model didn't match how teams actually work.

Enterprise software requires thinking about collaboration from the ground up. Individual user models with sharing bolted on don't provide the same experience as native team functionality.

Would We Do It Again?

Yes, though we'd pay more attention to early signals. Support ticket patterns and sales call feedback often reveal architecture issues before they become blocking problems.

For B2B software, it's worth considering team collaboration needs early in the design process rather than retrofitting individual user models.