Design for generating/filling a document based on a template-file, while considering permissions from a database?

Question

You have the backend for a web application. I'd like to keep this is as disconnected from specific programming lanuages as possible, but if necessary, assume we're talking about PHP.

You need to write code that generates or fills documents with data from a database, so that these documents can be stored as files. These documents are required from a third party and their structure is predetermined by that party; your job is to eventually fill prepared data-fields with your data. You initially have these empty documents at your disposal, which can be used as templates. If more specific info is necessary, I am talking about xlsx-files.

The problem is that it is rather difficult to write generic code that can handle filling all required documents with your data, as their structure and the data they expect do not seem to follow any strict pattern or norm, which requires you to write new code for every single new document that you need to support. It can also be expected that over time more documents will be required, so you have to add support for them as well.

In addition, information in a database dictates which of the users who request these documents have access to them.

Problems that need to be solved:

• Where do you find the 'source of truth' for which documents the application supports? Should you persist it to the database, should you base it on the list of template-files that the application can find or should you hard-code it?
• How do you associate code that generates/fills a specific document with the document itself (that would be stuff like its name or file-properties of the provided template-file)
• How do you associate the database-data regarding access-permissions to the document with the document itself?

These problems are largely about combining the truths of which documents/templates are known to exist with what the application knows to do with them by being able to reliably identify them.

I'd like to hear what you think could be a solution for this.

Answer 1

I think the core problem in your process description is, there is a missing role in this use case. There will be an administrator required, someone from the organisation owning the database who

• gets empty document templates from the third party, cleans them up, adds something like machine-readable placeholders into them and makes sure your application can process them. The placeholders could be special, unique keywords, which your application will simply search and replace.

• puts those templates into a place where the application can find it (whether that will be in the database or somewhere else is something you have to work out with one of the administrators)

• in case there are different access rights for the data required: someone who makes these decisions and enters them into the system somehow. For example, an admin has to make sure an individual template contains only a specific placeholder subset. Or the templates have to be classified into groups for users with different access rights, and only users with the necessary permissions will be offered to get templates for their permission level filled.

These steps cannot be easily automated, it is an interactive, manual process, separate from development. Of course, when the templates themselves change rarely (say, once or twice a year), and the developers work for the same organization as the database owner, or have a specific maintenance contract, and deploying new application versions works quickly, then the template assignments and access rights might be hardcoded into application by the devs, and updated at will.

But in all other cases, assume there will be this special administrative role necessary, with a special user interface (like an "admin panel") and maybe more requirements you have to implement for that role in the system.

Answer 2

Where do you find the 'source of truth' for which documents the application supports? Should you persist it to the database, should you base it on the list of template-files that the application can find or should you hard-code it?

How do you associate code that generates/fills a specific document with the document itself (that would be stuff like its name or file-properties of the provided template-file)

How do you associate the database-data regarding access-permissions to the document with the document itself?

Assuming the structure of each document could be chaotic/unpredictable, one idea would be to create Document Generation Plugins of your solution.

Each "supported document" could be determined by the existence of a Plugin installed on your application (e.g.: plugin for xlsx, plugin for .docx, plugin for .pptx, etc.).

One plugin package would contain:

• the empty template file it supports;
• code that receives ACL metadata and actual document data from its caller; and
• code that generates the filled document, based on its template and data.

Overtime, when you need to add support for more chaotic documents, you implement new plugins and deploy to your customer as additional DLLs, for instance.

Bottom-line: your plugin should at least contain the logic to generate the document based on the template, and which data to look for with a provided data access object. Regarding the ACL/permissions, if this gets specific based on document type, it might be great to place this logic into the plugin as well.