November 15, 2024

🤖 Prompt Column Pattern - Leveraging Database Concepts for AI Classification

Discover how to leverage AI "computed columns" for seamless data classification in SaaS applications!

Zero-shot and few-shot prompt techniques are effective methods for data classification. While integrating AI into various parts of a SaaS application, I've discovered that the mental model of computed columns from traditional databases naturally extends to these new techniques. I call this pattern "Prompt Columns" - a powerful implementation of separation-of-concerns where AI enrichment operates independently from the application logic. This approach enables powerful classification and enrichment capabilities without requiring extensive custom logic in your application code.

First, if you haven’t subscribed, please join us! I write weekly on technical and technical leadership topics. If you have thoughts on my writing, I’d love to hear from you.

Computed Columns

Computed columns in relational databases combine data from the current row to compute a new value.

Consider a simple example: storing someone's height in both inches and centimeters in the database.

CREATE TABLE people (
    height_cm numeric,
    height_in numeric
);

This basic approach requires computing both centimeters and inches, ensuring both columns are updated whenever one changes. This approach can introduce bugs. For instance, one script might update inches without updating centimeters, while another script reads centimeters and updates inches. This schema presents two significant problems:

1. Inches and centimeters are not guaranteed to be consistent.

2. There's no clear "source of truth."

Databases can solve this simple problem with a computed column:

CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
); 

Now, height_in is computed automatically by the database whenever height_cm is updated or inserted. The application can operate exclusively in centimeters and trust the database to maintain consistency between the two values.

Prompts as Computed Columns

This same concept can be applied to AI prompts. Consider a comment system requiring sentiment classification. Using a zero-shot prompt:

Classify the text into neutral, negative or positive. 
Text: I think the vacation is okay.
Sentiment:

The AI will return one of three values: neutral, negative, or positive.

We can envision an AI-enabled database supporting a definition like this:

-- Proposed syntax for an AI computed column. 
CREATE TABLE comments (
    comment text,
    sentiment text GENERATED ALWAYS AS ```Classify the text into neutral, negative or positive. 
Text: ${comments.comment}
Sentiment:```
);

When an application writes to the comments.comment field, an LLM call executes the prompt and stores the result in the specified column. This maintains the benefits of traditional computed columns: applications focus on their core functionality while the database handles data updates. The key innovation is using an AI prompt as the computation method!

Conclusion

This approach bridges traditional database concepts with modern AI capabilities, offering a clean, maintainable way to integrate AI classification into existing systems. The familiar computed column pattern provides a robust foundation for AI-enhanced data processing.

In an upcoming article, I'll demonstrate a practical implementation of this concept using Firestore, showing how to create AI-powered computed columns in a real-world application. We'll explore the technical details and best practices for implementing this pattern in a serverless environment.