Prevent timestamp manipulation?

Is there a way to ensure that the timestamp is the actual time and not the device’s time?

We just discovered someone manipulating their timecard by manually setting the device time and then punching in to show they were on time. Sneaky…

I can go into the audit logs which timestamps the actual time of the operation to verify but this would get pretty cumbersome when trying to manage 35+ timecards.

The device’s belong to the employees and we install the app on their phone.

Thank you.

1 Like

Sneaky sneaky. Hmmm I would suggest checking out UTCNOW() - though I think it’s not actually polling the time from some external source, instead only adding or subtracting based on your timezone.

@Phil might have a good idea what to do.

Just tried this and you’re correct. Changed my column to UTCNOW() and I was still able to manipulate the timestamp via phone settings; does not poll an external source.

:thinking:

1 Like

UTCNOW() is the device time without the timezone adjustment. It’s just as subject to tampering as NOW() is.

There is no way to poll an external source for time.

If you use a workflow to add a timestamp to a row, the timetamp will reflect the server’s time rather than that of the user’s device. (Workflows run from AppSheet servers, not on the user’s device.) The downside with workflows is that they run only when the server receives the data from the device when a sync occurs, so the timestamp added by a workflow really reflects when the data was synced, not when it was modified on the device. But this might still help you discover tampering. Or you could even say the clocking-in and -out must be done by syncing.

2 Likes

This worked.

I created a new column [Actual Sync Time] and created the workflow to set this column with a timestamp. The [Date/Timestamp] column captures device time, [Actual Sync Time] shows when workflow was run.

I’m curious how others have handled this. Particularly with apps that may be subject to compliance or regulatory review where accuracy and validity of something like when a record was created would be critical - apps in the medical field, osha inspections, fleet inspections, etc.

Thanks, again, Steve.

2 Likes

Yeah @Dialect_Junk you just exposed a major design flaw, one that I’m afraid I never really considered.

Even the solution of using a workflow to set a timestamp is kind of a Band-Aid.

What if I’m on the job and create all of my records, but I shut the app before they have a chance to sync… then the next morning when I open the app, it syncs all of those records - now my timestamps are all off.

No, we need a real solution to this; something that will appease any kind of regulatory investigators.

A solution I can think of is to have an internal clock inside the app, one that is separate from the local devices system clock and only get its updates from AppSheet’s servers. Then have some kind of function that we can use so we can call that time. SYSTEMTIME() or something.

Or, just completely switch over all of the time mechanisms to an internal clock that’s locked into the actual time of the world. This way any current formulas using NOW() or something like that will automatically start keeping track based on the real time.

@praveen

1 Like

I’m curious how you caught this?

We noticed an employee’s timecard timestamp didn’t match the time he actually arrived to work. We knew he arrived at 8:30AM, yet the timestamp showed 7:45AM. We thought he must’ve clocked in on the way to work, but we checked the gps capture and it showed he clocked in at the job-site.

I checked the audit log and the log captured the actual UTC time that the sync was performed, not the timestamp. This verified his timestamp was wrong and that something was amiss.

We did some poking around, asked the right questions, and found out that last year several employees were changing their device’s time, and then clocking in. Turns out, it was happening very frequently. We thought we’d devised a fool-proof system with timestamps and gps capture, but, obviously, we were mistaken.

As you can imagine, it’s caused quite a stir around here. The amount of loss via theft of time is incalculable and nightmarish. Quite honestly, it has our leadership group asking whether, absent a tamper-proof solution, this is the right platform for us. I’m an advocate for the platform so I’ve fallen on the sword and accepted responsibility for not being aware of this. I’m keeping my fingers crossed that Steve’s “band-aid” is sufficient but I think it’s something I’ll have to monitor daily.

I’ll also add that last year we had a reportable OSHA incident where we had to provide records of our fleet inspections. We were able to provide a table of this information and satisfy the OSHA inspectors. But, now, I’m not so sure that if our information was more closely examined, it would stand up to additional scrutiny. Especially if we were asked to verify and prove that inspection dates are accurate.

1 Like

This only works for an always-online app.

Wouldn’t this require hardware support in the device?

1 Like

How was fraud/tampering prevented prior to apps?

1 Like

I can hear the war drums from my clients already… :cold_sweat:

I’ve also had an app of mine go through OSHA scrutiny, and they were satisfied with everything we had put in place as well. (Creation and Edit timestamps. gps, audit trail, etc.)


Nefarious players are at work everywhere, and while it may be impossible to stop them - at least we can build systems to catch them.

1 Like

Old paper and pencil system:

  • Employees checked in and checked out with Supervisors daily.
  • Supervisors wrote down their hours.
  • Supervisors would have to get paper timecards back to the office by Friday 6pm.

Lots of delays in processing payroll and invoicing if paperwork was missing or turned in late. Wildly inefficient.

Hence the move to Appsheet.

2 Likes

Exactly how I found appsheet; I was working as an inventory manager at a construction company, and this is how they did payroll as well.

But I was privy to both sides of that equation - I was friends with all the workers, but I worked in the office (with the people who processed payroll) - so I was able to see the truth of what was really going on there.

1 Like

I think a reasonable solution is to use the sync time as recorded by the server. You can even force the app to sync when the clock-in and -out is submitted. Just make sure staff knows that they must be online when they check-in and -out.

1 Like

This would be 50/50 for us. Many of our jobs are in remote areas where service is spotty or nonexistent. In these instances, employees have to wait until they get back to an area with service or back to the hotel before they can sync.

2 Likes

I don’t know much about much, and this could be spectacularly dumb, but, I’m wondering if GPS may hold a solution. GPS doesn’t require internet connectivity and GPS timestamps are supposed to be accurate(?)

If the app is forced to allow location services, and the app can be forced to record GPS location upon startup, can the GPS’s timestamp be extracted from the GPS metadata at the time it was captured? This way it wouldn’t be reliant on device time or sync time.

Just an idea…

1 Like

GPS can be spoofed just as easily as device time. With dozens of third party applications directly on a phone, or even directly in a chrome browser with chrome devtools:

2 Likes

One solution may be to have a terminal set up at the job sites where users punch in and out at (instead of using their personal devices), although this seems to forego some of the benefits you are trying to gain.

I have a hard time envisioning a foolproof system. Cheaters are going to cheat. You may be able to come up with some algorithm based upon the recorded data and server sync times, and other records being uploaded, which can help detect the cheaters. Or you may have to do spot checks at job sites.

3 Likes