Skip to main content

Database Migration Guide for Intuit OAuth

Overview​

This guide will help you apply the database changes needed for the Intuit OAuth integration.

What's Being Added​

A new table IntuitOAuth will be created to securely store:

  • Access tokens (encrypted)
  • Refresh tokens (encrypted)
  • Token expiration times
  • QuickBooks Company/Realm ID

Prerequisites​

Before running the migration:

  1. ✅ Ensure your database is accessible
  2. ✅ Have your CockroachDB connection string in .env
  3. ✅ Backup your database (optional but recommended)
  4. ✅ Have the required Intuit environment variables ready

Required Environment Variables​

Before running the migration, add these to your .env or .env.local file:

# Intuit OAuth Configuration
INTUIT_CLIENT_ID="your_client_id"
INTUIT_CLIENT_SECRET="your_client_secret"
INTUIT_REDIRECT_URI="http://localhost:3000/api/admin/intuit/callback"
INTUIT_ENCRYPTION_KEY="your_secure_random_32_character_key"

# Optional: OAuth Scopes (defaults to accounting + payment)
INTUIT_OAUTH_SCOPE="com.intuit.quickbooks.accounting com.intuit.quickbooks.payment"

Generate Encryption Key​

# Using Node.js
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

# Using OpenSSL
openssl rand -hex 32

Running the Migration​

# Create a new migration
npx prisma migrate dev --name add_intuit_oauth

# This will:
# 1. Create a new migration file
# 2. Apply it to your database
# 3. Regenerate the Prisma Client

Option 2: Push Schema Directly (Quick Testing)​

# Push the schema changes directly without creating a migration
npx prisma db push

# Then generate the Prisma Client
npx prisma generate

Option 3: Production Deployment​

# Apply pending migrations to production
npx prisma migrate deploy

Verify the Migration​

Check the Database​

Connect to your CockroachDB and verify the table exists:

SHOW TABLES;
-- Should show: IntuitOAuth

DESCRIBE IntuitOAuth;
-- Should show the table structure

Check Prisma Client​

Verify the Prisma Client was updated:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// This should not show any TypeScript errors
const oauth = await prisma.intuitOAuth.findFirst();

Rollback (If Needed)​

If you need to rollback the migration:

# Find the migration to rollback to
npx prisma migrate status

# Reset to a specific migration
npx prisma migrate resolve --rolled-back add_intuit_oauth

# Or reset the entire database (⚠️ CAUTION: This deletes all data!)
npx prisma migrate reset

Troubleshooting​

Error: "Can't reach database server"​

  • Check your DATABASE_URL is correct
  • Verify your database is running
  • Check your network/firewall settings
  • Ensure SSL is configured if required

Error: "Migration already exists"​

# Check migration status
npx prisma migrate status

# If the migration was partially applied, mark it as applied
npx prisma migrate resolve --applied add_intuit_oauth

Error: "Column already exists"​

The table may have been created manually. Either:

  1. Drop the table and run the migration again:

    DROP TABLE IntuitOAuth;

  2. Or mark the migration as applied:

    npx prisma migrate resolve --applied add_intuit_oauth

What Happens After Migration​

  1. New Table Created: IntuitOAuth table is added to your database
  2. Prisma Client Updated: TypeScript types are regenerated
  3. Ready to Use: You can now connect QuickBooks in the admin dashboard

Next Steps​

After successful migration:

  1. ✅ Restart your development server
  2. ✅ Log in to the admin dashboard
  3. ✅ Look for "Intuit QuickBooks Integration" section
  4. ✅ Click "Connect to QuickBooks"
  5. ✅ Complete the OAuth flow
  6. ✅ Verify tokens are stored in the database

Verification Queries​

Check if tokens are stored correctly (after connecting):

-- See if OAuth record exists (tokens will be encrypted)
SELECT id, realmId, expiresAt, createdAt, updatedAt
FROM IntuitOAuth
ORDER BY updatedAt DESC
LIMIT 1;

Note: Access and refresh tokens are encrypted, so they will appear as random strings in the database. This is by design for security.

Migration File Location​

The migration will be created at:

prisma/migrations/YYYYMMDDHHMMSS_add_intuit_oauth/migration.sql

Database Schema​

The migration adds this table:

CREATE TABLE "IntuitOAuth" (
    "id" STRING NOT NULL DEFAULT gen_random_uuid(),
    "realmId" STRING NOT NULL,
    "accessToken" STRING NOT NULL,
    "refreshToken" STRING NOT NULL,
    "expiresAt" TIMESTAMP(3) NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "IntuitOAuth_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "IntuitOAuth_realmId_key" ON "IntuitOAuth"("realmId");
CREATE INDEX "IntuitOAuth_realmId_idx" ON "IntuitOAuth"("realmId");

Support​

If you encounter issues:

  1. Check the Prisma Migration Docs
  2. Review CockroachDB connection settings
  3. Ensure all environment variables are set correctly
  4. Check application logs for detailed error messages

Last Updated: October 11, 2025 Related Documentation:

  • INTUIT_OAUTH_GUIDE.md - Complete OAuth setup and usage guide