I’m working with an external service that has two main resources: Products and Deals. When I create these items through their API, I get back JSON responses that contain lots of data.
Right now I only need certain fields from these responses, but I’m worried that later on I might need other parts of the data that I’m not currently storing. So I’m thinking about saving the complete JSON response somewhere just in case.
What’s the recommended approach for handling this situation? I need advice on both the database design side and the application architecture side. My app uses Rails with the standard MVC pattern.
Should I create separate tables for the JSON data or add JSON columns to existing tables? And how should I structure my models and controllers to handle both the parsed data and the raw responses efficiently?
I’ve dealt with this exact problem when integrating multiple third-party APIs. A hybrid approach works best - store your essential fields in normalized columns for fast queries and relationships, but keep a jsonb column for the complete response payload. Treat the raw JSON as an audit trail and fallback, not your primary data source. In Rails, my API service classes always save both the parsed attributes and full response. Then I created model methods that gracefully fall back to the JSON when needed. This saved me countless hours when an external service deprecated endpoints and I had to reconstruct data from old responses. Just make sure you add database constraints on critical fields - you can’t rely solely on flexible JSON for data integrity. The performance overhead is minimal with modern PostgreSQL, and the peace of mind is worth it when dealing with external dependencies.
i’d go for a mix - keep the important fields in standard columns and add a separate table for full JSON responses. this method keeps your main tables tidy but lets you access everything later on. really helps with troubleshooting when apis behave unexpectedly.
I encountered a similar scenario last year with a payment processor API. What worked well for me was adding a raw_response JSON column to my existing tables rather than creating separate storage tables. This approach keeps everything consolidated and simplifies queries. On the application side, I developed a service object to manage the API client, which introduces both the raw JSON response and the necessary attributes to the model. The model thus retains all data while only exposing relevant information through methods. This strategy was beneficial when the external service altered their response format and when additional fields were needed later. Additionally, it’s crucial to implement proper JSON indexing on fields expected to be queried in the future, and PostgreSQL efficiently manages this. Running these API calls as background jobs can also help prevent blocking your main requests, particularly when accessing multiple external resources like Products and Deals simultaneously.
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.