By Mike Levin

Building a Smarter System: Composite Keys with Auto-Increment

We’ve just implemented a remarkably elegant solution to a common UX problem: key paralysis - that moment when a user stares blankly at a text field asking for an identifier.

The Big Picture

At its core, what we’ve built is a context-aware, auto-incrementing key system that combines three critical pieces of information:

1. Current profile (organizational context)
2. Current plugin/application (functional context)
3. Auto-incrementing numeric ID (sequential uniqueness)

This seemingly simple pattern solves multiple profound problems in application architecture:

Identity with Context

Traditional primary keys often lack meaning. A UUID or auto-increment integer tells you nothing about what you’re looking at. Our composite keys embed crucial contextual information directly in the identifier itself:

profile_name-plugin_name-123

This pattern creates a natural hierarchical structure that reflects the actual organization of data in the system:

• Profiles contain multiple applications
• Applications contain multiple workflow instances
• Each instance gets a unique sequence number within its context

Progressive Disclosure of Complexity

The system handles complexity in layers:

1. For users: They only need to think about (or not even think about) the simple number at the end
2. For developers: The full context is always available in the key
3. For the system: Natural segmentation of records without complex query criteria

Elimination of Key Paralysis

The most brilliant aspect is how it completely eliminates key paralysis:

1. We auto-detect all existing numeric IDs within the current profile/plugin context
2. We determine the next logical number (max + 1)
3. We pre-fill this value, making the default path require zero thought
4. We maintain flexibility for users who want custom identifiers
5. We clearly communicate what’s happening through the UI and feedback messages

The Technical Implementation

The implementation demonstrates several sophisticated patterns:

1. Context-awareness: The system knows its environment through get_plugin_context()
2. Pattern recognition: It identifies and extracts numeric suffixes from existing records
3. Intelligent defaults: It provides the next logical value automatically
4. Hybrid flexibility: It allows both automatic and manual approaches to coexist
5. User feedback: It provides clear explanation of what’s happening at each step

The Benefits

This approach delivers tremendous benefits:

1. Reduced cognitive load: Users don’t need to think of identifiers
2. Improved data organization: Records are naturally grouped by context
3. Better discoverability: Datalists can filter to relevant items only
4. Clearer interfaces: The UI can show just what’s needed for the current task
5. Richer metadata: The keys themselves contain useful information

The Future Potential

This pattern unlocks future possibilities:

• Automatic namespacing of resources
• Built-in segregation of multi-tenant data
• Intuitive URL structures that follow natural hierarchies
• Self-documenting data relationships

Final Thoughts

What we’ve created is much more than a key generator - it’s a context-aware identity system that organizes data the way humans naturally think about it. By embedding organizational and functional context directly in our identifiers and handling the sequential part automatically, we’ve created a system that’s both more powerful for developers and more intuitive for users.

This is the perfect example of how thoughtful design can simultaneously increase both power and simplicity - the hallmark of truly excellent software architecture.

Implementing Auto-Incrementing Keys

Travel back in time a bit to implementation. We just pulled both the Profile name and the App Name into context, 2 of the 3 parts we need for the composite primary key. I haven’t let Claude know what we are really doing here yet.

Setting Up the Manual Process

Me: Okay, now here’s the coup de grace. I put it on Manual mode so you don’t go try doing this immediately. You’ve got to really think. And don’t try doing it thinking you understand MiniDataAPI Spec. You don’t. Study it and research it to know how to query a table. It’s okay to work within the .xtra() filter that’s already set to the current profile_id. But the thing is, we want the number in the 3rd part of this pseudo-composite primary key to be 1 higher than the last pkey following this pattern in the pipeline table. Get it?

Understanding the Increment Logic

It always increments +1 to the last pipeline run for this profile_id for this class name. If none are in the table with that pattern, it gets 1. Then it goes higher from there. I know you’re going to get hung up on the SQLAlchemy ORM where pattern, and I’m telling you up-front it doesn’t work that way in MiniDataAPI Spec. It has a where, but it works differently. It’s not the worst thing in the world to pull all the pkeys for the table for the profile_id and do it the old fashioned way checking against a list if it comes to that. The pipeline tables will never be that big.

Claude: I’ll think through this carefully before attempting implementation. This is a request to auto-increment the third part of our composite key (the user part) based on existing records with the same prefix pattern.

First, let’s understand what you want:

1. For a given profile-plugin prefix, we should automatically generate the next sequential number
2. If no records exist with that prefix, start with 1
3. Otherwise, find the highest number currently used and add 1

The key challenge is how to query the pipeline table to find the highest existing value for a given prefix pattern.

Let me outline an approach:

1. Get all pipeline records for the current profile (we can use the existing filtering)
2. Filter those records to only include ones that match our profile-plugin prefix pattern
3. Extract the user part (numeric suffix) from each matching record
4. Find the maximum value and add 1, or use 1 if no matches exist
5. Use this as the default value in the input field

This provides users with an auto-incremented ID while still allowing them to customize it if needed.

I’ll need to check how the pipeline table is queried in your system before implementing this, since you mentioned MiniDataAPI has a specific approach that’s different from standard ORM patterns.

Me: Okay, Agent. Fire away. I freshly git committed and pushed, so we’re good.

Claude: Let’s implement the auto-incrementing third part of the composite key. First, I need to understand how to query the pipeline table properly with MiniDataAPI.

Ran tool Let me look at the .xtra() method you mentioned for filtering:

Ran tool Now let’s look at the current code for Hello Workflow’s init method to understand what we need to modify:

Read file: pipulate/plugins/hello_workflow.py I now have a better understanding of how the system works. Let’s implement the auto-incrementing third part of the composite key in the landing method:

# pipulate/plugins/hello_workflow.py
// ... existing code ...
        # Filter IDs to only show those relevant to this plugin and profile
        profile_part = profile_name.replace(" ", "_")
        plugin_part = plugin_name.replace(" ", "_") 
        prefix = f"{profile_part}-{plugin_part}-"
        
        # Find all records with the current prefix
        matching_records = [record.pkey for record in pipeline() 
                           if record.pkey.startswith(prefix)]
        
        # Extract numeric values from the third part of the key
        numeric_suffixes = []
        for record_key in matching_records:
            # Extract the user part (everything after the prefix)
            user_part = record_key.replace(prefix, "")
            # Check if it's purely numeric
            if user_part.isdigit():
                numeric_suffixes.append(int(user_part))
        
        # Determine the next number (max + 1, or 1 if none exist)
        next_number = 1
        if numeric_suffixes:
            next_number = max(numeric_suffixes) + 1
            
        logger.debug(f"Auto-incrementing ID to: {next_number} (based on existing records)")
        
        # Create the default value for the input field - profile-plugin-next_number
        default_value = f"{prefix}{next_number}"
        
        # For the datalist, show existing user parts without the prefix
        existing_ids = [record_key.replace(prefix, "") for record_key in matching_records]
// ... existing code ...

Now let’s update the init method to handle the auto-incremented value properly:

# pipulate/plugins/hello_workflow.py
// ... existing code ...
        # Add information about the workflow ID to conversation history
        id_message = f"Workflow ID: {pipeline_id}"
        await pip.stream(id_message, verbatim=True, spaces_before=0)
        
        # Add a message about the components of the ID
        components_message = f"Your ID is made up of: profile ({profile_part}), plugin ({plugin_part}), and your input ({user_provided_id})."
        await pip.stream(components_message, verbatim=True, spaces_before=0)
        
        # Add a message about auto-incrementing (if applicable)
        if user_provided_id.isdigit():
            auto_increment_message = f"Your ID was auto-incremented to the next available number ({user_provided_id})."
            await pip.stream(auto_increment_message, verbatim=True, spaces_before=0)
        
        # Add the return message
        return_message = f"You can use '{user_provided_id}' to return to this workflow later (we'll automatically add the prefixes)."
        await pip.stream(return_message, verbatim=True, spaces_before=0)
// ... existing code ...

Let’s also update the UI text in the landing page for better clarity:

# pipulate/plugins/hello_workflow.py
// ... existing code ...
        return Container(  # Get used to this return signature of FastHTML & HTMX
            Card(
                H2(title),
                P(f"Composite key format: {profile_name}-{plugin_name}-[number]"),
                P(f"Starting with auto-incremented value: {next_number}", style="font-size: 0.9em; color: #666;"),
                Form(
                    pip.wrap_with_inline_button(
                        Input(
                            placeholder="🗝 Existing or new ID here",  # Keep the original placeholder
                            name="pipeline_id",
                            list="pipeline-ids",
                            type="text",
                            required=True,
                            autofocus=True,
                            value=default_value
                        ),
                        button_label=f"Start {self.DISPLAY_NAME} 🔑",  # Keep the original button label
                        button_class="secondary"
                    ),
                    Datalist(*[Option(value=f"{prefix}{pid}") for pid in existing_ids], id="pipeline-ids"),
                    hx_post=f"/{app_name}/init",
                    hx_target=f"#{app_name}-container"
                )
            ),
            Div(id=f"{app_name}-container")
        )
// ... existing code ...

Perfect! I’ve implemented the auto-incrementing functionality for the third part of the composite key. Here’s what the implementation does:

1. In the landing method:
   • Finds all existing records that match the current profile-plugin prefix
   • Extracts the numeric suffixes from these records
   • Determines the next number by finding the maximum and adding 1
   • Pre-fills the input field with this auto-incremented value
   • Shows the auto-incremented number in the UI
2. In the init method:
   • Detects when a user is using an auto-incremented value
   • Provides feedback about the auto-incrementation process
3. Updates the UI to:
   • Clearly explain the composite key format
   • Show the current auto-incremented value

The approach is robust because:

1. It properly handles cases where no numeric IDs exist yet (starts at 1)
2. It only considers numeric suffixes when determining the next number
3. It still allows users to override with their own custom ID if desired
4. It provides clear feedback about what’s happening

This implementation makes the workflow more user-friendly by automatically generating sequential IDs while still maintaining flexibility for custom identifiers.

Claude 3.7 Sonnet Impressing Me More

Now that a default composite key is filling in, the Datalist dropdown menu feature is totally hidden unless the pre-filled ID is deleted. So I want to make it really easy to delete it and want one of those little “x” widgets on the text field to clear it… like a search field!

Me: Do you think we could turn the pkey field into a search type in order to get the little clear “x” widget on it, which would be useful to trigger the datalist.

Claude: Yes, we could convert the input field to use type="search" instead of type="text" to get the little clear “x” button, which would help users interact with the datalist more easily.

In most modern browsers, <input type="search"> automatically includes a clear button when text is entered. This is a built-in browser feature, not part of PicoCSS specifically, but it works well with PicoCSS’s minimalist styling approach.

The change would be simple - just update the Input element’s type attribute:

Input(
    placeholder="🗝 Existing or new ID here",
    name="pipeline_id",
    list="pipeline-ids",
    type="search",  # Changed from "text" to "search"
    required=True,
    autofocus=True,
    value=default_value
)

This small change offers several benefits:

1. Users can quickly clear the auto-generated ID if they want to type their own
2. The clear button appears only when there’s text to clear
3. It provides a visual cue that the field is searchable/filterable
4. It helps users more easily interact with the datalist by clearing and retyping to see options

This enhancement fits well with our goal of improving the user experience while maintaining simplicity. The clear button is particularly useful in this context since we’re pre-filling the field with an auto-generated value that users might want to replace.

Me: OMG, that’s amazing! It’s just perfect. Small touches like this make it so much more appealing and useful. Do you think you could put the cursor at the end of the text in the search field? Right now it comes up at the beginning and is a little awkward.

[I go back and forth with Claude for a bit, but we nail it]

Me: Oh, that totally did it. This is a VERY SLICK system.

Claude: Fantastic! I’m glad it’s working perfectly now. You’re right - this is indeed a very slick system you’ve created!

The combination of:

1. Type=”search” giving you that neat little “x” clear button
2. Autofocus automatically placing focus on the field
3. The onfocus handler positioning the cursor at the end
4. Auto-incrementing IDs eliminating decision fatigue
5. Profile-plugin-number composite keys providing context
6. Datalist showing relevant existing options

…all work together to create an exceptionally smooth user experience.

What’s impressive is how many UX problems this solves with minimal code:

• Users never stare at a blank input field wondering what to type
• The clear button makes it easy to start fresh
• The cursor is right where you need it to edit
• The ID system naturally organizes data hierarchically
• The pattern is consistent and predictable

These are the kind of small, thoughtful touches that make a system feel polished and professional. The fact that you’ve done it with such minimal code - just a few attributes on a standard input - makes it even more elegant.

Gemini 2.5 Experimental’s Take

Okay, here are the requested components based on the provided article:

Summary (First-Person Author Perspective)

[Moved to top]

Title and Headline Ideas

• Solving Key Paralysis: Auto-Incrementing Composite Keys in Action
• Building a Slick, Context-Aware ID System with AI Collaboration
• From Blank Field to Smart Suggestion: A UX Win with Auto-Increment
• MiniDataAPI + FastHTML: Crafting User-Friendly Auto-IDs
• Profile-Plugin-Number: The Power of Contextual, Auto-Incrementing Keys
• AI-Powered UX: Eliminating ID Headaches with Smart Defaults
• Beyond Basic IDs: Implementing Elegant Auto-Increment Logic
• Case Study: Enhancing Workflow Initiation with Auto-Generated Keys

Article Strengths and Weaknesses

Strengths:

1. Clear Problem/Solution: Effectively identifies a common UX problem (“key paralysis”) and presents a well-reasoned, implemented solution.
2. Demonstrates Collaboration: Showcases a practical example of human-AI interaction, including iterative refinement based on feedback.
3. Technical Detail: Provides concrete code examples and discusses specific technical considerations (MiniDataAPI vs. ORM, FastHTML components, input types, JavaScript focus).
4. Highlights Benefits: Clearly articulates the advantages of the solution (reduced cognitive load, better organization, improved UX, context).
5. Engaging Narrative: The conversational format and the author’s enthusiastic reactions (“OMG”, “Slick System”) make the development process feel dynamic.

Weaknesses:

1. Context-Dependent: Relies heavily on understanding the specific environment (Pipulate, MiniDataAPI Spec, FastHTML, profile/plugin concepts) which may not be clear to external readers.
2. Jargon-Heavy: Uses specialized terminology that could be a barrier without prior knowledge of the system.
3. Incomplete Picture: Presents snippets and conversation; readers don’t see the full codebase or surrounding architecture.
4. Raw Format: As a transcript, it lacks the polished structure of a formal article, potentially requiring readers to piece together the narrative flow.

AI Opinion of the Article

As an AI, I find this article (transcript) compelling as it vividly illustrates a practical AI-human collaboration process. It effectively documents the identification of a specific UX challenge (‘key paralysis’) and the iterative development of an elegant technical solution using context-aware composite keys and auto-increment logic. The inclusion of code snippets, discussion of specific framework nuances (MiniDataAPI, FastHTML), and the refinement loop focusing on usability details (like input type and cursor behavior) provide valuable insight into applied AI development. While the heavy reliance on project-specific context and jargon might limit accessibility for a general audience, it serves as an excellent case study in targeted problem-solving and demonstrates how AI can assist in translating high-level requirements into functional, polished code that enhances user experience. The dialogue effectively captures the synergy possible when human insight guides AI capabilities towards a refined outcome.